SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Hybrid View

  1. #1
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    One products table or multiple products tables?

    I'm working on a shopping cart application where the store contains a diverse range of products. What I'm trying to work out is whether to create individual tables tailored specifically for each product category, or whether to have a single table for all products with a fair amount of redundancy.

    Simple example: Let's say I'm selling CDs and Books at my store. Common fields might include

    Name
    Description
    Price
    Weight
    Height
    Width
    InStock

    ...but to fully describe a book I might want the
    additional fields

    ISBN
    NumberPages

    ...and to fully describe a CD I might want the
    additional fields

    NumberTracks
    NumberDiscs

    So is the 'best' solution to go for a single 'Products' table for all products such as this:

    CategoryID // Book or CD
    Name
    Description
    Price
    Weight
    Height
    Width
    InStock
    ISBN
    NumberPages
    NumberTrack
    NumberDiscs

    Or do I go for two tables

    Books
    -----
    Name
    Description
    Price
    Weight
    Height
    Width
    InStock
    ISBN
    NumberPages

    CDs
    ---
    Name
    Description
    Price
    Weight
    Height
    Width
    InStock
    NumberTrack
    NumberDiscs


    My guess is that there is no strictly right or wrong answer. The multi-table option is more efficient in terms of minimising redundancy, but the single table option is easier to code against because all products are in the same place.

    Perhaps my question should be - what factors should make me lean more towards one approach as opposed to the other?

    What does/would Amazon do?

    Thanks.
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  2. #2
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would use 2 tables. In the future you may need to add more fields to tables and they may not related to other product; or later, the store may cancel one product such as CDs, and add other products then it's a mess.

    I bet Amazon would do the same as I do since each group takes care one division, and they don't want to see any fields that are not related to what they do.

  3. #3
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay here's your answer.

    have more than just 2 tables.


    combine what fields are identical into one table.
    Code:
    MASTER PRODUCT TABLE
    ---------------------
    NAME
    DESCRIPTION
    :
    :
    :
    CATEGORY_ID
    under category_ID i would enter a category that would identify the product as a: CD, BOOK, SHOES, MASCARA, ETC.

    have another table CATEGORY TABLE that describes these categories
    Code:
    CATEGORY TABLE
    ---------------
    CATEGORY NAME
    CATEGORY ID
    then have a category description table
    Code:
    CD CATEGORY DESCRIPTION
    --------------------
    CATEGORY_ID
    PRODUCT_ID
    CD TRACKS
    :
    :
    
    
    BOOK CATEGORY DESCRIPTION
    --------------------
    CATEGORY_ID
    PRODUCT_ID
    ISBN
    TITLE
    :
    :
    :
    the reason you would do this is that if you just wanted to display the basics of a product you only have to query 1 table. when the person wants to get more details then you can involve the category tables and pull more info.

    this would accomplish a faster query when displaying multiple products in that there isn't a bunch of white space or redundant stuff.

    if you really want to get fancy under the category description table you can have a CROSS-SELL PRODUCT ID which would recommend a buyer a complementary product with their purchase.

    if you ever wanted to sell hot dog buns this would recommend a set of hot dogs to the client while shopping. or milk when purchasing eggs...

    man i'm hungry!

    hope this helps.

    dlg

  4. #4
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi DLG_1,

    Thanks for your reply. I'm really glad I asked the question, because I really like your answer. I just wasn't fully comfortable with either of my original options, but couldn't quite figure out why. Now I know why.

    Do you know from experience of having worked on big database projects that this solution is the 'best' or 'preferred' one? Just curious. Thanks again.
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  5. #5
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think that this helps normalize the database better than any other options. i have worked on a couple of database projects, but i'm not an expert as wayne. (sitpoint)


    dlg

  6. #6
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've got another level of complexity to introduce into this issue of database design for a store with a diverse range of products...

    How does one handle (or allow for) sub-categories and, potentially, sub-sub-categories (and so on) of products?

    For example, you might want to sub-categorise books into 'hardback' and 'paperback', and CDs into 'Rock', 'Jazz', 'Dance', etc...

    Categories
    ----------
    CATID CATNAME
    1 Books
    2 CDs
    3 Cameras
    . ..

    SubCategories
    -------------
    CATID SUBCATID SUBCATNAME
    1 1 Hardback
    1 2 Paperback
    2 3 Rock
    2 4 Jazz
    2 5 Dance
    .. ... ...

    Or perhaps you would create separate sub-category tables - 1 for Books, another for CDs, another for Cameras, etc?

    Or perhaps (and I think this is the probably the right answer) you have just one category table, with an ID and a PARENTCATID, eg.

    Categories
    ----------
    CATID PARENTCATID CATNAME
    1 - Books
    2 - CDs
    3 - Cameras
    4 1 Hardback
    5 1 Paperback
    6 1 Fiction
    7 1 Biography
    8 2 Single
    9 2 Double
    10 2 Triple
    11 2 Rock
    12 2 Jazz
    13 2 Dance
    . ..

    Er, there's a problem here. What if I want to construct the query "show me all double Jazz CDs". Hmmm - not sure now if the above would be helpful.

    So the same system would have to allow for the possibility that some products might not require sub-categories, and some products would have multiple levels of sub-categories.

    In any event, what would the fields of the Master Products table look like given that it's now not a simple case of a 1 to many relationship between categories and products?

    Hoping for some more assistance
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  7. #7
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    This thread might be helpful to you:
    http://www.sitepointforums.com/showt...ight=recursive

  8. #8
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you would keep my initial tables the same however, in the master table you would allow for two fields:


    MASTER PRODUCTS
    ----------------
    PRODUCT_ID (PK)
    CATEGORY_ID (FK)
    SUBCATEGORY_ID (FK)



    CATEGORIES
    --------------
    BOOKS
    CDS
    ETC
    :
    :

    SUB-CATEGORIES
    -----------------
    SUBCATEGORY_ID (PK)
    CATEGORY_ID (FK)
    NAME
    DESCRIPTION
    :
    :


    in the master table you would just have a "sub-category" number that would link the product to the sub-category table which would further describe the product. no biggy, really simple.

    if you really want to learn more about this kind of stuff, I highly recommend the following book:
    ] FUNDAMENTALS OF DATABASE SYSTEMS
    it has a "hefty" price, but it is literally the best book out there to learn how to design great databases.
    (btw, I have it in my collection)


    dlg

  9. #9
    Don't eat yellow snow spaceman's Avatar
    Join Date
    Mar 2001
    Location
    Melbourne, Australia
    Posts
    1,039
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    in the master table you would just have a "sub-category" number that would link the product to the sub-category table which would further describe the product. no biggy, really simple.
    I think this approach would work for most of the product catalogue I'm working with. But speaking from experience of other databases I've worked, you just know that the moment that you tell the client "the structure of the database is such that a product must belong to a sub-category, and a sub-category must belong to a category", is the moment he/she tells you that some products can't have a sub-category, and other products are better grouped with two levels of sub-category.

    In other words, looking at the really big picture, it would be great to be able to develop a system that allowed for a variable number of sub-categories, and allowed for products to be quickly and easily associated with one or more sub-categories.

    Maybe recursion is the only way, but I've dabbled there before and it can be hard to get (and keep) your head around it.
    Web Design Perth Melbourne .:. Itomic Business Website Solutions
    Drupal Experts .:. Drupalise

  10. #10
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree that recursion would be good, but in order to allow your sub-categories to have additional attributes you'll have to do something other than recursion.


    dlg

  11. #11
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    597
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually i take that back. you SHOULD do recursion and but add an attribute to tag a product if it is a sub-category (1, 2, 3, 4, etc.) or sub-sub-category. then link that field (sub-category field) to another table that will link to another table that finally describes the additional attributes.


    i know that i didn't make sense, i'm even re-evaluating what i said. ??

    BUT THEN AGAIN, WOULD SUB-CATEGORIES HAVE MORE ATTRIBUTES THAN THEIR PARENT CATEGORIES, MAYBE NOT. then you wouldn't have to worry about the sub-category tables....just another table to describe the sub-category name...nothing more.

    dlg

  12. #12
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I usually make a 'products' 'categories' 'subcats' and then a 'prod_cats' table.

    The prod_cats table then holds the foreign keys for the products, categories and subcategories. This allows one product to be assigned to multiple subcats.

    This can allow for some flexibility, if only the foreign subcat ID is filled in, the foreign product belongs to the subcat (and the subcat table knows what maincat it belongs to). If only the foriegn cat id is filled in, it belongs to the maincat and not in a subcat.

    Tke it one step futher to assign the same subcat to many maincats (a cat_subcat table)

    But still, clients will always find some reason why one of their products is super-special and needs special treatment.

    So I make another table called 'clients_are_jerks'...


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
  •