so lost in designing those tables !
I posted this question earlier but i didn't get any answer ! i will try to rephrase it and make it more clear hoping to get helpful advice.
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 !!
This one is a bit hard to answer because the requirements:
text boxes will be grouped together in one table, drop down lists in another table ...
and
... if i decide to upgrade my application later on and add a new type ,i wont have to ALTER my schema ..
are conflicting.
So, if you do not want to alter the schema, take a look at this question/answer.
If you would accept to modify the schema a bit, take a look the the model. In this model you would have to add a new table for each new element-type, but would not have to modify any existing tables.
Place all common fields into the Element
table, and only specific ones into TextBox, List, CheckBox, ...
Furthermore, form elements are mostly standard -- you could list them all right now and simply create all tables even if the application currently does not support them. This way you would rarely need to modify the schema.
That how I would desgin it to avoid data redundancy :
formTable
---------
id varchar(36)
name varchar(36)
creationDate date
lastUpdate date
isActivated boolean
...
...
componentTable
--------------
id varchar(36)
name varchar(36)
...
...
...
formComponentTable
------------------
form_id varchar(36)
component_id varchar(36)
metadataTitleTable
-----------------
id varchar(36)
name varchar(36)
metadataValueTable
------------------
id varchar(36)
name varchar(36)
metadataTable
-------------
metadata_id varchar(36)
title_id varchar(36)
value_id varchar(36)
metadataComponentTable
----------------------
component_id varchar(36)
metadata_id varchar(36)
Now let me explain you the way I see it.
You have a form that you insert in the formTable
, with all the datas you need. Dates, owner, etc...
A form is composed of a multiples components. The formComponentTable
will bind the form with the various components that composes it. Then the metedata*Table
will let you describe all of your components.
I did not understand why did you want to create a table for every kind of components. Why don't you just add a column that would define which type is it. Do you get me ?
Well I think that should be a good start for you to start thinking your design.
If you need more help don't hesitate to add comments,
Edit : content can -in this case- be considerated as a metadata. So you would have a set in the metadataTitleTable
that would be content
and then a set in the metadataValueTable
the actual content
then you just have to bind everything together.
精彩评论