I have a project that has a form with 60 fields. I need to store all data from the form. I see two approaches: a record for each submission in a table with more than 60 columns or two tables with one containing a unique identifier for the form and the other storing data pairs The first approach is simple but very labor intensive to create the table and related sql insert code. The second approach allows me to create the form and loop over the posted fields inserting a record for each field with four columns (unique id, form id, form field name, and form field value). The second approach can be implemented much faster and would handle additional fields without extra sql coding but it may suffer performance issues but the form would be used only a few times per hour. Any thoughts?
I would just use one table with many records. Many fields works but not very well, applications that need to compatible across multiple database platforms will have issues with the number of columns being greater than 64 and it is usually for a few name/value pairs. If you have more info for each field (e.g. for form elements), you would want one or two tables. I'd just the one table with a record per form element with fields for ID, name, value and anything else, such as default value.
It depends on the data types of the columns and of the queries you will make to retrieve data. Personally I don't see a problem with many columns. I prefer each column to have the appropriate datatype. Now you will probably put everything in a varchar column. This means that if you will store numbers or dates, you'll use more space than necessary (and if you have millions of rows your database will occupy more space then was necessary). You also lose a validation, unless you check everything at the form. Your database won't stop someone from entering 'ABC' as their zipcode or as a date. Only if almost everything you want to store is text, you could do store it as multiple rows. Also think about how you will query this column and how indexes should be put. Will you use only selects on primary key ? Then no problem for multiple rows. If you need to query on a range of a datetime field, then don't store it as text but store it as a datetime column.
I will store each field and field value as varchar and do client side validation. There will be thousands of records not millions. Thanks.
1st approach has some drawbacks. for eg. when you do select * from db, you will get huge amount of data which is not always required. 2nd approach has drawbacks that it has to perform multiple sql queries to get the same data which could be fetched by 1 query in 1st approach. So unless you give more details, it is hard to suggest which way to go. It all depends on how often the data is fetched from database and how often it is updated..
set up some relation tables. ie Table person id, name, city, state, zip Table job id task, etc and for all the rest of the LOGICAL groupings. and then create a main table: Table MAIN personid, jobid, etcid you will need to join when querying, but if you keep your groupings tight and accurate per your needs, you will most likely only have to do 1 join to get that group of info.
The performance of the data base depends upon the design of the database, plus the way you are using to retrieve and insert the data in the database. You should normalize your database atleast upto Second normal Form, and you also need to apply the constrainst on the tables for better performance. If the database is properly normalized then the number of columns don't effect the performance. For fast retrieval of records from Database you need to apply the indexes. The other thing you need to look at is, the logic you are using to connect to the database and retrieving or storing the records, Check the code carefully and avoid the unnecessary Database connections. Always try to close the connections immediately after the database manipulation has done. Follow these things and your program will work fine then.
Performance would not be an issue. We would insert several record sets per hour and audit recordsets infrequently.
The choice depends mostly on HOW you will use data further and also on the estimated number of records you will store I generally use the many records approach as it is a lot more easier and use bulk inserts BUT in some cases I had to switch to the many columns approach when data beeing stored needs to be searched in a specific manner