1

I'm sorry if this a stupid question, but I am new to this. I want to store all the information from all my customers' different contact forms in a database so they can retrieve the information later. Each contact form has a different number of fields. Ideally I'd store all of this info in one MYSQL table, but as I said each customer's contact form has a different number of fields.

I was thinking of creating a database with the following fields ID, customerID, dateTime, data The data would be the html of the contact form. However I am sure that's not best practice. Also it wouldn't allow me to offer a download option to the customer of all their contact forms as a csv or similar. I don't want to have a different table for each customer either, and I am sure having a table with 'field1', 'field2', 'field3' etc isn't that good either. Any ideas?

iagdotme
  • 1,033
  • 2
  • 21
  • 38
  • Using a *data* column in this way defeats the purpose of using a RDBMS. There are plenty of use cases, hence the adoption of NoSQL solutions. However, you be aware of the trade-offs. – Jason McCreary Jun 10 '11 at 16:35
  • with regards to a table with `field1`, `field2` as columns, ExpressionEngine uses this schema for it's weblog custom fields storage, and, well, it's a highly effective implementation. – Ross Jun 10 '11 at 16:45
  • I agree, but I don't quite see how I could implement a relational database for this case. If I have 100s of customers with different contact forms I don't want to have 100s of tables in the database. That would be difficult to manage. And, as I said in a comment further down, if some of these customers change the number of fields in their databases it would mean even more tables! – iagdotme Jun 10 '11 at 16:50
  • If you intend to query against the data in question frequently, you should really put it into a proper schema. If not, storing an xml blob may be an acceptable compromise. You can even query it (although inevitably not efficiently): http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html#xml-5.1-extractvalue – Frank Farmer Jun 10 '11 at 16:52
  • @ross - perhaps that is the way forwards. However I would need quite a few fields just to make sure! If a customer wants a very long contact form with 80 fields I might have to go back to the drawing board! Where would I store the titles of these fields for the customers' contact form, and what would happen if these fields changed later on? – iagdotme Jun 10 '11 at 16:52
  • Your only real limitation is SQL and the number of columns; if I recall it's around 1000 columns or so. If you opt for this approach, you would need an interface to easily create and edit the column names, and if you had to rename the field, you could do it through this interface. 80 fields for a contact form also sounds like you might need to rethink your app! – Ross Jun 10 '11 at 17:02
  • I gave the ideal solution below - why are we still talking about other off-the-wall options? – Dave Jun 10 '11 at 17:32

3 Answers3

1

If you don't need it to be relational, that would be fine. You wouldn't be able to query it very easily.

You could store the answers in another table using keys to link back to the form and field.

Or use a KeyValue store type DB like MongDB, and you could store all your data without worrying about the schema.

datasage
  • 19,153
  • 2
  • 48
  • 54
  • I agree storing the answers in one table would make quering the contact data difficult. I'm not sure how storing this information in another table would be much more useful as I'd still be storing it in one field. Unless I am wrong the choices are to store all info in one table or 1 table per contact form. The other issue would be if the customer wants different fields in his contact form. That would muck the whole thing up would it not?! – iagdotme Jun 10 '11 at 16:49
  • You have a key for each field. Usually what you have stored in the name attribute. So you could store each field result in its own row with a key back to the form table. The table would be something like form_key, field_key, field_value – datasage Jun 10 '11 at 17:13
  • Oh ok, that makes sense. That might mean a lot of fields. Is there anything wrong with having 70+ fields, just in case I get customers who want that many?! – iagdotme Jun 10 '11 at 17:19
  • You will have 70 rows of values, but it should work just fine. – datasage Jun 10 '11 at 17:20
  • OK, fair enough! btw what do you mean by "KeyValue store type DB like MongDB"? – iagdotme Jun 10 '11 at 17:39
  • MongoDB is not a relational database like MySQL. You could store the entire form information as one single object. Regardless of how many fields you have. – datasage Jun 10 '11 at 17:41
1

Make another table called user_data. In it, have:

id, user_id, data

Then, if they fill out a form with 10 fields, you can enter 10 entries into this table. When you pull data for the user, just also pull any data from the user_data table that has user_id = their id.

If you need more than that, you could also add a "field_name" field or something:

id, user_data, field_name, data
Dave
  • 28,833
  • 23
  • 113
  • 183
1

You can use the MySQL database to have a schema for your desired information that relates many tables to one another.

Here is another discussion about database design: Database Design Best Practices

If you must have only one table, the other choice I would mention is to have the ID created automatically, and then make the fields be not required. This way when a customer fills out one form before the other it won't mess you up. You can then store the ID as a Session Variable and reuse it for different INSERT statements to your database.

You can make a download function for the customer that will output in the desired format that queries your table and then generates the file.

Community
  • 1
  • 1
James
  • 539
  • 2
  • 6
  • Thanks for your quick response, it looks I need to learn a lot more about MYSQL. I'll look through the resources given in the link you provided. I don't quite understand what you mean by the database having a schema. The reason I wanted 1 table was that it would be easier to manage and to compose simple MYSQL queries. – iagdotme Jun 10 '11 at 16:46