Database Design – Entity-Attribute-Value Table vs Single Table for Storage

databasedatabase-designperformance

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?

Best Answer

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.

Related Topic