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

After brainstorming with some colleagues and seeing your & others feedback, I'm relatively convinced that the wide table will do no harm. This database is going to be a holding area and will feed into ETL later which will go into a data warehouse. So design & mapping of incoming & outgoing data will be easier with a wide table I think.

On Jun 15, 10:29 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> unc27..._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/brow...
>
> 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 - 18:27:43 CEST