proper survey design and "readability"

[pietlinden]

I have the onerous task of querying horribly denormalized databases.
I'm trying to convince the boss that normalization is their friend...
The databases at present are basically one column per question/
demographic etc. So they're all for the most part flat files. But not
always (just to screw me up). If not, I could pretty much union the
sets into summarizable form.

What everyone is afraid of is that the records won't be so easy to
look at... (They have NO forms or reports in their "databases") Also
no control over data entry, either, so querying is a HUGE nightmare.
Basically, I have to re-do query after query because some field names
change or the table structures do...

Okay... how would I show that I can from normalized to repeating
fields? For example,

PatientID WeekNumber TestName Result


To
Columns like this:
(PatientID, TestName_WeekNumber)
Then the value under TestName_WeekNumber

they basically have "sideways" tables (wide and shallow) instead of
(narrow and deep).

For me, there's just no contest.queries are portable etc etc...
I can build a single front end with queries that can look at different
databases and run the same routines... I get all that. The problem is
selling this to the people that have to look at this. I think I might
just build a properly normalized database with some sample data that
mimics what kind of data they're collecting and show them how easy and
portable the queries are (well, assuming that the structures remain
nearly the same)

The problem is that it's treatment research, so time is a significant
dimension.

Any suggestions are welcome.

Thanks!
Pieter

 

[dhauck via AccessMonster.com]

I use to work for the Government. I quit. Hope other have better advise....

I have the onerous task of querying horribly denormalized databases.
I'm trying to convince the boss that normalization is their friend...
The databases at present are basically one column per question/
demographic etc. So they're all for the most part flat files. But not
always (just to screw me up). If not, I could pretty much union the
sets into summarizable form.

What everyone is afraid of is that the records won't be so easy to
look at... (They have NO forms or reports in their "databases") Also
no control over data entry, either, so querying is a HUGE nightmare.
Basically, I have to re-do query after query because some field names
change or the table structures do...

Okay... how would I show that I can from normalized to repeating
fields? For example,

PatientID WeekNumber TestName Result

To
Columns like this:
(PatientID, TestName_WeekNumber)
Then the value under TestName_WeekNumber

they basically have "sideways" tables (wide and shallow) instead of
(narrow and deep).

For me, there's just no contest.queries are portable etc etc...
I can build a single front end with queries that can look at different
databases and run the same routines... I get all that. The problem is
selling this to the people that have to look at this. I think I might
just build a properly normalized database with some sample data that
mimics what kind of data they're collecting and show them how easy and
portable the queries are (well, assuming that the structures remain
nearly the same)

The problem is that it's treatment research, so time is a significant
dimension.

Any suggestions are welcome.

Thanks!
Pieter

--
Microsoft Access and Office Integration Experts
www.tabengineering.com
(e-mail address removed)

Message posted via AccessMonster.com

 

[Guest]

Take a look at "At Your Survey"
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

First, I mean this in a helpful (not negative)way: I read your inquiry and it
touches on about 6 areas (structure, data quality/rules (or lack thereof),
sales (how to sell someone on your idea) frond ends, an unexplained mention
of a "survey" and unexplained reference to user interface design. And the
only clear question seemed to be how to convince managers of then need for
better design. You also didn't say what the main task of these databases
are. Presumably they record the results of various surveys, and you want
somebody to be able to look at them A couple of random thoughts.

If the data is total crap (unintelligable) then you're SOL. I'm assuming
that this is not the case.

If the data is intelligable, but total crap from a database standpoint
(which sounds like the case) then the lowest grade solution might be us use
Access are a mere browser and printer for the various tables.

A few levels up from that would be to create a new structure that
incorporates the nature of all of the existing data, and then have humans
manually transfer. Or identify blocks of info that can be transferred into
the new structure en mass via. queries etc.



On selling it to your bosses, it depends on their quality level, it being
government . If they actually feel that it's important to get useful work
done see below. Otherwise you might have to appeal to their mor basal
motivaitons. Fear: This mess is a problem waiting to get really big and
they're going to look really bad when it does. Or it's a good looking
project that they can try to get approval for thus expanding their budget and
the size of their bureaucracy.

Assuming that they really want to get something done, you have to define
exactly what that is, and then show them that such is impossible to do even
half well with the current system, and then go to them with a concrete plan
to fix it. (not just preach normilaztion or other structure things for
their own sake)

 

[pietlinden]

I use to work for the Government. I quit. Hope other have better advise....
















--
Microsoft Access and Office Integration Experts
www.tabengineering.com
(e-mail address removed)

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...- Hide quoted text -

- Show quoted text -

ROFLMAO. After today, I was ready to do the same.

 

[pietlinden]

Take a look at "At Your Survey"http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
Microsoft Access MVP














- Show quoted text -

In the absence of other sane alternatives, I may show it to them so
they can see what can be done with a properly normalized survey. The
queries I have to write are mind boggling. along with data scrubbing
stuff. It's off the wall. Might be time to throw in the towel and
look for a job again... =(

Pieter

 

[pietlinden]

First, I mean this in a helpful (not negative)way: I read your inquiry and it
touches on about 6 areas (structure, data quality/rules (or lack thereof),
sales (how to sell someone on your idea) frond ends, an unexplained mention
of a "survey" and unexplained reference to user interface design. And the
only clear question seemed to be how to convince managers of then need for
better design. You also didn't say what the main task of these databases
are. Presumably they record the results of various surveys, and you want
somebody to be able to look at them A couple of random thoughts.

If the data is total crap (unintelligable) then you're SOL. I'm assuming
that this is not the case.

If the data is intelligable, but total crap from a database standpoint
(which sounds like the case) then the lowest grade solution might be us use
Access are a mere browser and printer for the various tables.

A few levels up from that would be to create a new structure that
incorporates the nature of all of the existing data, and then have humans
manually transfer. Or identify blocks of info that can be transferred into
the new structure en mass via. queries etc.

On selling it to your bosses, it depends on their quality level, it being
government . If they actually feel that it's important to get useful work
done see below. Otherwise you might have to appeal to their mor basal
motivaitons. Fear: This mess is a problem waiting to get really big and
they're going to look really bad when it does. Or it's a good looking
project that they can try to get approval for thus expanding their budget and
the size of their bureaucracy.

Assuming that they really want to get something done, you have to define
exactly what that is, and then show them that such is impossible to do even
half well with the current system, and then go to them with a concrete plan
to fix it. (not just preach normilaztion or other structure things for
their own sake)














- Show quoted text -

the shocker is that this is a research institue that basically gets
patients for its studies from regional hospitals. they make money by
testing drugs for drug companies, so the data analysis is a critical
part of the reporting process. Except they're "going to Oracle
Clinical any day now". To me it sounds like people in the streets
checking their watches waiting for the second coming. So they see
fixing this as not worthwhile.

So right now I'm trying to make the analysis job at least manageable.
If anybody really wants, I can spew a table structure onto the
screen... I had to write something just to be able to *find* the
columns they're talking about.

I'm writing front ends with standard queries so I can write the stupid
things once and just reuse them. Works okay most of the time, except
when someone mysteriously decided to put things in new places. (No
normalization rules to guide the decisions or anything, though... so
they're largely uninformed guesses.)

I guess PT Barnum *was* right... I might send my supervisor AYS2000
and see what they say... might be interesting.

 

[pietlinden]

oh, I did fix one database. Just because the queries were incredibly
hard to write (in the absence of a union query wizard) in order to get
out simple summary information. but I got lectured when I did that on
how that was not okay. Of course my queries were really small and
fast, easy to write and understand. But changing structure is
unfortunately not an option.