database - Relational Design: Column Attributes -

i have system allows person select form type want fill out drop down box. this, rest of fields particular form shown, user fills them out, , submits entry.

form table:

| form_id | age_enabled | profession_enabled | salary_enabled | name_enabled | 

this describes metadata of form system know how draw it. each _enabled column boolean true if form should include field filled out column.

entry table:

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

this stores submitted form. age, profession, etc stores actual value filled out in form (or null if didn't exist in form)

users can add new forms system on fly.

now main question: add ability user designing new form able include list of possible values attribute (e.g. profession drop down list of 20 professions instead of text box when filling out form). can't store global list of possible values each column because each form have different list of values pick from.

the solution can come include set of columns in form table profession_values , store values in character delimited format. concerned column may 1 day have large number of possible values , column out of control.

note new columns can added later form if necessary (and entry in turn), 90% of forms have same base set of columns, think design better eav design. thoughts?

i have never seen relational design such system (as whole) , can't seem figure out decent way this.

create new table 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 values (group, value, label) values (1, 'ny', 'new york'); insert values (group, value, label) values (1, 'ca', 'california'); insert values (group, value, label) values (1, 'fl', 'florida'); 

so, group 1 contains 3 possible values drop-down selector. then, form table can reference group particular column uses.

note should add fields form via rows, not columns. i.e., app shouldn't adjusting schema when add new forms, should create new rows. so, make each field 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 form (name) values ('new form'); $id = last_insert_id() insert form_fields (form_id, field_label, field_type) values ($id, 'age', 'text'); insert form_fields (form_id, field_label, field_type) values ($id, 'profession', 'text'); insert form_fields (form_id, field_label, field_type) values ($id, 'salary', 'text'); insert form_fields (form_id, field_label, field_type, field_select) values ($id, 'state', 'select', 1);