4

I have a system that allows a person to select a form type that they want to fill out from a drop down box. From this, the rest of the fields for that particular form are shown, the user fills them out, and submits the entry.

Form Table:

| form_id | age_enabled | profession_enabled | salary_enabled | name_enabled |

This describes the metadata of a form so the system will know how to draw it. So each _enabled column is a boolean true if the form should include a field to be filled out for this column.

Entry Table:

| entry_id | form_id | age | profession | salary | name | country |

This stores a submitted form. Where age, profession, etc stores the actual value filled out in the form (or null if it didn't exist in the form)

Users can add new forms to the system on the fly.

Now the main question: I would like to add the ability for a user designing a new form to be able to include a list of possible values for an attribute (e.g. profession is a drop down list of say 20 professions instead of just a text box when filling out the form). I can't simply store a global list of possible values for each column because each form will have a different list of values to pick from.

The only solution I can come up with is to include another set of columns in Form table like profession_values and then store the values in a character delimited format. I am concerned that a column may one day have a large number of possible values and this column will get out of control.

Note that new columns can be added later to Form if necessary (and thus Entry in turn), but 90% of forms have the same base set of columns, so I think this design is better than an EAV design. Thoughts?

I have never seen a relational design for such a system (as a whole) and I can't seem to figure out a decent way to do this.

Improve this question
4
  • 3
    You need more related tables, not more columns. Why use CSV in a database? That is a good clue that you are going wrong. Commented Jul 1, 2011 at 15:24
  • @Orbling I agree, however, I don't think there exists a good relational schema for this problem. Somewhere is going to have to give in a little bit to make it work. Do you know of a schema that would work? Commented Jul 1, 2011 at 15:33
  • 1
    @Davis Dimitriov: It is quite a standard relational schema problem, just think wherever you have a list inside a list within the data, then you need a new table linked to the parent. See Alex Howansky's answer below for an example schema. Commented Jul 1, 2011 at 15:44
  • @Orbling this is not quite a standard relational schema problem though because relational design doesn't allow for such flexibility well. Alex's post follows the entity-attribute-value (EAV) design pattern, but it is known to have several flaws as well. I don't believe you can win them all, but I would love to be convinced otherwise Commented Jul 1, 2011 at 15:59

2 Answers 2

Reset to default
4

Create a new table to contain groups of values:

CREATE TABLE values ( id SERIAL, group INT NOT NULL, value TEXT NOT NULL, label TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (group, value) );

For example:

INSERT INTO values (group, value, label) VALUES (1, 'NY', 'New York'); INSERT INTO values (group, value, label) VALUES (1, 'CA', 'California'); INSERT INTO values (group, value, label) VALUES (1, 'FL', 'Florida');

So, group 1 contains three possible values for your drop-down selector. Then, your form table can reference what group a particular column uses.

Note also that you should add fields to a form via rows, not columns. I.e., your app shouldn't be adjusting the schema when you add new forms, it should only create new rows. So, make each field its own row:

CREATE TABLE form ( id SERIAL, name TEXT NOT NULL, PRIMARY KEY (id) ); CREATE TABLE form_fields ( id SERIAL, form_id INT NOT NULL REFERENCES form(id), field_label TEXT NOT NULL, field_type INT NOT NULL, field_select INT REFERENCES values(id), PRIMARY KEY (id) ); INSERT INTO form (name) VALUES ('new form'); $id = last_insert_id() INSERT INTO form_fields (form_id, field_label, field_type) VALUES ($id, 'age', 'text'); INSERT INTO form_fields (form_id, field_label, field_type) VALUES ($id, 'profession', 'text'); INSERT INTO form_fields (form_id, field_label, field_type) VALUES ($id, 'salary', 'text'); INSERT INTO form_fields (form_id, field_label, field_type, field_select) VALUES ($id, 'state', 'select', 1);
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

but translating columns into rows makes other things difficult, like querying Entries later (since Entry columns will also have to be converted to rows). And then everything has to be stored as a string, there can't be any constraints and validation is hard.
Validation shouldn't be tied to the database field types. What if you want to store LAT/LON, or a multi-select list, or an email address? Those can only be validated with code. Querying entries later is actually easier, you just loop over the results of a select, and you don't need to know what the schema looks like.
but then you have to select all results into memory and then parse them in code as opposed to using SQL to do reporting. That is why I don't believe that this is a more relational design
That's a pretty weak argument. It boils down to this -- using a column-based design that relies on schema updates for its primary functionality completely defeats the purpose of using a relational database. This type of non-normalized design is better suited to NoSQL stores like MongoDB. (Or hell, even CSV files, as Orbling mentioned.)
4

I think you are starting from the wrong place entirely.

| form_id | age_enabled | profession_enabled | salary_enabled | name_enabled |

Are you just going to keep adding to this table for every single for field you can ever have? Generically the list could be endless.

How will your application code display a form if all the fields are in columns in this table?

What about a form table like this:

| form_id | form description |

Then another table, formAttributes with one row per entry on the form:

| attribute_id | form_id | position | name | type |

Then a third table forAttributeValidValues with one row per attribute valid value:

| attribute_id | value_id | value |

This may seem like more work to begin with, but it really isn't. THink about how easy it is to add or remove new attribute or value to a form. Also think about how your application will render the form:

for form_element in (select name, attribute_id from formAttributes where form_id = :bind order by position asc) loop render_form_element if form_element.type = 'list of values' then render_values with 'select ... from formAttributeValidValues' end if end loop;

The dilema will then become how to store the form results. Ideally you would store them with 1 row per form element in a table that is something like:

| completed_form_id | form_id | attribute_id | value |

If you only ever work on one form at a time, then this model will work well. If you want to do aggregations over lots of forms, then the resulting queries become more difficult, however that is reporting, which can run in a different process to the online form entry. You can start to think of things that pivot queries to transform the rows in into columns or materialized view to pull together forms of the same type etc.

Improve this answer

2 Comments

+1 this is what I was talking about. do it has a pattern name or model name? I know as "dictionary"
I was actually researching recently to see if this was a known pattern, and I think it is known as EAV - entity attribute variable. Wikipedia has some interesting insights - en.wikipedia.org/wiki/Entity-attribute-value_model

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.