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.


8 Years
Discussion Span
Last Post by dickersonka
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.