I'm in the process of designing an app which basically functions as a multi-part application(multiple forms) incorporating a basic cms and payment tracking. I'm having trouble coming up with the db structure for the multi-part application.

The site needs to be flexible enough to:
-Have multiple forms
-Store/track form submissions indefinitely
-Change form fields without losing earlier submission data

My first though was a table for each form, plus a table for tracking users- but that wouldn't allow me to add/change/remove my forms very easily. Also, I don't see how I would be able to relate the form questions to the table column names. I don't want to update my db schema every time I add a field to a form.

So right now I'm looking at using three tables for the forms plus a users table:

id | name | password ...

id | name | description

id | form_id | field_name | is_active

id | user_id | form_field_id | field_data

This design seems pretty flexible- I could add or remove fields(by setting is_active as false) from my forms without compromising previously submitted data.

It does seem rather inelegant, though. I imagine that table "form_data" could soon have an unwieldy number of rows. On the other hand, I can't imagine having more than 100 rows per user(probably more like 50).

If I do go with this schema, is there any way to prevent duplicate entries in the form_data table? Like a key that is somehow based off of a combination of user_id and form_field_id?

Does anyone have any ideas? Suggestions. Your help would be much appreciated.


are you trying to create viewstate?

if so, what front end language are you using?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.