Re: Design of tables
Posted by:
Rick James
Date: January 19, 2010 09:08PM
I would vote for multiple tables. The main table (possibly with an AUTO_INCREMENT PRIMARY KEY) would contain the price, manufacturer, a timestamp, and a few other fields. It might also have a product_category field.
Be sensible about normalization: The Manufacturer should be normalized out; the price and timestamp should not.
The other tables would have the same PK as the main table (but it would not be AUTO_INCREMENT, and would be missing lots of values). The "camera" table, for example, would have weight, zoom, pixels, etc.
If you lump film cameras and digital cameras into the same table, there might be some fields overlapping (max ISO, F-stop), some null (eg pixels). There would be little or no normalization in these tables.
NULLable fields are reasonable when something is optional. My example in cameras is debatable.
For one kind of product, color might be missing, for another it might be an ENUM, for yet another, it might be a SET.
"Size" might be in inches, and have 3 values. Or it might be in gigabytes.
1000 tables will work, but is probably a sign of mis-design.