Database Design – To EAV or Not to EAV?

database-designeav

I am working on an application which records data from different forms. New forms can (and will be) constructed dynamically by an admin. I am wondering what would be the correct way to save this data in a database? My first guess would be to use EVA, but considering all the bad publicity I am a bit hesitant.

Let's say I have two forms:

#form 1
description = TextField()
score = IntegerField()

#form 2
feedback = TextField()
NOR_score = IntegerField()

There is no way of predicting the number of possible attributes. I am playing around with two ideas, but maybe you'll be able to suggest a much better approach.

Idea I:

unique_id | entity | attribute   | value
1         | form 1 | description | test123
2         | form 1 | score       | 90
3         | form 2 | feedback    | blabla
4         | form 2 | NOR_score   | 5

Idea II:

unique_id | entity | value
1         | form 1 | {"description": "test123", "score":"90"}
2         | form 2 | {"feedback": "blabla", "NOR_score":"5"}

Thanks in advance for any suggestions.

Best Answer

Your example (idea II) looks like it shows how to store the response to the forms. Would you store the form descriptions (created by your admin) in the same fasion, as it:

id | name | value
-----------------------------------------------------------
1  | form1|{"question1":"description", "question2":"score"}

If you go this route, it means you have to store the full description of a form as a very long text string. You won't be able to reuse questions between forms. You won't be able to (easily) query to find which form had a specific question. It will be possible to have more interesting types of form questions such as multiple-choice, but it won't be pretty (and you won't be able to re-use option-sets).

I'm not sure why you think you need an EAV solution. A very simple schema could look like this:

form
----
  id
  name

question
--------
  id
  form_id
  question_text


question_answer
---------------
  id
  user_id
  question_id 
  answer_value

Data:

form
----
id | name
-----------
1  | form1

question
--------
id | form_id | querstion_text
-----------------------------
1  | 1       | description
2  | 1       | score

answer
------
id | question_id | answer_text | user_id
----------------------------------------
1  | 1           | test123     | 1
1  | 2           | 90          | 1

Would this not work?

Related Question