i want to build an application that allows a user to build its own form(s).
A user can input data into its form and query the data too.
the form can be edited after being created and used ( add/remove fields from it ).
the form can have fields like: small text box, big text box , single list, check box, drop down menu ....
i want to group the fields into tables according to their types... (text boxes will be grouped together in one table, drop down lists in another table and so one ) .
I want to design my tables so that if i decide to upgrade my application later on and add a new type ,i wont have to ALTER my schema ! for example, if at first i only allowed the user to use text boxes and dropdown menus, then later on, i upgrade my application and add a new type : the "check boxes" ! i want the user to be able to edit its form and add checkboxes to it without having to change the schema .
so i was thinking ( and please help me out here ) to have:
1- each FIELD has ( ID-Name-Position- value) 2- each FIELD has exactly one TYPE 3- each TYPE can be one of these ( string - numeric - date - binary - short ) 4- each FIELD can have one or more multiple items 5- if a FIELD has multiple items, then for sure it has "one selected items"
(4 and 5 are to cover the dropdown menu fields).
so i end up with 3 tables :
FIELD TABLE and the TYPE table and the MUTLIPLE ITEMS tables.
is this correct ? what if i want to add a new "field"..would this do it ? please help, im really new at this!! ive been tugging at my hair since forever and i cant get to fix this !!
Before we plunge into this EAV (Entity/Attribute/Value) model mess with you, please let us know the purpose of this database application.
Who are the consumers? What kind of lists will it store? Web-based, intranet, or client-side interface?
If it's not practically useful, then it's practically useless.