Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am trying to figure out a better way to decide table storage configurations whether it will use static configurations using tables or dynamic configurations using EAV (entity-attribute-value)

When creating database tables, forms, and reports, when do you decide that a program should use a normal database (wherein everything is statically configured, possibly final) or use entity-attribute-values (wherein all of the data are dynamic and can be customized)?

Does it all depend on the client/people using it? Is static-table-customized-programs only applicable for small and medium businesses? and dynamic-eav-customizable-field-programs (like Quickbooks) only for the general public or big businesses wherein they don't want another programmer to tamper again with their software?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Use EAV when the number of possible attributes is large, but the number of actual values is relatively small and cannot be predicted with any certainty.

An example of this is symptom capture for medical records. There can literally be hundreds if not thousands of possible symptoms, but the actual number of symptoms that any one patient might have is small relative to the total symptom pool.

In this scenario, it is impractical to have one column in the database for every possible symptom; many databases are not even capable of storing that many columns. Nor can you predict with any certainty how many symptoms any given patient might have, so you can't just add an arbitrary number of columns to the database for actual symptoms.

If you do either of these things, you will find that most of your database is empty, because you will have a vast swath of fields that are not used. It's a sparse matrix, basically. This is why spreadsheets are not actually stored as an array but as a sparse matrix, because there are so many empty cells otherwise.

The difficulty with EAV is in flattening the sparse matrix into a conventional format for reporting and other purposes, which is why such reports are typically done vertically, rather than horizontally. An invoice is an example of such a report: the product names are never listed out as columns, but rather appear as rows in the line item detail.

This is why sales inventory can also be considered a form of EAV (your customer is not going to buy every one of your products, but only a select few).

share|improve this answer
    
It seems like on this point of view, every reports and database tables should be EAV, because every object seems to be scalable in attributes. But about using static tables, if a client knows the needed attributes for the reports/customer-tables/forms/etc., you don't build EAV, but rather build a fixed static report for them. Then just revise them in the future (it's going to be easier the next time). Another thing is it is hard to train users to use EAV software. I'm trying to figure out if this is a safe method. Is this point of view right as well? Would you advise this? –  Franz Noel Dec 28 '12 at 18:04
1  
every reports and database tables should be EAV -- No. Many business domains are very well defined, even in the more "fuzzy" places. Take an Address record, for example. Seldom is an address so variable that we have to EAV that, and using EAV would make it very difficult to print mailing labels, so we simply provide enough columns in the record to accomodate all possible addresses. Remember, we're talking about adding custom attributes, and there are really no custom attributes needed in an Address record. –  Robert Harvey Dec 28 '12 at 18:08
    
Oh yes, I understand the part which tables to use EAV for and when not to use EAV. But, I'm thinking if it would be ok if a client request for a customized software; instead of using EAV (when supposed to), you use static tables, reports, and forms. –  Franz Noel Dec 28 '12 at 18:28
1  
Some applications use SQL DDL to allow clients to add their own custom fields. It can work, for simple customization scenarios. –  Robert Harvey Dec 28 '12 at 18:29
1  
Symptoms is a bad example for EAV. This can be solved with basic normalization, no EAV required. Create a "Symptoms" table. Establish a many-to-many relation between "Symptoms" and the Visit instance. –  mike30 Dec 28 '12 at 20:45
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.