help in db design

PHPycho

Hello forums!!

I am in dilema regarding DB design, so I am expecting the forumians help here.
I have 3-4 tables(say categories, brands, products, etc.) and each table has images.
I would like to design db for images for all those tables.

1> My first Approach:

----------------
category_images
----------------
- id
- category_id
- image_title
- image_path
- is_active
- ordering
----------------

----------------
brand_images
----------------
- id
- brand_id
- image_title
- image_path
- is_active
- ordering
----------------

----------------
product_images
----------------
- id
- product_id
- image_title
- image_path
- is_active
- ordering
----------------

Note: all the table have similar structure

2> 2nd Approach
(wordpress like taxonomy concept)

------------------
taxonomy_images
------------------
- id
- taxonomy
- object_id
- image_title
- image_path
- is_active
- ordering
------------------

where,
taxonomy = category or brand or product
object_id = category_id or brand_id or product_id

And query is done as:
[HIGHLIGHT="MySQL"]SELECT * FROM taxonomy_images WHERE taxonomy=? AND object_id=? ORDER BY ordering[/HIGHLIGHT]

I want opinions from you to choose the appropriate design (for long run)
or can suggest some alternatives too.

Thanks

sneakyimp

I don't like the first approach much. Sounds like the single images table would be much better.

What other kind of advice are you looking for?

ixalmida

I personally don't like the idea of having 3 tables for storing similar information unless you know beforehand that those tables are going to be huge. I'm not sure what you want to do with taxonomy but just as a matter of good practice, you don't want to have multiple tables that have a nearly identical structure unless you know for sure that the table will be a storage issue. And if it IS a storage issue, you should be very sure the table structure is as lean as possible before you go splitting tables.

Weedpacket

I'm curious about why the tables need both "id" and "(product|category|brand|object)id" fields. Surely in the three separate tables there's a 1:1 relationship between "id" and "(product|category|brand)id" or it wouldn't be much of an ID. And in the other case, the combination of "taxonomy" and "object_id" would similarly be unique, and again "id" would be redundant.