Featured Post

Downsides of EAV data model over class row model

Flabbiness: Flexibility is great with EAV, but there will be no structure any longer. Typically, the reliability on the built-in database features such as referential integrity is lost. To guarantee that a column takes values only in acceptable range, integrity check needs to be coded inside the application.
Inefficient queries:  In cases where one would be required to execute a simple query returning 20 columns from a single table in classic row modeling technique, in EAV one ends up with 20 self-joins, one for each column. It makes for illegible code and dreadful performance as volumes grow

Features unavailability: Much of the machinery of modern relational databases will be unavailable and will need to be recreated by the development team. For e.g. System tables, graphic query tools, fine grained data security etc.
Other standard tools are much less useful:  Cursors in database functions do not return rows of user data since the data must first be pivoted. Users defined functions become large and are harder to develop and debug. Ad-hoc SQL queries of the data take much longer to write and the necessary joins are hard to specify so that data does not get missed.
The format is also not well supported by the DBMS internals:  The standard query optimizers for SQL do not handle the EAV formatted data well and much time will have to be spent on performance tuning for an acceptable production quality application. Having a few huge tables and many small ones can frustrate the DBMS code that tries to optimize disk layout.



Other Related Links:

Comments

Related Posts

Ex-HP CEO Hurd Pays $14 Million Bounty as Oracle Pledge Fee

The bug in the name of the C++ language.

Biography of The Rock 'n' Roll Drummer...John bonham....

Scrum Roles (Scrum Master, Product owner, Development Team) and collaboration between each other

Wi-Fi WPA2 Zero Day Vulnerability Found