[dba-SQLServer] John Colby's problem

That's why they pay me the big bucks LOL. 

Somewhat more seriously, it's not that difficult to write the code to normalize your giant table, but as I recall quite a few columns were ambiguous in their contents. That part of the problem remains troublesome. And I also detect the need for a bridge table between Surveys and Question/Answers, such that Surveys One and Two might both contain the list of Questions applying to Smokers.
 
A.


----- Original Message ----
From: JWColby <jwcolby at colbyconsulting.com>
To: dba-sqlserver at databaseadvisors.com
Sent: Friday, February 16, 2007 6:02:45 AM
Subject: Re: [dba-SQLServer] John Colby's problem


LOL. I don't blame anything on you Arthur.  In fact I believe you are right,
and I have told you that.  What you haven't provided is the "how".  Doing
what you propose requires an entire system to perform the denormalization.
In fact in order to get to where I need to be I have to have a "tblSurvey"
to hold the names of the survey lists as they come in.  My client does not
refer to a specific field, he says "the smoker list".  The survey list has
to be parent to a "tblSurveyField" which holds all of the field names of the
various lists, with a FK back to the survey table.  Then the survey field
DATA has to be held in a table child to tblSurveyField with a fk back to
tblSurveyField and tblPeople.  

So there is this entire process, that has to be done automagically, which
disassembles the surveys, adds the survey names to tblSurvey, survey fields
to tblSurveyField, people to tblPeople, and data to tblSurveyData.  You know
(because you have seen it) that the very first table has 700 fields.  This
is not a job for "manual labor".  I would spend a month just normalizing
that one table.  

You are long on "this is what you need to do" and entirely absent on "this
is the how of doing it".  Your "this is what you need to do" is not news to
me or anyone else.  Of course that is what is needed, I have never denied
that and you know it.  OTOH, I am actually fill orders the way I am doing it
now.  What we both know NEEDS to be done is "someday when the system is
working".  My client needs (and I am giving him) answers now.

>assuming that everything is stored and indexed correctly

And there ya go - "assuming that everything is stored and indexed correctly"
is your entire answer to "this is how to do it".

John W. Colby
Colby Consulting
www.ColbyConsulting.com