homepage Welcome to WebmasterWorld Guest from 54.166.173.147
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
help in db design
PHPycho




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

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

 

rocknbil




msg:4092303
 7:25 pm on Mar 5, 2010 (gmt 0)

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.

PHPycho




msg:4093135
 5:19 am on Mar 8, 2010 (gmt 0)


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

rocknbil




msg:4093451
 6:37 pm on Mar 8, 2010 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved