Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Have you also considered using XML in any way? –  SQLRockstar Sep 10 '13 at 19:19
add comment

1 Answer

up vote 3 down vote accepted

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?

share|improve this answer
    
Thanks, this seems to accomplish what I want. I'm going experiment with it. –  Bart Sep 11 '13 at 11:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.