Page 1 of 5 123 ... LastLast
Results 1 to 15 of 67

Thread: Modelling help

  1. #1
    Join Date
    Jul 2004
    Posts
    32

    Modelling help

    Hi guys,

    I want to be able to store different types of information for products in different categories.

    So Category A maybe TV's and category B maybe DVD players, I want to be able to store different types of information on each product in that category. So for TV I may want to store screen size etc.. and for DVD players I may want to store if it supports upscaling etc.

    Obviousely each peice of info will be of a different data type but the reason I ask how best to store this is because I will have lots of categories of products and the products will be used for filtering too. So I need to create a solution which is not too costly on performance.

    Thanks in advance, any guidance even to any online tutorial would be appreciated.

  2. #2
    Join Date
    Mar 2008
    Posts
    3
    an extension to the name-value modeling might work here

    THINGS
    1 television
    2 radio
    3 dvd player

    ATTRIBUTES
    1 width
    2 height
    3 weight

    MY_THINGS
    1 1 47 inches
    1 2 47 inches
    1 3 2 tons

    MY_THINGS primary key is compound of THINGS & ATTRIBUTES, clustered on THINGS first. Assuming you'll query on your thing more often than everything that has width. Create a view for people that need human-readable data

  3. #3
    Join Date
    Feb 2007
    Posts
    62
    EAV (as above) is a dreadful model and I would not recommend this.
    Some alternate solutions that are better suited to relational databases:

    - Have one monster table with columns for each possible attribute
    - Have one table per product
    - Have one table per broad category of product and some 'spare' columns of various types that are used according to the category
    - Same as above with EAV for other stuff, but do not allow searches on this data

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Possibly a candidate for the SubType/SuperType Model

    Thing(ThingID,ThingClassID,ThingDescription,ThingM odelNo,ThingPrice,.........)

    ThingClasses(ThingClassID,ThingClassDescription)

    DVDThings(ThingClassID,ThingID,DVDSpecific_1,DVDSp ecific_2,DVDSpecific_3....)
    RadioThings(ThingClassID,ThingID,RadioSpecific_1,R adioSpecific_2,RadioSpecific_3...)
    etc...........
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    defo a candidate

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Silly me, I would create a table per category
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2007
    Location
    Chicago, IL
    Posts
    82
    I would create a category table, a product table to store the attributes common among all products/categories, and then hang specific tables off the product table for specific attributes.
    Example:

    CATEGORY
    category_id
    category_name
    description

    PRODUCT
    product_id
    category_id
    product_name
    description
    price


    PRODUCT_TV
    product_id
    tv_specific_attribute

    PRODUCT_DVD
    product_id
    dvd_specific_attribute

    You get the drift...

    -A

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by architect
    I would create a category table, a product table to store the attributes common among all products/categories, and then hang specific tables off the product table for specific attributes.
    so, basically, the supertype/subtype model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Do people think it may be better to put the category_id in the PRODUCTS Tables as well ?

    eg.

    PRODUCT_TV
    product_id
    category_id
    tv_specific_attribute
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I like to include the category_id in the supertype AND subtype. I make the combination a unique index (i.e. a super key) and then I use BOTH the product_id and category_id in the relationship definition. Within each subtype I have a constraint on the category_id limiting it to a single value (DVDs only for the DVD table, HiFis only for the HiFi table). This way you handle the rule "a supertype can be related to one and only one subtype".

    Just my preference - this is the only declarative way to handle the mutual exclusivity that I know of.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that makes good sense to me, but the surrogate key nazis aren't gonna like it

    a compound pk? get outta town!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Why would we need a composite PK in the PRODUCT_TV Table (subtype) if the category_id had a constraint (allowing only one value) and possibly that value as a default?

    Possibly to remind us to include it in any Product Table UNIONS, JOINS etc ?
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I thought I was conceding some ground by using a surrogate for the category rather than a natural
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by GWilliy
    Why would we need a composite PK in the PRODUCT_TV Table (subtype) if the category_id had a constraint (allowing only one value) and possibly that value as a default?
    The reason is simply because you can only create relationships on combinations of columns that are declared as unique in at least one of the tables. We can infer that the combination of product id and category is unique if product alone is unique but SQL Server won't accept it then as part of the relationship definition. If this was not necessary then I wouldn't bother with the unique index on both columns, just on the product id.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Makes sense - Thanks Pootle

    What about .... say the SupplierID, would that sit in the PRODUCT Table or the PRODUCT_TV Table.

    Is it a Judgement call or can we simply say that "Any value that would logically exist against ALL SubTypes should be included in the PRODUCT table"?

    I'll stop at this because I think this (MSSQL Server) is the wrong place to discuss general modelling techniques.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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