Welcome to WebmasterWorld Guest from 107.22.7.35

Forum Moderators: open

Message Too Old, No Replies

help in db design

     
6:33 am on Mar 5, 2010 (gmt 0)

5+ Year Member



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
7:25 pm on Mar 5, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



I'd probably do this instead.

table image_types
----------------
- id
- type_id
- type_title

table all_images
----------------
- id
- type_id
- category_id
- image_title
- image_path
- is_active
- ordering

combining all the various types into one table, identified by the type_id field. Most use just the auto_increment id field for joining, I prefer not to . . . . there are reasons. If you're one of those, eliminate the type_id field from the image types table.
5:19 am on Mar 8, 2010 (gmt 0)

5+ Year Member




table all_images
----------------
- id
- type_id
- category_id
- image_title
- image_path
- is_active
- ordering

but this is same as my 2nd method
where type_id = taxonomy (which is varchar instead of int)
category_id = object_id

can you explain what's the advantage of using type_id instead of taxonomy varchar for 2-3 rows?
Thanks
6:37 pm on Mar 8, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



can you explain what's the advantage of using type_id instead of taxonomy varchar for 2-3 rows?


What you name the column is not important (other than making it easy to understand.) But in a complex select, the numeric id on this field will be faster.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month