SQL model for flexible and configurable attributes

Question

I'd like to design an application which will eventually fuel a configurable data-driven form.

Each user should be able to customize what fields appear in their own forms, and configure new form types to collect different sets of data.

One user may require:

Name
Contact Number
Email

Another user might want:

Name
Organization
Address

I've heard that the **Entity Attribute Value (EAV) ** design pattern may be appropriate for this, but in my case basically all fields are going to be known initially. New field types may be added later, but it'll be a controlled process.

Is there an advantage of using a EAV design pattern, compared to using a SQL table with ~1000 columns where each user may toggle on/off columns depending on their requirements? Does EAV have query disadvantages compared to the single table?

Is there another approach I should be taking?

I am currently in the situation to implement exactly this: A free definable form with not many but very different fields (not known in advance). The main reason for me to use EAV is that I don't want to deal with the sheer mass of fixed columns and the customer doesn't want me as a developer to be involved with every new field. For a slow, evolving process though (where you want to be involved from time to time), I would consider using fixed columns. The drawback of EAV is the relatively complex way of saving and retrieving values with several joins involved, and worse performance. — IngoB, Apr 01 '17 at 21:46
@IngoB thanks for the insight, I was also considering EAV initially for it's future flexibility however it does seem to be sub-optimal for my use-case as it'll initially be a finite amount of domain specific fields. — J3Y, Apr 01 '17 at 21:48

score 2 · Accepted Answer · edited May 23 '17 at 10:30

You might like my presentation: Extensible Data Modeling with MySQL. It was done with MySQL in mind, but many of the concepts apply to any SQL database.

Or this past answer of mine on Stack Overflow: How to design a product table for many kinds of product where each product has many parameters?

I'm not a fan of the EAV solution for this kind of task. It introduces more problems than it solves. Read the link above for details, or this blog post of mine: EAV FAIL.

SQL model for flexible and configurable attributes

1 Answers1