Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Database design: variable attributes

    Hello everyone,

    My apologies if this has been asked before, but to be honest I wouldn't know what search criteria to use.

    I've been struggling with a problem for the last couple of days and would really appreciate your input on this.
    I am by no means a database expert, so feel free to point out any mistakes I am making.

    I'm creating a webshop and have the following tables:

    Products:
    - productID
    - categoryID (linked to category table)
    - product
    - description

    Types:
    - typeID
    - productID
    - typecode
    - cost

    So each product can have multiple product types, so far so good.
    However, every product type can have multiple attributes.
    These attributes can vary for each product.
    The way I see it, there are 2 possible solutions:

    Create an attribute table:

    Attributes
    - productID (I think this would make querying easier)
    - typeID
    - attribute
    - value

    This would mean I would also need a table defining which attributes exist for a product? (for instance to generate input forms I need to know which fields to create)

    Structure:
    - productID
    - attribute

    So with 5000 products, each having an average of say 5 attributes and 5 product types, this would mean 125000 records in the attribute table.

    The other solution I have in mind would be separate type tables for each product instead of the one mentioned earlier:

    types_[productID]
    - typeID
    - productID
    - typecode
    - cost
    - attribute 1
    - attribute 2
    - etc.

    This would mean dynamically creating tables and adding/removing columns on mutations.

    This is the solution I chose at first and this works as it should (for now).
    But somehow that annoying little voice in the back of my head keeps nagging that it's not "clean".

    I guess I would rather go for the attribute table, but am a little worried about retrieving data (the queries would get rather complex I assume?) and performance in the future.

    On the other hand having 5000 separate tables doesn't seem right either.

    Any advice from the wise?

    (Sorry for the long post, just trying to be as clear as I can)

    Thanks for your time!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I'd go with the attribute table, but ONLY where it makes sense. if you have common information across the board store it in the the product table (things like price, availability, manufacturer etc.

    but I think you need to balance the benefits of the attribute model against the disadvantages. if you need data integrity and data coherence use a traditional normalised design dont use the attribute model.

    I've used this attribute model in a few applications, but I'm not claiming its wonderful or the solution to the 'the' problem (what ever 'the' problem is).

    it works very well if you want to list attributes of various products, and you either don't know or don't want to give full details.

    eg if you run a white goods electrical wholesaler you may have a 'pool'/table of products which stores common data (eg size, height, weight etc, cost & maker) and then have an attribute table detailing what each machines manufacturer has told you)

    say your customer wanted to compare clothes washers, is a combi washer drier a washer, a drier or a product in its own right. the customer probably doesn't care they want to see what features the products offer. they may want to compare washer A drier D and washer drier X and expect you to display a table of data matching relevant attributes on the same row.

    5000+ tables is a nightmare... don't even think of going down that route.
    as to the amount of data in the attribute table.. does it matter what size it is if it stores the data that is needed, in a manner that is appropriate to the task.
    Attached Thumbnails Attached Thumbnails webshopcrudemodel.png  
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2008
    Posts
    6
    First of all, thanks a lot for taking the time to reply healdem, I really appreciate it!

    if you have common information across the board store it in the the product table (things like price, availability, manufacturer etc.
    I understand what you mean, but in my case wouldn't that be stored in the types table?
    For instance:

    Products table:
    Code:
    productID  |  categoryID      |product             |  description
    1              |   1 (washers)    |  whirlpool VT     |      blahblah
    2              |   2 (televisions) |  Pioneer LX5090 |      more blahblah
    Producttypes table:
    Code:
    typeID | productID | typecode     | cost   | weight | (more common data)
    1        |  1            |    XR000232 |  1200 | 80Kg      
    2        |  1            |    XR000233 |  1450 |  80Kg      
    3        |  2            |    XS000110 |  1850 |  20KG
    Attributes table:
    Code:
    attributeID | typeID | atrribute         | value
    1              |  1       |    energy class |  A
    2              |  1       |    soundlevel    |  3
    3              |  2       |    energy class |  B
    4              |  2       |    soundlevel    |  4
    5              |  3       |    resolution     |  1920 x 1080
    6              |  3       |    HMDI           |  3
    Structure table:
    Code:
    structureID | productID | attribute
    1              |   1           |    energy level
    2              |   1           |    sound level
    3              |   2           |    resolution
    4              |   2           |    HDMI
    Am I on the right track here, or is this not what you meant?

    Edit: sorry for messing up the layout, guess that doesn't work.
    Last edited by gvee; 10-13-08 at 12:02. Reason: [CODE] tags added to retain formatting

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    to me a product type is a category of product ferisntance
    Code:
    |---white goods
         |---Laundry
               |--- Washer
               |--- Tumble Drier
               |--- Spin Drier
               |--- Washer Drier
          |---Refigeration
                |--- Refrigerator
                |--- Freezer
                |--- combi Frige Freezer
          |---Cookers
          |---Vacuum
    |--- Vehicles
           |--- Cars
    ...etc

    attribute Group would be things that describe the product, say it was a multi function printer
    Code:
    Printer Characteristics
    |--- Mono printer
          |--- Resolution
          |--- print speed
    |--- colour printer
          |--- Resolution
          |--- print speed
    |--- Scanner
    |--- fax modem
          |--- CCIT
          |--- Max Speed
    in the attribute table
    id expect to see something like
    print speed 10 ppm
    resolution 2400 x 2400
    or
    resolution, horizontal 2400
    resolution, vertical 2400

    if the printer didn't have a modem, there would be no record of it in the Attributes table, or if you wanted to it could appear as N/A, or not available or what ever

    theres a optional parent group so you can group things to gether logically
    in my design theres also a sort order so that you could group together things in what ever sequence you decided was relevant

    this model is fine providing you don't need to enforce data integrity and do need flexibility in what information you can store. doing comparisons can be tricky, but if you are repreating the information to the viewer than you don't need to concern yourelff too much. storing the attrivute data as a varchar allows you to decide if its numeric or character or date or whatever... the system doens't cre, becasue its not really significant in terms of how the information is represented. the down side is that its quite possible for typos to rep in

    eg a line could get transcribed (its perfectly valid to store say 'yes' as the attribute data of print speed, or 10ppm as the resoluton). thats is soemthing you have to guard against. you could extend the attribute group definition to include say a format rule, or indictae what is an acceptable value

    so you need to guard that the representation is for guidance and should not be relied on. and point customers to the manufacturers website for full details.
    Last edited by healdem; 10-13-08 at 12:23.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2008
    Posts
    6
    Well that is basically the way I have it right now, however I came to the conclusion that attributes (which I now have on category level and what you mean by product type) should be on product level.

    In this webshop the following will occur more often than not:
    Product X
    - Type A: 100 pieces
    - Type B: 150 pieces

    Product Y
    - Type A: Length 100mm radius 8mm
    - Type B: Length 120mm radius 10mm

    Basically there are a lot of products (mostly tools) that have different types/versions/materials/pieces/etc.
    And when the user selects one product, the available types should be presented.
    (If that makes sense).
    So thats why I thought I needed to use both products and producttypes.
    How would that fit in your example? Or should I just abandon the whole product/type setup alltogether?

    Thanks again, for your help.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    fine it may well be terminolgy

    a product type to me identfiies the type of product, I'd suggest an optioanl parent category so you can apprropriately identify the type of products and where appropriate its family tree

    certain information is common to all products (eg manufacturer, cost etc.. that sort of stuff in my books should be stored in the item/product table and if it must exists put a constraint on that element (eg cost must exist and must be more than...)

    the attribute group is called group but it coudl just as equally be attribute type. that stores the variable stuff.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2008
    Posts
    6
    Got it, I think I know how to proceed (or rather start over) now.

    Thanks a lot for your help healdem, I'm sure it will save me a lot of headaches in the future!

  8. #8
    Join Date
    Oct 2008
    Posts
    6
    So after reading about every article/post I could find about this topic, I am back to where I started.

    To summarize I (think I) have 3 options:

    - EAV : However, the more I read about this, the more it seems that people strongly advise against this.
    I read countless of arguments, complex SQL being one of the most named.
    (Including the "heated" discussion on this forum)

    - Supertype/subtype: My current setup which seems to be working fine, but this would mean I would get a table for every product, resulting in a huge number of tables once I get a large number of products.

    I just can't seem to find that piece of information that justifies chosing either one of these options.
    Which brings me to my third option:

    - Using one big product table with a fixed number of attribute columns (e.g. att1, att2, att3, etc.)
    They way I see it, this option has a few disadvantages:
    1. Limiting the number of possible attributes to, for instance 10 (this wouldn't be a big problem imo).
    2. All attributes will be of the same data type (NVARCHAR I presume).
    3. Lots of null values (again, not a big problem, though not very "clean" either).

    I am really at a loss here, it seems every one advises another approach, but it seems hard to find the "right" way (if there even is one, that is).

    I would REALLY appreciate some more opinions on this.

  9. #9
    Join Date
    Oct 2008
    Posts
    1
    I would NEVER go with individual tables for each product, you will drive yourself crazy!!

    I am having a similar problem, in my case i have groups of products however i have to allow for infinite attributes for these. If uits of any help the way i am planning to go about it is:

    Products_table
    id
    name (Honda acura)
    description (Mint condition)
    price
    product__type_id

    product types
    id
    name (car, motorcyle)

    attributes:
    id
    name (number of wheels, tank capacity...)

    product-to-attributes
    product_id
    type_id
    value (4, 53 Gallons , etcc..)


    if you can group your products into types, and these types have the similar attributes you could create a table products-to-types to simplify data entry. The other way you would have to display a dropdown with the list of attributes and then a field for its value.

    hope this helps. Its helped me in creating a similar dbase. Good luck!
    Last edited by lucardo; 10-20-08 at 18:10.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Why do you need an id field in the product types and attributes table?

    I'm assuming that the name field should be unique in these tables, so it is perfectly acceptable to have this as the primary key (and the only field in the table!).
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a table with only one column, a natural primary key, drives many developers nuts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2008
    Posts
    6
    That's what I thought, I decided to go with the third option (using one big product table with a fixed number of attribute columns).

    The advantage (imo) is that I know beforehand what limitations and problems exactly, I'll run into.

    The EAV model just seems to "un-databaselike" to me.

Posting Permissions

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