CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-designentity-attribute-value
J3Y
J3Y

Reputation: 1853

SQL database design with some user defined fields

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?

Upvotes: 2

Views: 2381

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48246

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

Upvotes: 4

Related Questions