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 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?

share|improve this question

migrated from stackoverflow.com Dec 4 '12 at 6:47

This question came from our site for professional and enthusiast programmers.

    
It is off topic. –  Lion Dec 3 '12 at 21:30
    
there are many other schema design questions here.. so maybe not that far off... –  Randy Dec 3 '12 at 21:31
    
I believe you're looking for an EAV-type model. –  Kermit Dec 3 '12 at 21:38
    
EAV is what I mentioned in my question but for that I have to look at the field type and based on that, grab the attributes (default values) for the fields. Not sure if that's a little complicated –  Pittfall Dec 3 '12 at 22:04
    
A different design can include a table for each fieldtype (generalization/specialization). I think what you need in both cases is a many-to-many relationship (Page - Field). In this table you can include the position of the field, on the page. –  Ruud van de Beeten Dec 6 '12 at 14:32

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer
    
p.s. i would at least go with the second choice. –  Randy Dec 3 '12 at 21:36
    
the only problem with that is that a page is not unique to its fields, it's unique to the values of the fields –  Pittfall Dec 3 '12 at 21:38
1  
well.. maybe make a new thing for that - say called: submission –  Randy Dec 3 '12 at 21:40

Your Answer

 
discard

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