Re: design question - lots of columns....

unc27932_at_yahoo.com wrote:

> Wanted to get this groups opinion on my issue. I started a thread _at_
> microsoft.public.sqlserver.server ......... Can you all take a look &
> offer any advice?
>
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/46126ff8c5af502c/f3cdb66023ee17ac

Again, I don't know your requirements. I am just tossing this out to stimulate thought.

In the relational model, the existence of a row is the equivalent of a boolean true, and the absense of a row is the equivalent of a boolean false. Instead of having a single table with 200+ columns, you could have 200+ relations each with a single column where the column identifies the questionnaire.

Logically, the two designs are equally expressive. With adequate physical independence, each would have exactly the same options for physical storage. Of course, that level of physical independence does not yet exist in any product I know of--certainly not in the product you use.

Assuming a direct mapping of the logical model to the physical, whether having multiple tables is more or less efficient than having a single wide table would depend on the health of your patients.

A healthy patient who has no conditions at all would not require any space in the answer tables. An unhealthy patient who has all conditions would require space in all tables.

If you have the wide table, it would be very easy to write views for the 200+ single-column relations. If you have 200+ single-column tables, writing a view for the wide relation would be much more difficult in SQL.

If these questionnaires are feeding into a product like SAS for regression analyses etc, the single wide table will work better.

If you need to use this data to query patients with specific conditions, the 200+ tables will generally work better for indexing etc. Received on Fri Jun 15 2007 - 16:29:36 CEST