EAV Database Sheme

Question

Hello Stackoverflow comunity!

I believe that my question has to do with all db guru here!

Do you know the EAV DB Scheme ( http://en.wikipedia.org/wiki/Entity-attribute-value_model ) and what they say about the performing of this model.

I wonder, If I break this model into smaller tables what the result is?

Let's talk about it.

I have a db with more that 100K records. A lot of categories and many items ( with different properties per category ) Everything is stored in a EAV.

If I try to break this scheme and create for any category a unique table is something that will I have to avoid?

Yes, I know that probably I'll have a lot of tables and I'll need to ALTER them if I want to add an extra field, BUT is this so wrong?

I have also read that as many tables I have, the db will be populate with more files and this isn't good for any filesystem.

Any suggestion?

Thank you!

Answer 1

EAV DB schema is very flexible for adding more relational database's "columns" but at the cost of deteriorate the query performance and loosing your business logic which was kept in the relational database schema.

Because you have to create multiple views to actually pivot the result, which will cause the performance issue if the table contains billions of rows. And another nature of EVA schema is query is always made when you join the data table with the meta data table and there might be multiple joins on the same data table.

This is based on my experience.

Answer 2

As the primary structure in a database design, the structure will fail as the data grows. The way you know that a database schema does not fit the business model is when you need to query against for reporting. EAV's require many workarounds and non-native database functionality in order to get reasonable reports. I.e., you are constantly creating crosstabs/pivot queries for even the smallest query. All that processing to take the EAV and put it in a queryable format chews through database cycles and is highly prone to error. In addition, the size of the data is growing geometrically. If you have 10 attributes, 10 rows in a standard design will generate 100 EAV rows. 100 standard rows would equate to 1000 EAV rows and so on.

Database management systems are designed to handle lots of tables and this should not be a worry.

It is possible to create a hybrid solution where an EAV structure is part of the solution. However, the rule must be that you can never include a query [AttributeCol] = 'Attribute'. I.e., you can never filter on, sort on, restrict the range on any attribute. You cannot place a specific attribute anywhere in a report or onscreeen. It is just a blob of data. Combined with a good schema for the rest of the system, having an EAV that stores a blob of data can be useful. The key to making this work is enforcement amongst yourself and the developers never to cross the line of filtering or sorting on an attribute. Once you go down the dark path, forever will it dominate your destiny.