dynamic tables in cakephp

Question

Can I create tables in the database dynamically with the columns created based on the values entered in a web page?

Actually I need to create an application that users can use for creating forms and templates,get data from others by mailing the link and store them in the database. Each user may create a different form with different fields and send it to a number of people who in return fill in the data and submit the forms. So how do I store the data,since each form has different fields?

Answer 1

Yes, you can do this. This is not specific to cakePHP: you just need to know the SQL code for creating columns and removing them, and put that SQL into your query() call.

This page is your friend.

Whether you should be doing it is another matter; generally you shouldn't be modifying database or table structure at run time - only during installations or upgrades. For example, if what you need to achieve is allowing custom fields to be created for user profile or something like that, then modifying the table structure at runtime is not the answer. What you would do is have a separate table containing all of the custom fields, and a table containing all of the values along with the field ID and the user ID. That would be the proper way to normalise this; yes it does involve three tables instead of one, but it is better than having columns user_customfield1, user_customfield2, user_customfield3 all in one table, for example, and modifying that structure at runtime.

Answer 2

Rather than creating tables on the fly, perhaps it'd be better to see how that data can be expressed in static tables.

How about:

• a table called 'forms' with 'id', 'user_id', 'created', 'modified' etc.
• a table called 'fields' with 'id', 'form_id', 'type', 'label', 'required', 'properties' etc.
• a table called 'answers' with 'id', 'form_id', 'field_id', 'answer' etc.

That way you rely on joins rather than executing tons of structural SQL. Your sanity will thank you.