Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jordy
Starting Member
5 Posts |
 Posted - 2012-08-13 : 20:06:42
|
| Hi,I am using SQL Server 2008 R2 with a possible upgrade to SQL 2012.I have a project kicking off that will allow a web administrator to create questions for end users. My part in this project is to store just the answers that the end user(s) give. Thus where my issue arises.My first thoughts around this is that:- I will need to store any data type that the user(s) enter in as their answer. Which will include data such as long essay answers, dates, numeric values etc etc. - I would like to be able to run calculations against the numeric type data and comparisons against the date type data for example.- Have the db table structured so we can run reports against particular answers. For example if a question was 'what is your date of birth' I would want to be able to report on people who were born Jan-1983.I see my options as being:a) Store all answers in a nvarchar(max) field. Not very easy to report from or compare answers such as dates etcb) Store answers in a nvarchar(max) field with a second 'datatype' field that tells us what data type the nvarchar(max) field will be. We would then know what function to run against the data to be able to preform for example calculations and comparison type queries.c) Create 3 separate tables, one for text answers, date answers and numeric answers so that some form of reporting and calculating can be done against the date and numeric tables.I guess my questions is, what is the best way to store different data types passed through in answers, keeping in mind that I would want to make it as easy as possible to report from this data.Im not to sure if this is the clearest question in the world but its a start.Any suggestions would be very much appreciated.CheersBrendan |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-08-14 : 04:41:30
|
| c)You can always union & convert them to varchar if you need to. Going the other way is the hard part. Well done for even asking the question rather than coming here with a broken database that you can't query and looking for help :) |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-08-14 : 05:43:46
|
How about a controversial suggestion for your user case?Consider using a schema-less store like mongodb instead? Your problem is key / value on a key of QuestionID+UserID (at least from the info given)You application could attempt to parse whatever the user gave you (probably using some sort of per question pattern matching -- or a more explicit user form per question approach).Then dump the parsed response into the datastore (because it's a document oriented schema-less design you can have different fields per document).Mongodb supports json documents (bson actually) that are query able and index-able per field.Whatever relational database you were to use you'd have a hard time modelling this requirement because there are so many ad hoc options you could put on each response.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-08-14 : 05:44:57
|
quote:
Originally posted by LoztInSpace
c)You can always union & convert them to varchar if you need to. Going the other way is the hard part. Well done for even asking the question rather than coming here with a broken database that you can't query and looking for help :)
+++Yes -- whatever you decide to do, well done for thinking first!!!!!Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
|
|
|
How to best store different data types