Database schema-related problem

I have a kind of theoretical question about databases. To make it more concrete I have thought up an example case.

Suppose I have a store with products. I have lots of different products. Not every product has the same applicable properties. For instance, I could define the size of a harddisk in gigabytes, but cannot use that same property on a CPU, simply because it does not apply. What I want is a database where I can add properties to products dynamically. The only thing I can come up with is the following:

One product table with an ID, a Name and a Description.

One propert开发者_运维知识库ies table with an ID, Product_ID, Property and a Value.

This way I would potentially get a gigantic, I-suppose-not-so-efficient, table of properties. This has been bugging me for a long time now. Does anyone know a better solution to my problem?

This is actually moving towards Sixth Normal Form, it is just that people like you who do not have the academic or experiential background do not know the (a) name for it and (b) the rules and the caveats. Such people have implemented what is commonly know as Entity-Attribute-Value or EAV. If it is done properly, it is fine, and there are many thousands of medical system out there carrying diagnostic and dosage info in such tables. If it is not, then it is one dog's breakfast to use and maintain.

1. First make sure you have Product in true and full 5NF.

2. Always use full Declarative Referential Integrity; CHECK constraints and RULES.

3. Never put all that into one table with a VARCHAR() for Value. Always use the correct (applicable) DataTypes. That means you will have several tables, one each per DataType, and there is no loss of control or integrity.

4. Likewise any Associative tables (where there is a multiple reference to another table [eg. Vendor] ) must be separate.

   • I am providing a Data Model which has the full control discussed; it includes a simple catalogue which can be used for verification as well as navigation. You need to add every CHECK Constraint and RULE to ensure that the data and referential Integrity is not lost. That means, eg:
     • for the CPUSpeed column, which is stored in ProductDecimal, CHECK that it is in the proper range of values
     • for each sub-Product table CHECK that the DataType is correct for the ProductType-ColumnNo combination
   • This structure is way better than most EAV, and not quite the full 6NF.
     .

5. Keep all the mandatory columns in Product; use the sub-Product tables for optional columns only.

6. For each such (eg Product) table, you need to create a View (dotted line), which will construct the 5NF rows from the EAV/6NF tables. You may have several Views: Product_CPU, Product_Disk.

7. Do not update via the View. Keep all your updates transactional, in a stored proc, and insert or update each of the columns (ie. the Product and sub-Product tables which are applicable, for each particular ProductType) together.

8. Gigantic ? Commercial databases (not the freeware) have no problems with large tables or joins. This is actually a very efficient structure, and allows very fast searches, because the tables are in fact column-oriented (not row-oriented). If the population is gigantic, then it is gigantic, do your own arithmetic.

9. You need one more table, a Lookup table for Property (or Attribute). That is part of the catalogue, and based on ProductType

The better solution is to go for full, formal Sixth Normal Form. Not necessary if you have only one or a few tables that require optional columns.

To be clear:

• Sixth Normal Form is The Row consists of the Primary Key and, at most, one Attribute.

• This is 6NF (for at least the Product table cluster), then Normalised again (Not in the Normal Form sense) by DataType, to reduce the no of tables (otherwise you would have one table per Attribute).

• This retains full Rdb control (FKs, constraints, etc); whereas the common EAV types don't bother with the DRI and control.

• This also has the rudiments of a catalogue.

Link to Product Cluster Data Model

Link to IDEF1X Notation for those who are unfamiliar with the Relational Modelling Standard.

Update

You might be interested in this ▶5NF 6NF Discussion◀. I will write it up at some point.

Initially I'd have suggested you have a productproperty table to model the relationship between products and properties. This would allow you to associate many products with a particular property.

However, I'm not keen on the idea of having a value stored alongside each property as a 1:1. It might be best if you have a propertyvalue table that associates a property with a value. Then, you'd ditch the productproperty table in favour of having a richer productpropertyvalue table which could fully describe the relationship between a product, it's properties and their values.

Perhaps then you could have the following:

product => (ID (unique key), Name, Description)
property => (ID (unique key), Description)
propertyvalue => (ID (unique key), propertyID (foreign key), value)
productpropertyvalue => (ID (unique key), productID (foreign key), propertyValueID (foreign key))

Of course, property values could be complex rather than simple strings or integers, but hopefully this takes you in the right direction.