StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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.</p> <ol> <li><p>First make sure you have <code>Product</code> in true and full 5NF.</p></li> <li><p>Always use full Declarative Referential Integrity; <code>CHECK</code> constraints and <code>RULES</code>.</p></li> <li><p>Never put all that into one table with a <code>VARCHAR()</code> 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.</p></li> <li><p>Likewise any Associative tables (where there is a multiple reference to another table [eg. Vendor] ) must be separate.</p> <ul> <li>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 <code>CHECK</code> Constraint and <code>RULE</code> to ensure that the data and referential Integrity is not lost. That means, eg: <ul> <li>for the <code>CPUSpeed</code> column, which is stored in <code>ProductDecimal</code>, <code>CHECK</code> that it is in the proper range of values</li> <li>for each sub-<code>Product</code> table <code>CHECK</code> that the DataType is correct for the <code>ProductType-ColumnNo</code> combination</li> </ul></li> <li>This structure is way better than most EAV, and not quite the full 6NF.<br> . </li> </ul></li> <li><p>Keep all the mandatory columns in <code>Product</code>; use the <code>sub-Product</code> tables for optional columns only.</p></li> <li><p>For each such (eg <code>Product</code>) 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: <code>Product_CPU</code>, <code>Product_Disk</code>.</p></li> <li><p>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 <code>Product</code> and <code>sub-Product</code> tables which are applicable, for each particular <code>ProductType</code>) together.</p></li> <li><p>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.</p></li> <li><p>You need one more table, a Lookup table for <code>Property</code> (or Attribute). That is part of the catalogue, and based on <code>ProductType</code></p></li> </ol> <p>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. </p> <p>To be clear:</p> <ul> <li><p>Sixth Normal Form is <strong>The Row consists of the Primary Key and, at most, one Attribute.</strong></p></li> <li><p>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).</p></li> <li><p>This retains full Rdb control (FKs, constraints, etc); whereas the common EAV types don't bother with the DRI and control.</p></li> <li><p>This also has the rudiments of a catalogue.</p></li> </ul> <p><strong><a href="http://www.softwaregems.com.au/Documents/Student%20Resolutions/Bram%20DM.pdf" rel="nofollow">Link to Product Cluster Data Model</a></strong></p> <p><strong><a href="http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf" rel="nofollow">Link to IDEF1X Notation</a></strong> for those who are unfamiliar with the Relational Modelling Standard.</p> <h2>Update</h2> <p>You might be interested in this <a href="http://www.softwaregems.com.au/Documents/Article/Sixth%20Normal%20Form/5NF%206NF%20Discussion.pdf" rel="nofollow"><strong>▶5NF 6NF Discussion◀</strong></a>. I will write it up at some point.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload