3

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?

4

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.

  • Thank you for your answer. So if in the future I need to add a few more columns to extend functionality, it would still be better then EAV? For instance, if a table has 1 million records, adding 5 optional fields to the events table will add 5 million empty fields to the database. Using EAV would add 0 empty fields. This is the only concern I have. Do you think even in this situation it would still be better to add columns to the appropriate table then to just add a event_meta table? – alwaysStuck Aug 6 '14 at 15:58
  • @alwaysStuck If you want to use relational databases, you should adhere to normal forms. If you want to use no-sql databases, that are key-value-based, non relational type of databases, you can do as you are proposing here. Also a great number of optional fields in an entity is a bad design, indicating an extra entity is needed. For example a doctor-guild-number in the person table will be empty for almost everyone, a doctor-table with a one-one relationship with person should hold all attributes only applicable to doctors. – Tulains Córdova Aug 6 '14 at 16:07
  • 1
    @alwaysStuck: There's nothing wrong with using a SQL database table as a Key/Value store or EAV, if Key/Value or EAV are the right tools for the task. – Robert Harvey Aug 6 '14 at 16:44
  • 1
    @alwaysStuck -- one thing we've used successfully for things like this would be to have an extra metadata field that is just a serialized object. The key caveat there is it works great if you never, ever need to query on the contents. – Wyatt Barnett Aug 6 '14 at 16:54
  • @alwaysStuck: existing table rows are not modified when a new column is added to the table. – kevin cline Aug 6 '14 at 16:59
0

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)

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.