Lazycoder

You think the CSVDB is funny

Introducing the CSVDB – The Daily WTF

I did too. Until someone told me they were working on adding a “metadata table” to our application. Meaning they could hang arbitrary fields and data off of any table in the database. Then I saw this article.

Use the Entity-Attribute-Value (EAV) Model.
The EAV is a generalized form of row modeling, which basically means a single
table with three columns, an entity (such as a patient), an attribute
(such as an allergy, which is actually a pointer into a metadata table),
and a value for the attribute (e.g., penicillin). In EAV models, a single
fact is stored in an “entity attribute” table. By contrast, in a conventional
table that has one column per attribute, a row can store multiple facts. EAV
works well when the number of attributes that potentially apply to an
entity is significantly more than those that actually apply to an
individual entity. For instance, if a single patient can potentially have
hundreds of attributes, but typically each patient only has a dozen attributes,
then EAV may work well. Although EAV models are quite extensible, they are
typically considered much harder to manage by DBAs because applying standard
relational techniques such as normalizing, adding constraints, attaching
triggers, etc., doesn’t work very well. While I have used the EAV model with
great success in the past, I only suggest it for groups with very strong
understanding of relational data models that can understand the drawbacks as
well as the benefits.

He makes it almost sound reasonable. Cost effective even. I’m hardly a standards and practicies nazi, but I think I’d like to have a little bit more control over data that I might have to query.