Dynamic forms in a single table

[nloding]

I'm working on a ticketing (support/request) system for my company.  We have a standard set of forms that may be used by clients, and I've deemed these "Requests," which are displayed differently as they are populated with form data, not just info from a text area.  We want to make these forms as dynamic as we can, so we're looking at dynamically changing the fields depending on what the user chooses (Ajax).  Thus, each form posted could contain different fields.

 

Validating this isn't a problem.  It's saving the data.  What type of design would you suggest?  I was going to do a single table for each form type until we decided on making the forms more dynamic and "alive" (as my boss said).  So I was then thinking something like this:

 

-------------------------------------------------------------
| id | num_fields | separator | fields         | values     |
-------------------------------------------------------------
| 1  | 2          | <.>       | name<.>address | me<.>street

 

Then you can validate the amount of info pulled.  I just don't know if the separator idea would work ... what if the request, for some odd reason, contained "<.>" ... I'd need to have multiple potential separators, hence the field.  "<.>", "~~!", "&*&" ... whatever.

 

Any suggestions?  I want to, theoretically, store any form in the same table, and using (roughly) the same script, pull that data, parse it, and display it in a table, with the fields on the left, the values on the right.

[nloding]

That idea detailed above is obviously not suited to this.  My next thought was to store XML data in the table as a string, and parse it when it comes out, but there isn't a great way to parse XML as a string instead of a document.  And writing that data to a file is just a waste of resources.

 

I've instead decided on having the request table, with a unique request id, and then a request_data table, which has a relationship ID, the request id, the field, and the value and the order to be presented in.  Each ticket/field/value combo is a unique relationship, with an infinite number of combo's allowed per request.  This also becomes changeable later, if the user wants to modify the request after the fact.

 

Any thoughts on this?  This sounds like most reasonable option for what I need to accomplish.

[keeB]

I have thoughts. Your idea is not good

 

Form

| form_id | field_id (FK) | name | action |


Field
| field_id | type (TEXT, CHECK, SELECTOR) | value

 

Much more elegant

 

 

[nloding]

Ah, that's a great way to store the form, but I was looking to store the data that is entered into the form.  The data will then be displayed in just a simple table format when viewing the ticket/request.  So I was just looking to store the information posted to the site from the form itself.

[keeB]

Well...............

 

You would then go 1 step further and implement a Field_Values table, which has a FK of FieldId.

 

For a Selector, you'd have multiple values for a single field.

 

Make sense?

[nloding]

But again, that's assuming that the form itself, or the actual form fields, are stored in the database for retrieval.

 

Say I have a form with three textbox inputs ('First_Name', 'Last_Name', and 'Email') and a radio ('Am_I_Dead').  When they fill it out and post it, it would go into my proposed database as follows:

 

| id | reqid (FK) | field_name | value | position |
| 0 | 1 | first_name | nathan | 1 |
| 1 | 1 | last_name | loding | 2 |
| 2 | 1 | email | loding@xxx.com | 3 |
| 3 | 1 | am_i_dead | no | 4 |

 

Then: SELECT * FROM request_data WHERE reqid='$req' ORDER BY position ASC

 

And then I can output with simple PHP an HTML table with two columns: Field and Value.

 

PS: I don't care about storing the form itself in the database.  I don't care to build my sample "Am I Dead" form by pulling data form the db and building the form.  When I said 'dynamic' forms, I more meant that, using Ajax, if a user clicks a certain radio button, various form fields disappear/appear, but it's all posted to one form processing file, and I would like it all saved in the same table.

[keeB]

Good luck

[nloding]

I'm confused about why it's bad to just store the data that way.  It'd be a long table, but it's not going to be used each visit to the page.  And I don't need the form itself in the database -- it's just hard-coded HTML.

 

I'm also considering just generating HTML code and then storing that as TEXT in a database field for the request.  My bosses keep adding requirements to this, so my design keeps changing slightly.

[keeB]

My bosses keep adding requirements to this, so my design keeps changing slightly.

 

That's why.

Less friction when requirements change.