SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help in db design

    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:
    ----------------
    category_images
    ----------------
    - id
    - category_id
    - image_title
    - image_path
    - is_active
    - ordering
    ----------------
    Code:
    ----------------
    brand_images
    ----------------
    - id
    - brand_id
    - image_title
    - image_path
    - is_active
    - ordering
    ----------------
    Code:
    ----------------
    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:
    ------------------
    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:
    Code MySQL:
    SELECT * FROM taxonomy_images WHERE taxonomy=? AND object_id=? ORDER BY ordering

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

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try writing a query which retrieves categories, brands, and products, each with images
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try writing a query which retrieves categories, brands, and products, each with images
    but on which table structure: i> or ii> ?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do it on both, of course, to compare how complex it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On my view:

    Working on 1st: will be easier but it will violate the DRY principle.
    Working on 2nd: will be a bit tricky on querying but it can be reused for different table just by using the combination of object_id & taxonomy fields.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    DRY principle?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    little lever
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dont Repeat Yourself
    Manchester Free Ads - Free classifieds, only in manchester.
    Best web hosting

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh, i see, thanks dddougal

    i still think 1st approach is better

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,135
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    I recently tackled the same problem but for addresses and used your second methodology for the same reason.

  10. #10
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    I recently tackled the same problem but for addresses and used your second methodology for the same reason.
    are you referring to Rudy or me?

  11. #11
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I would personally prefer method 1. I'm not exactly sure why, it just seems to be more of a modular design to me. Maybe easier to extend, customize and modify down the road if necessary.

    Also it would make your queries easier in general b/c you're no longer having to worry about separating the images by category, they're already separated.

    I think the DRY method could be focused on having to repeat your query code (WHERE taxonomy=?) in EVERY image query you make. I do NOT think you are repeating yourself by having three similarly structured tables, the data in them will be completely different.

    Just my $.02

  12. #12
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In either ways you have to query the tables exactly that number of times what the total tables are for images but only the table names will be different. So I would prefer the first method which is clear and would definitely be easy to extend and can be implemented Referential Integrity (RI) too. I don't think you can implement easily RI with second method. So except increasing the number of tables in the database, there will not be anything wrong in first method as far as I know.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rajug View Post
    I don't think you can implement easily RI with second method.
    that's correct, you cannot declare the FK and relational integrity is down the, um, tubes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,135
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by PHPycho View Post
    are you referring to Rudy or me?
    Your second method.

  15. #15
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok Thanks to all of you for the opinions.
    I went with the Method #2 and implemented it.
    Reasons:
    - my table types are MyIsam type
    - The same MVC files has been re-used for all the different types of images (just have to pass the 'taxonomy' as an additional reference).
    - Its easier for me to add images for other tables.

    Nevertheless to say Opinions from your side are always welcome


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •