6/28/2012

Opting classic row modeling or EAV data modeling?


Few of the circumstances are listed below where EAV scores over conventional tables:
·         The data type of individual attributes varies. Like in above example EAV is used because the requirements vary from client to client like some may want an extra address like added to existing address format.
·         The categories of data are numerous, growing or fluctuating, but the number of instances (records/rows) within each category is very small. Here, with conventional modeling, the database’s Entity-Relationship Diagram might have hundreds of tables: the tables that contain thousands/ millions of rows/instances are emphasized visually to the same extent as those with very few rows. The latter are candidates for conversion to an EAV representation.


Other Related Links:
EAV(Entity-Attribute Value) Model


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:

EAV versus Row modeling


Following are main features/downsides EAV has over classic row modeling technique.
  • A row-modeled table is homogeneous. It will always carry same type of data in contrast to EAV modeled table where this cannot be an issue.
  • The data type of the value column/s in a row-modeled table is pre-determined by the nature of the facts it records. In contrast, in an EAV table, the conceptual data type of a value in a particular row depends on the attribute in that row.
  • A Row modeling technique data increase only in one direction i.e. from Upside-down whereas in EAV data model data increase in both directions i.e. from upside down as well as from left-right.







Other Related Links: