SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 How to best store different data types		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jordy
Starting Member

New Zealand
5 Posts
Posted - 08/13/2012 :  20:06:42  Show Profile  Reply with Quote
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 etc

b) 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.
Cheers
Brendan

LoztInSpace
Aged Yak Warrior

878 Posts
Posted - 08/14/2012 :  04:41:30  Show Profile  Reply with Quote
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 :)
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3448 Posts
Posted - 08/14/2012 :  05:43:46  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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 Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3448 Posts
Posted - 08/14/2012 :  05:44:57  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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 Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000