Tables with "dynamic structure"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
schgrypt
Forum Newbie
Posts: 17
Joined: Thu Jan 29, 2009 6:34 am

Tables with "dynamic structure"

Post by schgrypt »

Hello everyone,

I found on this forum similar issues but unfortunately no solution to my specific problem.

I am working on a Form-Generator which allows to create html Forms by simply "clicking them together" (I need the fields "Name", "Email", "Language" and "Smoker/Non-smoker" => *click* and voilà). This works quite well, the forms structure and some additional information (Validation) is stored in a xml file.

Now my problem: How and where should I store the data submitted with these forms, means in my example f.e. "Bob", "bob@example.org", "English" and "0".

The data should be sortable and directly accessible, so storing it in the same xml like the forms structure seems to be a bad choice.

The easiest and most convenient solution would be to create a new mysql table for each new form, but: first of all there would be in short time quite a lot of tables (without too much content) and, more important for me, a friend once told me that whenever I am planning to create tables "on the fly" it is for sure due to a poor design and I should find a better solution.

Well, I've really been thinking about it a lot but still don't see a better way to solve the problem (than the to create for every form a new table), as I don't know the structure of the table because itself is generated dynamically.

I hope you could follow and understand my poor english, sorry for it :?

Thank you for your time and I'm eager to hear your thoughts about the subject

greetings from switzerland
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Re: Tables with "dynamic structure"

Post by andym01480 »

How about one table with 4 fields - form_field_name, form_field_value,form_id,form_data_id
form_field_name contains name of a form field
form_field_value contains the data for the form field
form_id is a unique id for each form
form_data_id is the primary key for each row.

You get the last form_id by SELECT max(form_id) FROM form_data and then add 1 to it for the next form!

You can then get all data for each form by SELECT * FROM form_data WHERE form_id=$x...

Creating form tables on the fly is not good because the database user would need very high privileges, which is bad for security!
schgrypt
Forum Newbie
Posts: 17
Joined: Thu Jan 29, 2009 6:34 am

Re: Tables with "dynamic structure"

Post by schgrypt »

Hi Andy,

thank you for your reply.

I've been thinking about the same structure, the biggest disadvantage in my eyes would be that you have to store all kind of data in the same form_field_value, instead of saving numbers to int, strings to varchar, big text to blob etc. The obvious solution for this would be to create tables for the different types, means one int table, one varchar, ...

Far away from a real 'good' solution, but probably really better than creating tables.

Does anyone have experience with many many tables, is it a problem for mysql (speed?) or should it work? Just in case :)
Post Reply