0

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

PeacefulSoul
  • 47
  • 1
  • 3
  • It is better to _edit_ your previous question and address the points in the comments than posting a copy as new one (even if somewhat edited already). Please do that and delete this one. – Oded Dec 12 '10 at 16:48
  • possible duplicate of [How to design these two tables?](http://stackoverflow.com/questions/4419600/how-to-design-these-two-tables) – Oded Dec 12 '10 at 16:51
  • thank u for ur reply, but im afraid if u do that no one would look at it cz it already has many views and no answer :( and i really really need help .. :( what do you suggest ? if you want me to delete it, i will – PeacefulSoul Dec 12 '10 at 16:54

2 Answers2

1

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, ...

alt text

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.

Community
  • 1
  • 1
Damir Sudarevic
  • 21,112
  • 2
  • 42
  • 67
0

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.

Spredzy
  • 4,702
  • 13
  • 47
  • 67
  • thank you for your reply, i am not sure i understood your design...can you give me an example of how i would store the contents of a text box, a drop down menu and a single list if i user you design ? – PeacefulSoul Dec 12 '10 at 21:02