Page 1 of 1

help in db design

Posted: Fri Mar 05, 2010 12:34 am
by 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:

Code: Select all

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

Code: Select all

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

Code: Select all

----------------
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)

Code: Select all

------------------
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

Re: help in db design

Posted: Fri Mar 05, 2010 3:22 am
by Christopher
I think I am missing a step. Do you want multiple images per categories, brands, products, etc.? Just change image_path to image_id and get the image details (path, description, etc.) from the new images table.

Re: help in db design

Posted: Fri Mar 05, 2010 3:34 am
by papa
As the images share the same features I would probably do a relation db structure instead.

Image
id | image_title | image_path | is_active | ordering
-------------------------------------------------
1 | test image | images | y | 10

Type
id | name
-------------------------------------------------
1 | brand
10 | category
34 | product


Image_Type
image_id | type_id
-------------------------

Now you can add the same picture to several types (brand, product etc) without having to insert title, path etc in several tables:

Image_Type
image_id |type_id
----------------------------
1 | 1
1 | 34
1 | 10

Same image but three different categories.

Re: help in db design

Posted: Fri Mar 05, 2010 5:05 am
by PHPycho
Please suggest me among the two?
yes ofcourse i will take that separation of image details into separate table in mind.
thanks