EAV Database Scheme

Question

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?

Answer 1

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 it 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 CPU 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 on-screeen. 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.