January 15, 2015 at 12:10 pm
Hi,
I have decided to use Wufoo online forms so collect evaluation data from my clients.
Each form will have different type of data.
For example
Form 1 might have:
Client id, name, user id, comments
Form 2 might have:
Client id, name, user id, address, comments, telephone number
Form 3 might have:
Client id, name, user id, comments, date of birth, email address
Each form can have over 20+ data types which can be all different to other forms.
My question is: What is the best way to store all the data into sql server without creating new columns of new data types everytime a new form is created in wufoo. Any help designing the tables will be appreciated.
Thank you
January 16, 2015 at 8:27 am
maxlezious (1/15/2015)
Hi,I have decided to use Wufoo online forms so collect evaluation data from my clients.
Each form will have different type of data.
For example
Form 1 might have:
Client id, name, user id, comments
Form 2 might have:
Client id, name, user id, address, comments, telephone number
Form 3 might have:
Client id, name, user id, comments, date of birth, email address
Each form can have over 20+ data types which can be all different to other forms.
My question is: What is the best way to store all the data into sql server without creating new columns of new data types everytime a new form is created in wufoo. Any help designing the tables will be appreciated.
Thank you
This is a tough question. In a situation like this, it can be hard to design something that would be effective.
Just a couple of thoughts to get the discussion going.
1. It seems like you have a couple of common fields that would be a good for identification.
Client ID, User ID, and Name seem to show up in all your fields.
2. In a situation like this, you may not need to be as strict about getting the "correct" datatype for all your various fields you're trying to store. Unless you're trying to do calculations with a certain field, you can make it a varchar field. For example, I've had co-workers in the past look at something like a zip code and put it into an int field. It messes things up for people in the far northeast who have a zip code starting with 0, and are you ever going to add a zip code to something else, find the average zip code, or do anything else that would require an int?
So, I'm going to make a suggestion that a lot of people are going to hate because it can make for some awful performance and make it difficult to report on your data in meaningful ways, but... an awful EAV is the only thing I can come up with at the moment.
I'd set up 2 tables.
First table has your client identifier information
Client ID, User ID, Name
Second table would be your evaluation data.
Client ID, UserID to link back to your client identifier table
Field Name - so you know what the value that you're storing means
Field Value - Varchar (based on point 2 above)
So when you have a form with birth date, you'd store "Birth Date" in the Field Name and the actual date in the Field Value. You're going to want to be consistent within your forms so that you're not storing birthday and birth date and brith date and then have an awful looking report.
Then when you run a report, you're going to end up with a list of all your data running in a column. If you want to have all the data in a single row per client ID/User ID, you're going to have to write a dynamic pivot to do it.
Take this as a starting point for thinking about how to solve your problem.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply