Data Modeling Question - Medical Lab Test Results

I am redesigning a database for two medical offices who are merging their
operations. Each office currently has its own database, and I'm retiring
both with something that will be better. One existing db was implemented in
MS Access and the other in Oracle. The replacement will be implemented in
SQL Server.
Both databases store Lab Test Results in a denormalized table structure. The
MS Access database stores this data "horizontally" (in columns in multiple
tables - with lotsa nulls as can be expected). The Oracle database stores
similar information "vertically" (in two tables - one table is a
"dictionary" of what's possible in the other table which stores the actual
values; missing rows imply a null value in this table).
My preference for the redesign is to identify the logical groupings of lab
results and place those in their own table. So instead of storing the data
"vertically" or "horizontally" in a denormalized fashion, we'd have several
tables related to each other and to the patient that more closely model the
various "things about the patient" the lab tests are measuring.
My question is this: What would you recommend for how we go about the
redesign: keep this data denormalized, or create a more normalized table
structure?
FWIW: Here's sample DDL (stripped of everything but the ColumnName, Data
Type, Length) from the MS Access database. This should give you a pretty
good idea of the kinds of "lab results" being stored in the existing db.
--
PatientMRN Text 10
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
Biopsy Memo 0
BiopsyDate Date 8
VCUG Text 75
VCUGDate Date 8
Ultrasound Memo 0
Ultrasound2 Text 75
UltrasoundDate Date 8
PostVoidResidual Text 75
PostVoidResidualDate Date 8
Urolynamics Memo 0
UrolynamicsDate Date 8
DopplerCarotid Text 75
DopplerCarotidDate Date 8
DopplerLE Text 75
DopplerLEDate Date 8
IliacArtDup Text 75
IliacArtDupDate Date 8
OphthamologyExam Text 75
OphthamologyExamDate Date 8
Mammogram Memo 0
MammogramDate Date 8
PapSmear Memo 0
PapSmearDate Date 8
AdditionalTests1 Memo 0
AdditionalTestsDate1 Date 8
AdditionalTests2 Memo 0
AdditionalTestsDate2 Date 8
AdditionalTests3 Memo 0
AdditionalTestsDate3 Date 8
AdditionalTests4 Memo 0
AdditionalTestsDate4 Date 8
AdditionalTests5 Text 75
AdditionalTestsDate5 Date 8
AdditionalTests6 Text 75
AdditionalTestsDate6 Date 8
Vascular Memo 0
VascularDate Date 8
Gi Memo 0
GiDate Date 8
Cardiology Memo 0
CardiologyDate Date 8
Urology Memo 0
UrologyDate Date 8
Pulmanary Text 75
PulmanaryDate Date 8
Endocrinology Text 75
EndocrinologyDate Date 8
Gyn Text 75
GynDate Date 8
Dentistry Memo 0
DentistryDate Date 8
Psychiatry Memo 0
PsychiatryDate Date 8
Dietary Text 75
DietaryDate Date 8
Other Memo 0
Other2 Text 75
Other3 Text 75
OtherDate Date 8
LastDraw Date 8
CompletionDate Date 8
Na Double 8
K Double 8
CL Double 8
CO2 Double 8
BUN Double 8
CR Double 8
CA Double 8
ALB Double 8
PHOS Double 8
Gluc Double 8
Choles Double 8
Trig Double 8
TP Double 8
AlkPhos Double 8
AST Double 8
ALT Double 8
LDH Double 8
BILI-TOT Double 8
WBC Double 8
RBC Double 8
HGB Double 8
HCT Double 8
Plt Double 8
Segs Double 8
Lymphs Double 8
Monos Double 8
Eosins Double 8
Basos Double 8
PT Double 8
PTDate Date 8
INR Double 8
INRDate Date 8
TSH Double 8
TSHDate Date 8
PSA Double 8
PSADate Date 8
PTH Double 8
PTHDate Date 8
T4 Double 8
T4Date Date 8
Anticardiolipin Text 2
AnticardiolipinDate Date 8
Anticardiolipin2 Double 8
PancreasDate Date 8
CPeptide_Anylase Text 5
GlycoHemo_Lipase Double 8
HDL Double 8
Triglycerides Double 8
Cholesterol Double 8
HbgAIC Double 8
CMV Text 2
TiterGreaterLess Text 2
CMVTiter2 Long 4
CMVTiterDate Date 8
CMVAbIgM Text 2
CMVAbIgM2 Double 8
CMVAbIgMDate Date 8
CMVAbIgG Text 1
CMVAbIgG2 Double 8
CMVAbIgGDate Date 8
VZV Text 1
VZVDate Date 8
Cocci Text 1
CocciDate Date 8
HBsAb Text 1
HBsAbDate Date 8
Toxo Text 1
ToxoDate Date 8
RPR Text 1
RPRDate Date 8
HBsAg Text 1
HBsAgDate Date 8
HTLV Text 1
HTLVDate Date 8
EBVIgG Text 1
EBVIgGGreaterLess Text 2
EBVIgG3 Long 4
EBVIgGDate Date 8
HBcAb Text 1
HBcAbDate Date 8
HIV Text 1
HIVDate Date 8
EBVIgM Text 1
EBVIgMGreaterLess Text 2
EBVIgM3 Long 4
EBVIgMDate Date 8
HCVAb Text 1
HCVAbDate Date 8
ImmABODate Date 8
ImmABO Text 2
ImmRh Text 1
ABORhDate2 Date 8
ABO2 Text 2
Rh2 Text 1
VerifyName Text 30
Event Memo 0
PRA1 Text 7
PRA2 Text 7
HLA_A1 Text 10
HLA_A2 Text 10
HLA_B1 Text 10
HLA_B2 Text 10
HLA_Bw1 Text 10
HLA_Bw2 Text 10
HLA_C1 Text 10
HLA_C2 Text 10
HLA_DR1 Text 10
HLA_DR2 Text 10
HLA_DQ1 Text 10
HLA_DQ2 Text 10
Mismatches Text 50
PeakPercent Integer 2
PeakPercentDate Date 8
CurrentPercent Integer 2
CurrentPercentDate Date 8
AntimurineAb Text 7
AntimurineAbDate Date 8
AntiHorse Text 7
AntiHorseAbDate Date 8
AntiRabbitAb Text 7
AntiRabbitAbDate Date 8
CurrentSpec Text 110
HistoricSpec Text 250
UrineStudyDate Date 8
Studies1 Text 30
Studies2 Text 30
UrineSG Double 8
UrineProt Text 10
UrineGluc Text 5
UrineWBC Text 6
UrineRBC Text 6
UrineBact Text 6
UrineCulure Text 15
CreatinineClearance Double 8
CreatinineClearanceDate Date 8
CalculatedCreatinineClearance Double 8
CalculatedCreatinineClearanceDate Date 8
UrineOther Text 30
UrineOther2 Text 30
EKGComment Text 100
EKGDate Date 8
EchoComment Text 255
EchoComment2 Text 130
EchoGDate Date 8
StressEcho Text 90
StressEchoDate Date 8
CardioliteStress Text 90
CardioliteStressDate Date 8
Rest Text 90
RestDate Date 8
Stress Text 90
StressDate Date 8
CXR Text 90
CXRDate Date 8
PFT Text 90
PFTDate Date 8
ABG Text 65
ABGDate Date 8
Spirometry Text 80
SpirometryDate Date 8
Angio Text 100
AngioDate Date 8
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
<< END of Original POST>>I believe your "vertical" approach sounds like a normalized design. You
would have a table of possible tests, another of patients and then an
associative object (junction table) that links a patient to a test. You'd
have to allow for multiples of the same test on the same patient, though
likely this would not occur on the same day. That table would likely look
like:
PatientTests
PatientID -> FK to Patients
TestID -> FK to Tests
TestDate
Remarks
Hopefully, the table of tests is straightforward, though it is possible that
certain tests would have attributes that would not apply to other tests. In
that case, you could have exclusive subtypes, with a parent test table -
containing the common attributes - and child tables with the specialized
stuff.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Frankie" <A@.B.COM> wrote in message
news:%23P%23ICzxkGHA.2272@.TK2MSFTNGP05.phx.gbl...
I am redesigning a database for two medical offices who are merging their
operations. Each office currently has its own database, and I'm retiring
both with something that will be better. One existing db was implemented in
MS Access and the other in Oracle. The replacement will be implemented in
SQL Server.
Both databases store Lab Test Results in a denormalized table structure. The
MS Access database stores this data "horizontally" (in columns in multiple
tables - with lotsa nulls as can be expected). The Oracle database stores
similar information "vertically" (in two tables - one table is a
"dictionary" of what's possible in the other table which stores the actual
values; missing rows imply a null value in this table).
My preference for the redesign is to identify the logical groupings of lab
results and place those in their own table. So instead of storing the data
"vertically" or "horizontally" in a denormalized fashion, we'd have several
tables related to each other and to the patient that more closely model the
various "things about the patient" the lab tests are measuring.
My question is this: What would you recommend for how we go about the
redesign: keep this data denormalized, or create a more normalized table
structure?
FWIW: Here's sample DDL (stripped of everything but the ColumnName, Data
Type, Length) from the MS Access database. This should give you a pretty
good idea of the kinds of "lab results" being stored in the existing db.
--
PatientMRN Text 10
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
Biopsy Memo 0
BiopsyDate Date 8
VCUG Text 75
VCUGDate Date 8
Ultrasound Memo 0
Ultrasound2 Text 75
UltrasoundDate Date 8
PostVoidResidual Text 75
PostVoidResidualDate Date 8
Urolynamics Memo 0
UrolynamicsDate Date 8
DopplerCarotid Text 75
DopplerCarotidDate Date 8
DopplerLE Text 75
DopplerLEDate Date 8
IliacArtDup Text 75
IliacArtDupDate Date 8
OphthamologyExam Text 75
OphthamologyExamDate Date 8
Mammogram Memo 0
MammogramDate Date 8
PapSmear Memo 0
PapSmearDate Date 8
AdditionalTests1 Memo 0
AdditionalTestsDate1 Date 8
AdditionalTests2 Memo 0
AdditionalTestsDate2 Date 8
AdditionalTests3 Memo 0
AdditionalTestsDate3 Date 8
AdditionalTests4 Memo 0
AdditionalTestsDate4 Date 8
AdditionalTests5 Text 75
AdditionalTestsDate5 Date 8
AdditionalTests6 Text 75
AdditionalTestsDate6 Date 8
Vascular Memo 0
VascularDate Date 8
Gi Memo 0
GiDate Date 8
Cardiology Memo 0
CardiologyDate Date 8
Urology Memo 0
UrologyDate Date 8
Pulmanary Text 75
PulmanaryDate Date 8
Endocrinology Text 75
EndocrinologyDate Date 8
Gyn Text 75
GynDate Date 8
Dentistry Memo 0
DentistryDate Date 8
Psychiatry Memo 0
PsychiatryDate Date 8
Dietary Text 75
DietaryDate Date 8
Other Memo 0
Other2 Text 75
Other3 Text 75
OtherDate Date 8
LastDraw Date 8
CompletionDate Date 8
Na Double 8
K Double 8
CL Double 8
CO2 Double 8
BUN Double 8
CR Double 8
CA Double 8
ALB Double 8
PHOS Double 8
Gluc Double 8
Choles Double 8
Trig Double 8
TP Double 8
AlkPhos Double 8
AST Double 8
ALT Double 8
LDH Double 8
BILI-TOT Double 8
WBC Double 8
RBC Double 8
HGB Double 8
HCT Double 8
Plt Double 8
Segs Double 8
Lymphs Double 8
Monos Double 8
Eosins Double 8
Basos Double 8
PT Double 8
PTDate Date 8
INR Double 8
INRDate Date 8
TSH Double 8
TSHDate Date 8
PSA Double 8
PSADate Date 8
PTH Double 8
PTHDate Date 8
T4 Double 8
T4Date Date 8
Anticardiolipin Text 2
AnticardiolipinDate Date 8
Anticardiolipin2 Double 8
PancreasDate Date 8
CPeptide_Anylase Text 5
GlycoHemo_Lipase Double 8
HDL Double 8
Triglycerides Double 8
Cholesterol Double 8
HbgAIC Double 8
CMV Text 2
TiterGreaterLess Text 2
CMVTiter2 Long 4
CMVTiterDate Date 8
CMVAbIgM Text 2
CMVAbIgM2 Double 8
CMVAbIgMDate Date 8
CMVAbIgG Text 1
CMVAbIgG2 Double 8
CMVAbIgGDate Date 8
VZV Text 1
VZVDate Date 8
Cocci Text 1
CocciDate Date 8
HBsAb Text 1
HBsAbDate Date 8
Toxo Text 1
ToxoDate Date 8
RPR Text 1
RPRDate Date 8
HBsAg Text 1
HBsAgDate Date 8
HTLV Text 1
HTLVDate Date 8
EBVIgG Text 1
EBVIgGGreaterLess Text 2
EBVIgG3 Long 4
EBVIgGDate Date 8
HBcAb Text 1
HBcAbDate Date 8
HIV Text 1
HIVDate Date 8
EBVIgM Text 1
EBVIgMGreaterLess Text 2
EBVIgM3 Long 4
EBVIgMDate Date 8
HCVAb Text 1
HCVAbDate Date 8
ImmABODate Date 8
ImmABO Text 2
ImmRh Text 1
ABORhDate2 Date 8
ABO2 Text 2
Rh2 Text 1
VerifyName Text 30
Event Memo 0
PRA1 Text 7
PRA2 Text 7
HLA_A1 Text 10
HLA_A2 Text 10
HLA_B1 Text 10
HLA_B2 Text 10
HLA_Bw1 Text 10
HLA_Bw2 Text 10
HLA_C1 Text 10
HLA_C2 Text 10
HLA_DR1 Text 10
HLA_DR2 Text 10
HLA_DQ1 Text 10
HLA_DQ2 Text 10
Mismatches Text 50
PeakPercent Integer 2
PeakPercentDate Date 8
CurrentPercent Integer 2
CurrentPercentDate Date 8
AntimurineAb Text 7
AntimurineAbDate Date 8
AntiHorse Text 7
AntiHorseAbDate Date 8
AntiRabbitAb Text 7
AntiRabbitAbDate Date 8
CurrentSpec Text 110
HistoricSpec Text 250
UrineStudyDate Date 8
Studies1 Text 30
Studies2 Text 30
UrineSG Double 8
UrineProt Text 10
UrineGluc Text 5
UrineWBC Text 6
UrineRBC Text 6
UrineBact Text 6
UrineCulure Text 15
CreatinineClearance Double 8
CreatinineClearanceDate Date 8
CalculatedCreatinineClearance Double 8
CalculatedCreatinineClearanceDate Date 8
UrineOther Text 30
UrineOther2 Text 30
EKGComment Text 100
EKGDate Date 8
EchoComment Text 255
EchoComment2 Text 130
EchoGDate Date 8
StressEcho Text 90
StressEchoDate Date 8
CardioliteStress Text 90
CardioliteStressDate Date 8
Rest Text 90
RestDate Date 8
Stress Text 90
StressDate Date 8
CXR Text 90
CXRDate Date 8
PFT Text 90
PFTDate Date 8
ABG Text 65
ABGDate Date 8
Spirometry Text 80
SpirometryDate Date 8
Angio Text 100
AngioDate Date 8
Stent Text 60
StentDate Date 8
CABG Text 75
CABGDate Date 8
<< END of Original POST>>|||>> My question is this: What would you recommend for how we go about the
Do you know the benefits of having a normalized schema? Do you understand
the drawbacks of "denormalized" structures? You have already stated that
your preference is to identify the logical groupings of lab results and
place those in their own table. Therefore, the answer should be obvious.
Anith