4

I am building an application in ASP.NET, C#, MVC3 and SQL Server 2008.

A form is presented to a user to fill out (name, email, address, etc). I would like to allow the admin of the application to add extra, dynamic questions to this form.

The amount of extra questions and the type of data returned will vary. For instance, the admin could add 0, 1 or more of the following types of questions:

  1. Have you a full, clean driving liscence?
  2. Rate your drivings skills from 1 to 5.
  3. Describe the last time you went on a long journey?
  4. etc ...

Note, that the answers provided could be binary (Q.1), integer (Q.2) or free text (Q.3).

What is the best way of storing random data like this in MS SQL?

Any help would be greatly appriecated.

Thanks in advance.

Improve this question
3
  • 4
    Consider using EAV - while it has its issues, it is much more maintainable (IMHO) than adding a new column for every new question. I blogged about this some time ago: sqlblog.org/blogs/aaron_bertrand/archive/2009/11/19/… I think you should separate out your question into two, in order to cover both the database design and the application design to support it. Worry about the database design first, then attack the application solution. Commented Aug 16, 2011 at 16:11
  • Cheers Aaron, I will check out your blog post. I also broke out the question into two separate parts (I need to wait 20 minutes before I ask it). Thanks again! Commented Aug 16, 2011 at 16:17
  • Nice EAV article Aaron. As per some of the comments I've previously used XML columns for udfs successfully. Commented Aug 16, 2011 at 16:25

2 Answers 2

Reset to default
1

I would create a table with the following columns and store the name of the variable along with value in the appropriate column with all other values null.

id:int (primary)
name:varchar(100)
value_bool:bit(nullable)
value_int:int (nullable)
value_text:varchar(100) (nullable)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This was precisely the solution I came up with, though I went to lunch before posting ^_^. I thought you could even store an additional int to represent the value_type, though, with coding logic checking for nulls, it wouldn't be necessary. Whichever works!
I have used this method to go about creating something similar to Google Forms where forms can be dynamically created.
The OP mentioned "binary" but they actually meant bit (e.g. yes/no). You could overload the int column for this (maybe with an additional column for checking type more explicitly) or keep the third column just changing binary to bit.
Woops thats what I meant to put in there. Changing it.
Thanks Caimen and Aaron- I have enough here to get me started! Performance wise, would there be much of a difference between checking for nulls or checking the type more explicitly?
1

Unless space is an issue, I would use VARCHAR(MAX). It gives you up to 8,000 characters and stores numbers and text.

edit: Actually as Aaron points out below, that will give you 2 billion characters (enough for a book). You might go with VARCHAR(8000) or the like then, wich does give you up to 8,000 characters. Since it is VARCHAR, it will not take empty space (so a 0 or 1 will not take up 8,000 characters worth of space, only 1).

Improve this answer

2 Comments

The problem with this method is you won't be able to use SQL to pull out certain values later on. You'll have to pull out the whole value and parse it.
VARCHAR(MAX) gives you 2 billion characters, not 8,000 characters. However this seems overkill for some of the questions whose answers can only ever by '1' or '0'...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.