Design question

1

Hi there,

I am trying to get a databse going and I have a qusetion:

I have some items that I have to track, and most of them have similar proprieties, yet there are some properties that apply only to some. for example:

  1. table of items would have:
    id, ser#, dateofPurchase, cost.....etc.
    a) Monitor would have everything + size , type, connector type...etc
    c) Cell phone same + IMEI, OS, ... etc.
    and so On.
    I have identified About 6 different add-on categories, and my question is how would I design table or tables?

Thanks

2

I would have one common table for attributes that (almost) all entries have. Then have a separate table for monitor, cell_phone, etc., with their custom data in it, naturally linked by the key(s) from the common table.

3

I've done it different-ways in different-projects.

Alternative to Scott's method would be to have all the "common" attributes in one table, and then a table of "Key/Attribute/Value" rows - one row per Atrribute per Item- where "Key" would be the ID of the items table, "Attribute" would be "Size, Type, Connector" for a "Monitor" and "IMEI, OS, etc." for a "Cell phone", and finally "value" would be the actual value.

I would then have a table of which Attributes are valid for each product Types with columns Type, Attribute and IsRequired

The data entry routine would then require anything flagged IsRequired, any others would be optional.

You would need to take a decision about how Data Entry (and/or Database validation rule constraints) handle an Item's Type changing. If you change Monitor to Computer then maybe the Height, Width, Length attributes are all still valid, but Pixels would not be. Personally I would keep all the previously entered Attributes until the user saves or somesuch. Users will change the Type by accident and then want to change it back and it would be a kindness not to have thrown away the attribute data by then! Also a user might choose Computer, fill in the Height, Width, Length and then when they get to Hard Drive Size realise that they should have chosen Monitor instead so, again, retaining the attributes would save them having to retype them.

We have Product Comparison in our eCommerce APP. Each Product has Attributes in a Key/Attribute/Value table, and we allow any type of product to be compared to any other. This might seem strange but, for example, an Outdoor Clothing and Equipment client sells tents, anoraks, camping cooking equipment etc. Although these are not directly comparable, in the normal "Which is better/cheaper" sense, for a camper the weight / volume of a backpack is important, so seeing the overall weight, and size, compared was helpful.

4

Thank you for suggestions. I have started doing it as Scott suggested by was not sure it that is OK. Kristen's approach is good for many variations but I would have only 6 or so. Once again thanks for your help.