I go about this slightly differently and in doing so only need to worry
about the max length of one field, "the answer to a question" outside of
this, the form table only holds distinct information about the form. In this
case it's a dynamic survey system I've cut out lots of fields here because
the Isactive columns last modified etc is just wasted space here.
I wouldn't know if it's more or less work to do this type of setup but I've
re-used the code a few times and it works well with minor tweaks. I might
jump up to radio buttons next ;-)
The Main Table that controls different surveys available.
`SurveyID`
`Title`
`Description`
In your case groupID and the userID would go here as well.
Now you have another table for survey Fields.... Linked back by survey ID in
this case, text, textarea, select
`FieldID`
`SurveyID`
`Question`
`Type`
To make matters even worse you need to take into account option select
fields
`OptionID`
`FieldID`
`Title`
Once a form is submitted, you need a way to organize the data hence the
answers table. Main goal for this table is to list when forms were submitted
and provide the link to the details page for answers
`AnswerID`
`SurveyID`
`AnswerStatus`
Now the Answers themselves
`AnswerDetailID`
`AnswerID`
`FieldID`
`Answer` --- This is the only field I don't know the size of...
everything else can be set to a particular datatype size. so I set this to
TEXT
`DateAdded`
On 7/12/07, Mik Muller <[EMAIL PROTECTED]> wrote:
>
> Hey all,
>
> I'm adding a dynamic-forms section to my sites and am wondering about how
> to best create db table columns intended for dynamic data.
>
> Admins will be able to specify the form fields in their custom forms to
> their heart's content, all manner of form field types, and when the form is
> posted it will go into a custom table named FORM_### based on an ID in the
> FORMS table which holds meta data.
>
> Now, the FORM_### table will have some mandatory columns, like userID,
> groupID, postDateTime, etc., but the rest of the columns will be determined
> by the dynamic form: FIRSTNAME, COUNTRY, SHOESIZE, FLAVOR, etc.
>
> I don't want to even try to type these columns. Once the form data is
> submitted, it's all text, pretty much, right? None of it will be relational
> except the groupID and the userID, etc., which will be automatically added
> when the table is initially created by my code.
>
> My thinking is I make *all* the dynamic table columns from the form fields
> VARCHAR and give the admins a drop-down for maximum data width when creating
> the tables, ie; 5, 10, 20, 30, 40, 50, 75, 100, 150, 200, 300, 400, 500,
> 750, 1000, 1500, 2000, 2500, 3000, 4000, with the stressed caveat that
> shorter fields will retrieve and sort quicker in their reports.
>
> What do you think. Does this sound ... er, sound? These are going to be
> simple forms, nothing high-powered. Something so they can create their own
> conference registration forms or whatever without having to bug me.
>
> Thanks,
>
> Mik
>
>
> --------
> Michael Muller
> Admin, MontagueMA.net Website
> work (413) 863-0030
> cell (413) 320-5336
> skype: michaelBmuller
> http://www.MontagueMA.net
>
> Eschew Obfuscation
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 and Flex 2
Build sales & marketing dashboard RIAâs for your business. Upgrade now
http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283606
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
