2

I'm developing a database schema to handle collection of data and later, reporting on this data.

After a requirements discussion, it seems that either an entity-attribute-value (EAV) solution, or a flat table solution would be alright - since the data is somewhat sparse but not highly sparse.

However, user defined fields will become a must in the future, but I understand that querying and optimizing an RDBMS with EAV tables can become complex.

I've taken a look at the discussion here, and I was thinking an option similar to option 1 would be possible. For example, have a number of set fields, then a number of spare fields that users can define the labels of.

In terms of reporting, is there any downside in using this approach rather than using EAV?

Community
  • 1
  • 1
J3Y
  • 1,843
  • 1
  • 18
  • 27

1 Answers1

4

You will regret EAV, especially when it comes to reporting

  1. Make sure you're aware of existing data model patterns before you try anything: Ready to use database model patterns

  2. Familiarize yourself with Table Inheritance: How can you represent inheritance in a database?

  3. Consider allowing users to modify their own schemas: https://martinfowler.com/bliki/UserDefinedField.html

  4. EAV is almost always a really bad idea. If you still need custom fields after trying the above, use a blob type (like JSON or XML) with indexing: http://backchannel.org/blog/friendfeed-schemaless-mysql . Postgres's binary jsonb is fast and allows indexing/querying

Community
  • 1
  • 1
Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • I'll take a more in depth look into serialized LOB, as it does seem a little easier in terms of queryability. I don't think user modifiable schemas will be ideal, as fields may exist very briefly (e.g. a new field for a year that may not exist the next year). This makes EAV and JSON type fields more attractive, due to their dynamic nature. Additionally I've also seen this [EAV paper](https://www.ncbi.nlm.nih.gov/pmc/articles/PMC346624/), which shows a relatively common use in medical data colleciton. – J3Y Apr 12 '17 at 19:00