P
pietlinden
I'm working with hideously unnormalized databases, most of which have
similar structures. (Of course, if they had the same structures, it
would be too easy.)
I work at a company that does cancer research, so they collect
information about their patients over a series of visits. The
questions they ask (queries) for parts of the final report are
standard across all databases, and then some are specific to a the
type of study they're doing.
What is the best way to approach this, given that I have to summarize
like 20 databases in maybe two weeks, and the column names are not
necessarily even consistent. Nor is the location of some fields.
(Imagine a database built by someone that does not really understand
normalization, so he does it on _some_ tables and not on others.)
that's basically what I'm faced with.
Oh, MAJOR CAVEAT: I am not allowed to normalize the databases. (that
was my first inclination, and I was told very explicitly NOT TO.
Right now my plan is to do something like this:
1. Create a generic front end for each database type. (there are like
5 different ones).
2. Include all the queries that are "stable"/consistent across all
databases.
3. turn that into a template and then
4. customize the template for each of the various study types.
(because they'll basically add columns and standard queries).
Yes, I know this is a lot of work for a screwed database structure,
but I am in no way for no reason allowed to change it. So I'm getting
good at building utilities that create insane union queries for me,
because I'm querying essentially repeating fields, eg
WBC_Week1
WBC_Week2
WBC_Week3
WBC_Week4
Yes, I know not to store information in column names, but whoever
built these monstrosities did not know that. and that's where the
data is, so I'm stuck with it.
is there an easier way to get to the finish line (besides taking the
subway)?
Thanks!
Pieter
similar structures. (Of course, if they had the same structures, it
would be too easy.)
I work at a company that does cancer research, so they collect
information about their patients over a series of visits. The
questions they ask (queries) for parts of the final report are
standard across all databases, and then some are specific to a the
type of study they're doing.
What is the best way to approach this, given that I have to summarize
like 20 databases in maybe two weeks, and the column names are not
necessarily even consistent. Nor is the location of some fields.
(Imagine a database built by someone that does not really understand
normalization, so he does it on _some_ tables and not on others.)
that's basically what I'm faced with.
Oh, MAJOR CAVEAT: I am not allowed to normalize the databases. (that
was my first inclination, and I was told very explicitly NOT TO.
Right now my plan is to do something like this:
1. Create a generic front end for each database type. (there are like
5 different ones).
2. Include all the queries that are "stable"/consistent across all
databases.
3. turn that into a template and then
4. customize the template for each of the various study types.
(because they'll basically add columns and standard queries).
Yes, I know this is a lot of work for a screwed database structure,
but I am in no way for no reason allowed to change it. So I'm getting
good at building utilities that create insane union queries for me,
because I'm querying essentially repeating fields, eg
WBC_Week1
WBC_Week2
WBC_Week3
WBC_Week4
Yes, I know not to store information in column names, but whoever
built these monstrosities did not know that. and that's where the
data is, so I'm stuck with it.
is there an easier way to get to the finish line (besides taking the
subway)?
Thanks!
Pieter