Entity Attribute Value (EAV) frameworks?

Question

I'd seen Entity Attribute Value in lots of contexts before I actually learnt what its name was. Its that technique that often crops up when instead of storing data in database columns you 'flip it' and have a table with Entity, Attrbute, Value columns and each piece of data becomes a row in that table. Sometimes its also known as 'Open-Schema'.

Its good for some things, bad for other things. This wikipedia article has a good discussion of the theory behind it.

It seems like the sort of oft-used technique that should have Frameworks or Engines or NoSQL Databases or general software tools to build and support it.

So, do you know of any? I'm particularly interested in the Microsoft stack (.Net, SQL Server, etc), but also in other technology stacks.

For example, here's a project to build an ASP.NET EAV engine that is exactly what I'm looking for, but apparently never got started.

Answer 1

If you can live with the drawbacks of NoSQL databases, the best way to approach the EAV pattern is with a NoSQL alternative like CouchDB or MongoDB. These databases offer a "schemaless" design which allows each row to have its own schema. Doing an EAV with a traditional RDBMS is asking for trouble as the querying becomes very difficult and performance suffers the larger the dataset.

An alternative that I have used successfully in the past is to combine an RDBMS with a NOSQL variant (MySql and MongoDB). I use MySQL to store the EAV data (gaining the transactional integrity), and I use MongoDB as a reporting store to get around the querying issues with the EAV model.