|
|
Posted by Erland Sommarskog on 08/27/05 01:23
JG (jayrom@gmail.com) writes:
> I am a developer and I have a problem trying to design a system to
> manage data coming from web surveys. Each section can potentially have
> dozens of questions, i.e., fields.
> I am focusing here only on the table(s) that will hold the survey data.
> I do not have any DDL as I am still trying to understand this!
> All the examples I have found so far in books and on the web seem to
> deal with fairly limited data, that is easily, or so it looks, broken
> down in multiple tables.
> It seems that, from my research, having a wide table per survey section
> with each field as a column, which has been suggested to me, is not
> proper design for many reasons - missing values for non-required
> questions, table with 100s of possible columns, etc... - so I played
> with the idea of a single table where one of the columns would be the
> foreign key pointing to a questions table and another column would hold
> the data (this is a simplified explanation.)
Yes, I would very much advocate this design.
> The problem with this is that now this column will have to accomodate
> all types of data, from bits to large varchars, and that field
> validation seems now impossible jeopardizing data integrity.
You could make that column sql_variant. This data type can fit any
other SQL Server data type except text, ntext and image.
In order to prevserve integrity, you can for each question define which
datatype that is correct for that question. In the trigger of the answers
table, you can check with sql_variant_property() that the data type is
correct.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|