I am a dba and a database design was thrown over the wall at us. The designer maintains this design is in 3rd normal form. I know this isn't true the database contains structures that follow a metadata model. Can anyone point me in the direction of some documentation to de-bunk the persecption that this model is a normalized model. I do understand how the model should look. Please note The example I gave is not the exact design.
It looks to be in at least 3NF, but normalization isn't the issue, it's lack of design. This is a variation of the entity-attribute-value model. EAV is a simple schema, hence its appeal. However, it discards much of the convenience, robustness and efficiency of the dbms. It discards appropriate data types for a general purpose varchar (or similar) 'value' field. This eliminates validation and optimization by the dbms and makes indices, at the very least, inefficient. Constraints become difficult, if not impossible, to define. Complex queries often need to join this monster value table to itself multiple times to reconstruct what should have been the design of database. If not self-joins, then possibly a high number of small round trips which increases the load on the infrastructure.
It's possible for a program to dynamically alter tables according to need, by issuing DDL like ALTER TABLE instructions to the dbms. Unlike EAV, this technique keeps strict bounds on data integrity and you're forced to play by the rules. Also unlike EAV, your data will be well-defined.
It doesn't model the business domain at all, unless one's business domain is labels. It's not relational either (in the mathematical sense), but then again, neither are SQL dbms's in general. However, you should focus on pragmatic issues rather than purity.
Last edited by reaanb; 03-05-12 at 15:11.
Reason: Expanded my answer
Thank you so much. My problem is that I am the physical dba. It falls to me to optimize this data. Based on it's design there isn't much I can do. Let me tell you performance is a dog. To do the simplest query involves a 10 level join, with 5 of the joins on the same table.