Hello,
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:
user
id | name | password ...
forms
id | name | description
form_fields
id | form_id | field_name | is_active
form_data
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.
thanks,
Sam