Posts

Showing posts from 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 EAV versus Row modeling Downsides of EAV data model over class row model Opting classic row modeling or EAV data modeling?

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 bec…

EAV versus Row modeling

Image
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:
EAV(Entity-Attribute Value) Model
EAV versus Row modeling
Downsides of EAV data model over class row model
Opting classic row modeling or EAV data modeling?