CODEWITHSUNDEEP
sqldatabasepostgresql
J3Y
J3Y

Reputation: 1853

SQL model for flexible and configurable attributes

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:

Another user might want:

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?

Upvotes: 2

Views: 685

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 2

Related Questions