SQL rows in place of columns ?

Question

I have a form with many project questions and in some of those questions are about 20 CheckBoxes to be checked. I want to create a SQL structure for these projects.

The majority of data is located in a Project table. But I don't want to have all these CheckBoxes as columns of the project table, instead I want to make a table where all these CheckBoxes would be represented as Rows. This table would have a group designating the question (parent of all those checkboxes) and I'd add 1 more table, between the project and the table with checkboxes to create a [1]-[N N]-[1] structure.

But I'm asking, if this is acceptable, because when the query comes, I won't have the columns to set the condition for, but a table with rows...

Any better idea? Thank you

Answer 1

This is absolutely acceptable. We sell a couple products that deal with dynamic data in just that way. Also it's very fast even with large data sets (1M+ records)

To be sure, you will want to be on SQL 2008. Things like Table Value Parameters, MERGE, and CTE's make dealing with this type of data so much easier than older versions of sql server.

HINT: you might be tempted to create views of that data in order to select out of it..If the number of columns is less than 10, then you'll be fine. Anything more than that and you are much better off doing the rows to columns transform (I'm thinking reports here) in code.