Dynamic forms table design

[biggieuk]

Hi all,

 

Having a little trouble designing my database for a project.

 

The web app consists of a number of 'pages', each page with a form and some fields on.  The fields on each form need to be editable and stored in a database. So basically it is a number of forms with dynamic fields.

 

My current table structure is as follows:

 

Questions Table

id

field_name

title

description

orderno

 

1

company_name

Company Name

What is your company name

1

 

2

telephone

Telephone Number

What is your telephone number

2

 

Then for each time a user starts a questionnaire a row is created/updated in the 'assessments' table:

 

Assessments Table

id

user_id

question_id

answer

 

1

32423

1

My Company

 

2

32423

2

01144556677

 

 

For a static form I usually would do an INSERT statement and insert the value corresponding to the column name, however using the approach above I would need to do an INSERT for each separate question on each form.

 

Does this seem like the best approach for storing data on a per user basis for a dynamic table?

 

thanks for your guidance on this.

 

 

 

 

[requinix]

Yeah: a table listing pages/forms, a table listing questions per form, and a table listing answers per user per question. You might want to add a "question type" for, at the very least, a difference between a single-line answer and a multi-line answer, but of course that's totally dependent on the app.

[biggieuk]

thanks for your reply.

 

Yeh a table type column would be needed too.

 

So a separate INSERT for each row is the best way to do this? Just seems a bit of an overhead but can't think of another way.

 

thanks

[requinix]

Separate INSERTs for what? Answers? If you don't have too many at once you can do a batch INSERT, but yeah that's how you'd do it.