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

I would suggest you make your database center more on the data than the forms. Make each table focus on a particular set of data like customers, orders, products, or reviews. This way your forms can be flexible without repetition such reviews that carry customer data and reports that show products ordered by a particular customer.

reli4nt,
What you're saying makes sense- since I first posted I have already separated some data such as contact info into separate tables. However, I still need flexibility-

This form aspect of this project is so that students can apply to a program I work for- the application process is fairly long and a subset of the data required could change on a yearly basis. I also want it to be portable enough that another organization could use it without major database changes.

Perhaps I figure out which parts of the app will be static, create those tables and then create two tables to hold any custom fields?

custom_fields
id | field-name | application-year

custom_data
id | custom_fields_id | data

Or would it be better to include a custom_fields column in each table, and store any extra data as a string that can be parsed out by php?

thanks,
Sam

I see. You are trying to create a generic content management system.

Id keep custom fields to a separate indexed table.

You need a way, in the logic or the db, to tie custom fields to something meaning full. This may mean a separate table for forms and a form-id field in the custom_fields table.

You also need a way to tie your custom fields data to something relevant, be it an applicant or a user etc.

Originally Posted by reli4nt

You need a way, in the logic or the db, to tie custom fields to something meaning full. This may mean a separate table for forms and a form-id field in the custom_fields table.

I think I'll have a separate table for each form, and a table referencing those table names. Then I can include that reference in the custom_fields table. That way I can extend the functionality by creating additional tables as needed without having to change my application logic.

Originally Posted by reli4nt

You also need a way to tie your custom fields data to something relevant, be it an applicant or a user etc.

Yeah, that was an oversight on my part- it will be tied to the user_id.

Thanks so much for your help- it has really helped me think through this process.