Storing variable data

问题:

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.

回答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)

回答2:

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).