Should I make specification table referenceable?

Question

Since I know there are lots of expert database core designers here, I decided to ask this question on stackoverflow.

I'm developing a website whose main concern is to index every product that is available in the real world, like digital cameras, printers, refrigerators, and so on. As we know, each product has its own specifications. For example, a digital camera has its weight, lens, speed of shutter, etc. Each Specification has a type. For example, price (I see it like a spec) is a number.

I think the most standard way is to create whatever specs are needed for a specified product with its proper type and assign it to the product. So for each separate product PRICE has to be created and the type number should be set on it.

So here is my question, Is it possible to have a table for specs with all specs in it so for example PRICE with type of number has been created before and just need to search for the price in the table and assign it to the product. The problem with this method is I don't see a good way to prevent the user from creating duplicate entries. He has to be able to find the spec he needs (if it's been added before), and I also want him to know that the spec he finds is actually is the one he needed, since there may be some specs with the same name but different type and usage. If he doesn't find it, he will create it.

Any ideas?

---------------------------- UPDATE ----------------------------

My question is not about db flexibility. I think that in the second method users will mess the specs table up! They will create thousand of duplicate entries and also i think they wont find their proper specs.

Answer 1

My suggested data model based on your requirements:

SPECIFICATIONS table

• SPECIFICATION_ID, pk
• SPECIFICATION_DESCRIPTION

This allows you to have numerous specifications, without being attached to an item.

ITEM_SPECIFICATION_XREF table

• ITEM_ID, pk, fk to ITEMS table
• SPECIFICATION_ID, pk, fk to SPECIFICATIONS table
• VALUE, pk

Benefits:

1. Making the primary key to be a composite ensures the set of values will be unique throughout the table. Blessing or curse, an item with a given specification could have values 0.99 and 1.00 - these would be valid.
2. This setup allows for a specification to be associated with 0+ items.

Answer 2

I have just finished answering http://stackoverflow.com/questions/1655202/dynamic-table-generation which discusses a similar problem. Take a look at the observation pattern. If you replace "observation" by "specification" and "subject" by "product" you may find this model useful -- you will not need Report and Rep_mm_Obs tables.