Entity-Attribute-Value table vs single table for storage of posts, events and pages

Question

I am trying to decide whether to just use individual tables with more fields for storing posts, pages and events or using tables with basic fields plus having EAV tables as well for fields that are optional. Also EAV would allow to add more 'attributes' to each entity without actually changing the database at all (just changing the application code slightly).

However, what I am worried about is performance. Would EAV decrease performance in such scenario (storing data like events)?

Also, is it worth adding an EAV table like event_meta to the database just in case I will need to add extra fields/attributes to the model later on (when specs change and the system has to be expanded)?

Currently I can think of two options:

1. Not using EAV tables and just having single tables for each entity type (posts, pages and events) and having all data (about 15 fields) in the entity table.

2. Use EAV tables to store optional data that is not usually entered by users (this would prevent the entity tables from having many empty fields) but store all required and crucial data in the entity tables.

Also, for option two I might actually be able to just have one generic table 'posts' to instead of 3 tables posts, pages and events and just use more attribute-value rows in the EAV table (and have a type field in the posts table). However that would probably reduce the performance even more.

Option 1 is much easier to implement. However option 2 might make it easier to extend the entities in the future as database would not have to ever be adjusted.

Another important thing to consider is performance. What happens when we have millions of rows in the events_meta. Every time someone would view an event the application would have to perform queries on both tables which would be quite big and I am afraid that would reduce performance.

So which option should I use for my situation?

Answer 1

Do not build a database in your database. The performance will suck and so will the code to manage it. Attributes that drive application logic should be real columns in the database on the appropriate table with a name matching their purpose. Use an EAV when you need to support an arbitrary number of user-defined attributes that are transparent to your application.

Schema changes are a part of database application maintenance. Attempting to avoid it leads to madness.

Answer 2

I agree with @kevin cline. We should have fixed and logic governing fields as Columns rather than attributes as we can easily optimize the performance having these fields as columns (indexing etc.). Now a days Performance is much more important than storage (storage is becoming cheaper), so we need to build our design which gives best performance. From my experience I feel we should make the fields as Attributes only if there is rapid requirement of creating new fields in Application OR if we need to log the actions at each Attribute\Field level. Unless until we do not have any one the above 2 special requirements we can always go with Normal Architecture (Not EAV)

Answer 3

The old saying is "you can put the logic in the code, or you can put it in the database" - the best choice is driven from the requirements. If the prime value of your app is to be totally flexible to the "user defined fields" concept, then go EAV for sure. On the other hand, however, as far as performance goes, if your DB is indexed & configured appropriately, 5 million rows is not a big deal - I've managed apps with hundreds of millions of rows of data in the healthcare industry. When the indexes/config are where they should be, even 300 million rows works quickly (except for batch loads :) ). Check out https://en.wikipedia.org/wiki/Entity–attribute–value_model