What is the best database design to keep track of fields with multiple values?

Question

I am trying to dynamically build a form with a label (field) and a user entered value (fieldValue) but the form can have several different control, like a combobox, textbox, listbox, checkbox, etc....

Here is my schema right now

Page
* PageId

Field
* FieldId
* FiledTypeId
* FieldName

FieldType
* FieldTypeId
* FieldTypeName (Combobox, Textbox, etc...)

FieldValue
* FieldValueId
* FieldId
* PageId
* FieldValueValue
* CurrentValue (This is a bit because a combobox could have multiple values but I wanna show which one is selected. Similarly, a listbox could have multiple values selected, a textbox would not have any values selected.)

The problem with my design is that every time I have a form using the same fields, I have to add all of the items in the combobox again as it is a new field, specific to a new page AND when I create a new page how will it know that "these" values belong to a combobox, for example. I've thought of having a 0 to many table to keep track of comboboxes and things with multiple values and then my field value table will only hold one value per field and the CurrentValue column will be gone but I'm not 100% sure if this is correct.

Any suggestions?

Answer 1

Your new design constraint is such that there's a many-to-many relationship between pages and fields, and a many-to-many relationship between page-fields and field values. That means you'd have to do this:

Page
* Page_Id

Field
* Field_Id
* Field_Type_Id
* Field_Name

Page_Field
* Page_Field_ID
* Page_Id
* Field_Id

Page_Field_FieldValue
* Page_Field_FieldValue_ID
* Page_Field_ID
* FieldValue_Id

FieldValue
* FieldValue_Id
* FieldValue_Value
* Current_Value

"That's really gross" you're thinking. Yes, it is. I'd strongly suggest creating views (indexed or materialized if possible) to make retrieval easier. It doesn't really solve your problem of having to define everything for each new page, but at least you won't be storing the same data repeatedly.

The Field_Type table doesn't change at all. It's relation to Field is the same. I did not list it in order to reduce confusion.

I'd also caution about using the FieldValue.Current_Value field as you seem to be suggesting, as that will not really work if your application allows multiple users. Application state needs to be stored separately from application data, typically.

Answer 2

i think you have the pageid in the wrong spot for one.. it should go with the field. if you want to reuse the same field on many pages, then there should be an entirely new table.

first option:

Field
* FieldId
* PageId
* FiledTypeId
* FieldName

FieldValue
* FieldValueId
* FieldId
* FieldValueValue
* CurrentValue 

this allows the field to belong to one page - then all the other stuff belongs to that field

alternately:

Page
* PageId

Field
* FieldId
* FiledTypeId
* FieldName

page_field
* PageId
* FieldId

this allows the same field to repeat on different pages, with all its other behaviors intact.