table design help plz

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am designing the database structure for an online store that carries a wide variety of products. For example, hats, briefcases, pens, etc

Under normal circumstances I would put all the products in one table, but in this case each product type has specific information that pertains only to that type. For example hats has a "size" but pens and briefcases do not. Briefcases have "height" "width" etc, and hats and pens do not (at least not a height and width that need to be recorded). Though they do all have properties that are common, like "color"

So, my plan was to have a "Hats" table which would contain only products that are hats, and likewise a "briefcases" table, "pens" table, etc. One for each product type

My question is
Is this the best way to go about solving this problem (conforming to industry standards)
If so is it advisable to have a master products table containing the product ID, and the product type
 
I am designing the database structure for an online store that carries a wide variety of products. For example, hats, briefcases, pens, etc.

Under normal circumstances I would put all the products in one table, but in this case each product type has specific information that pertains only to that type. For example hats has a "size" but pens and briefcases do not. Briefcases have "height" "width" etc, and hats and pens do not (at least not a height and width that need to be recorded). Though they do all have properties that are common, like "color".

So, my plan was to have a "Hats" table which would contain only products that are hats, and likewise a "briefcases" table, "pens" table, etc. One for each product type.

My question is:
Is this the best way to go about solving this problem (conforming to industry standards)?
If so is it advisable to have a master products table containing the product ID, and the product type?
Click to expand...

This sounds like a perfect case for "Subclassing". You'ld have one
table, Products, with a unique ProductID and any fields that pertain
in common to all products (unit price, quantity in stock, etc.).

This table would be related one-to-one to specific product tables,
also with ProductID as their primary key; e.g. a Briefcases table with
fields for height, width, depth and weight or whatever.
 
Back
Top