Best practices for storing data from hundreds of fields

I have a form with about 500+ fields (it's a 10 page form, different data types). Can you guys please advise me on the best way to store the data from the form? I can create 500 fields in multiple, logically divided tables but that seems a lot (or maybe that's the best way?!) since I have a few of these forms. I am looking into serializing 开发者_如何学Gothe data and storing in longtext mysql field. That will have its drawbacks (the one I am thinking of is if the customer wants to search individual fields in the future) but it does seem like a pretty fast solution. I will appreciate if you would share you experience with a similar situation.

---

Presumibly you dont expect the user to fill the form in in a single sitting! So you will need some sort of work flow to store drafts and amend previous copies etc.

Also assuming some parts of the form are optional.

You could either define a set of database tables with a master table to track status, user name etc, and a child table for each optional part of the form.

Or you could define an XML schema which contains all the possible fields in the form etc plus some status information.

If you always process the entire form and dont want to search through your collection of forms then the XML soiution is slightly better as there are some nifty tricks for moving data from XML to HTML forms and back again. If you need to search based on values inside the form then the SQL based solution is preferable.

---

You may need 500 columns - unless they can be placed in other tables. It can be hard to tell without seeing your requirements.

Serialising it would make one of the advantages of using a database impossible - querying against certain column values.

---

create table profile_details (
   user_id number,
   field_name varchar,
   field_value varchar
);

Now you are not only not limited by number of fields, you also pretty free to add and remove them as you keep developing and maintaining your app.

select firstname, lastname, zipcode 
from profiles p 
join profile_details d1 on (p.user_id=d1.user_id) 
join profile_details d2 on (p.user_id=d2.user_id) 
where d1.field_name='hobby' and d1.field_value='fishing'
and   d2.field_name='income' and d2.field_value>cast(250000 as number);