I'm working on a project that shares many of the same demands as this project described by this project for Yale university (frequent adding and removing of columns, objects that logically belong to the same structure but that might collect very different attributes) and as a result I began looking more heavily into EAV/CR models (where the attributes belonging to a certain table are collected in a separate attribute table which basically provides the attribute description while the values are collected in yet another table).

Yet when I study conventional programming/database literature most naturally advises strongly against it.

I was wondering if from a database POV it had already been decided that this should never be done? Or when it is done, what the thresholds usually are for when it becomes indeed the best choice at least for some tables (new columns added several times a day/month/year; flexibility for the user to create their own studies where on a very loose study structure they then decide which measurements are taken in their study; automatic form creation based on table description). Are there literature or tools which help doing EAV effectively? (one A and V table per table-table? What about number table, string table etc distinction?) What about other types of database (possibly non-relational)?

It seems that there is quite a demand for EAV type flexible architecture in the scientific field. At least that is where I found most of the literature on the subject.

Though I would naturally be very happy to receive any pointers I mostly wanted to know whether it is a dead end (if it should generally never be done under any circumstances) for my group to look into.

last word: dont*



*unless like virtually every rule in every sphere of life you have to

by no means is EAV a "dead end"

however, that article you linked to itself says right up front --you should be okay as long as you are going in with your eyes open to the difficulties that you're going to encounter

here are two articles which describe what's ahead for you --

• Dave's guide to the EAV
• OTLT and EAV: the two big design mistakes all beginners make

So, would a case where users are allowed to create their own research study templates be one of those cases where EAV might be appropriate?

What alternatives are there? A new table for each study that is created? Wouldn't that make it rather hard to search/access/optimize the new table?

One huge table which has columns for every possible thing that can be measured in a study and the user "templates" it in a way that they choose which columns they want to visible to them and all other values are set to NULL, table gets wider by one column every time somebody thinks of a new value to be measured? But where and how would be the best way to save which columns want to be "seen" by a particular user/study?






(Yes I realize that in a way this is databases within database; )

No, it would make it easier to search, access and optimize. Attributes that are common to more than one study can still go into one table. Attributes that are unique to one study or another should go in the tables for those studies. (I'm assuming only one table per study, which may or may not be the case I guess - tables need to be identified by common keys as well as common attributes).

McGoveran and Date call this the Principle of Orthogonal Design and if you follow it then you will have minimal repetition of attributes in your schema. Orthogonal Design reduces redundancy, means you need less code to access any given attribute and because attributes are consolidated it is easier to create the right constraints and indexes to support them.

EAV is fine, but don't roll-your-own.
Use a mature ready-made solution which will mitigate the main valid argument against it... relatively slow performance. I'm not saying that all EAV systems become too slow, but unless certain steps are taken it probably will be.
Creating your own requires quite a lot of upfront building work so use an EAV system that's already built and can deliver good caching for speed.
Many large, successful, proprietary solutions use EAV, but rarely promote that fact. As a general rule of thumb, any system which allows an admin user to create, alter or remove tables (objects, content classes, content types, etc.) and their fields (attributes, properties, etc.) via a simple UI, at run-time is probably using some form of EAV model.
There are mature development frameworks available that provide this model for general application development.

yeah, we see users of these systms occasionally here, asking for help with their SQL, hopelessly lost in how to assemble even a single a row of information...

their table names usually start with jos_

They probably a) don't understand this type of schema and b) are avoiding to use the API supplied with it.