SQL database design with some user defined fields

Question

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?

In Postgres either use a hstore column or a jsonb

user330315
– user330315

2017-04-12 05:35:11 +00:00
Commented Apr 12, 2017 at 5:35 — user330315
– user330315, Commented Apr 12, 2017 at 5:35

Community · Accepted Answer · 2017-05-23 12:09:58Z

4

You will regret EAV, especially when it comes to reporting

Make sure you're aware of existing data model patterns before you try anything: Ready to use database model patterns
Familiarize yourself with Table Inheritance: How can you represent inheritance in a database?
Consider allowing users to modify their own schemas: https://martinfowler.com/bliki/UserDefinedField.html
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

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Apr 12, 2017 at 18:30

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J3Y Over a year ago

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, which shows a relatively common use in medical data colleciton.

Collectives™ on Stack Overflow

SQL database design with some user defined fields

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related