How to create a database with differing form responses
Hi....need some help if possible.... I have a multipage form that will have very basic data requested, however part of the form will be dynamic as in each person that completes the form will have differing criteria and answers, sections of the form can be added or not as the case may be. My query is (and I'm fairly new to DB so be kind)...I need to have the form data submitted to a MySql database, but am unsure of how to do it....do I need to build a database with every a table and column for every single type of response that comes in....as each will submitted will be completely different to any other.... really not sure of my options on this.... I hope I have explained well enough for someone to help out... Thanks A
I'll caveat that my experience with mysql is limited, but this is more of a standard RDBMS design question so I will be happy to try to help. Now, depending on how you intend to use the database you have a number of options. **One Table** One option is to to include columns for every single type of response you may need in your main form data collection table(s). This is not overly elegant and can result in a table with a large number of columns, but it does not necessarily violate 3NF (Third Normal Form) since each column could still be related to the key, and only the key, and would not necessarily be repetitive. This would violate 5NF since it would permit nulls, but most designers find that acceptable. This could be the best design if you want to do lots of correlation between the columns and there is nothing else in your design that would compel you to avoid nulls or make you want to reduce the number of columns in the table. There are some complication in SQL Server (especially older versions) if any table has more than 1,024 columns or if any row is larger than 8060 bytes, but I don't know how that plays out with mysql. **Many Tables** Another option is to split it into tables based on the columns that go together and then using a PK-FK relationship to be able to rejoin them as needed. This can make for a more elegant design in some ways and you could achieve 5NF if you want to and could be faster for certain types of queries that focus on columns that are normally grouped together. However, it can lead to complicated joins and can be slower if you try to look at statistics or correlations for columns that are in different tables. **XML** Finally, you could use an XML column to store the data that is particular to that type of query. In some ways this is the simplest design from the SQL Server standpoint, but it requires more work for the other pieces of software to create and then parse out the XML. Of course, with LINQ and other XML parsers that may not be much more work. If you will generally want to review individual records, this may be the best overall answer. However, it will be slow and tedious if you want to go through and look at correlations between the different pieces of data that would be stored int he XML column. **Others** There are also other options you could consider, including using a shema-less non-relational database instead of an RDBMS. It all depends on what you intend to do with the data from these forms. When in doubt, I would favor the second option (split it up into tables which store groups of related columns). It is generally the more elegant and more flexible one, especially if you have lots of possible columns and face the need to add more later. But if you think about how you will use the data after it is collected, it will likely suggest one of the options as best for that need. I hope this helps, please feel free to ask more detailed follow ups.