Relational Design: Column Attributes

Question

I have a system that allows a person to choose the type of form that they want to fill out from the drop-down list. This shows the remaining fields for this particular form, the user fills them in and sends the record.

Table of forms:

| form_id | age_enabled | profession_enabled | salary_enabled | name_enabled | 

Form metadata is described here, so the system will know how to draw it. Thus, each _enabled column is a boolean true if the form should contain a field that should be filled for this column.

Entry table:

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

The submitted form is stored here. Where is age, profession, etc. Stores the actual value filled in the form (or null if it does not exist on the form)

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

Now the main question: I would like to add the opportunity for the user who is developing a new form to include a list of possible values ​​for the attribute (for example, a profession is a drop-down list of 20 professions, and not just a text field when filling out a form). I cannot just save a global list of possible values ​​for each column, because each form will have a different list of values ​​for selection.

The only solution I can come up with is to include another set of columns in the form table, for example professional_values, and then save the values ​​in a delimited format. I am worried that a column may one day get a large number of possible values, and this column will get out of hand.

Please note that new columns can be added later to the form if necessary (and therefore recording in turn), but 90% of the forms have the same basic set of columns, so I think this design is better than the EAV design. Thoughts?

I have never seen a relational design for such a system (in general), and I cannot find a suitable way to do this.

Answer 1

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 instance:

 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 the dropdown selector. Then your form table can refer to which group the particular column is using.

Note that you must add fields to the form through rows, not columns. Ie, your application should not adjust the scheme when adding new forms, it should only create new lines. So make each field its own line:

 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); 

Answer 2

I think you start from the wrong place entirely.

 | form_id | age_enabled | profession_enabled | salary_enabled | name_enabled | 

Are you going to continue to add this table for each individual field that you have ever had? In general, the list can be infinite.

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

How about a form table as follows:

 | form_id | form description | 

Then another table, formAttributes with one row per record in the form:

 | attribute_id | form_id | position | name | type | 

Then the third table forAttributeValidValues ​​with a valid value of one row for each attribute:

 | attribute_id | value_id | value | 

It may seem like more than a start, but it really isn’t. Think about how easy it is to add or remove a new attribute or value in a form. Also think about how your application will display 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; 

Then the dilemma will become how to save the form results. Ideally, you would save them with 1 row in a form element in a table that looks something like this:

 | completed_form_id | form_id | attribute_id | value | 

If you work only one form at a time, then this model will work well. If you want to perform aggregation across multiple forms, the resulting queries become more difficult, however this is a report that can be run in another process to be entered into an online form. You can start thinking about things that summarize queries, to convert rows to columns or a materialized view, to combine forms of the same type, etc.