Trying to redisign the perfect database...

[NC_Sue]

....to track enrollment of patients into certain courses of treatment. I have

800 patients, > 100 treatment plans. Patients may be enrolled in multiple

courses of treatment (USUALLY at different times), and they may have more
than one "status" for any given course of treatment (i.e. I may "screen" a
patient on January 1st & determine that they're ineligible, perhaps repeat
the screening process on March 1st & find that they're ineligible, then
determine that they're eligible & begin this course of treatment of April
19th). A year later, they may begin a different sort of treament altogethere.

I need to track each time a patient is screened (and for which treatment
plan) and each time they actually begin a treatment plan (including any and
all treatment plans they begin). Currently I'm entering the patient in a
"patient table" multiple times - once for each time they screen, and once for
each time they start a course of treatment. It WORKS... but I'm violating
every normalization rule known to God and man.

I'd like to redesign the database "the right way". Any clues how I should
start? I need to include (for each patient) the day they are screened, their
status (screen failure, started treatment, on to other treatment, etc), and
the course of treatment. Again, there's a possibility that any one patient
will have 2 - 3 "statuses" for any one course of treatment and more than one
course of treatment where they have a status.



Thanks.

 

[Tim Ferguson]

I'd like to redesign the database "the right way". Any clues how I
should start? I need to include (for each patient) the day they are
screened, their status (screen failure, started treatment, on to other
treatment, etc), and the course of treatment.

Candidates for tables would be:

Patients(*FileNumber, FName, DoB, etc)

Treatments(*TxCode, ResponsibleDr, LegalStatus, etc)

Screenings(*Patient, *DateOfScreen, Result)
FK Patient references Patients.FileNumber
? are there different screenings for each treatment

Allocations(*Patient, *Tx, DateAllocated, PerCentCompliance,etc)
FK Patient references Patients.FileNumber
FK Tx references Treatments.TxCode

Statuses(*Patient, *TxCode, *DateValid, CurrentDescription)

Again, there's a
possibility that any one patient will have 2 - 3 "statuses" for any
one course of treatment and more than one course of treatment where
they have a status.

I am not sure that I understand what this means: can you provide an
example of what a Status record looks like?

One thought: if there are real patients and real treatments involved
here, and if it's as complex a situation as it appears, are you sure that
you are the right person to be designing this? Who goes to prison when a
patient is damaged after receiving the wrong treatment?

All the best


Tim F

 

[NC_Sue]

Actually I'd probably be one of the folks in prison regardless... I'm
responsible for coordinating their care. But I'm having better luck keeping
track of things with my database than I had when I inherited the reams of
notebooks (and not particularly well organized at that).

Status examples:
Workup
On study
Off study
Longterm followup
Dead

One particular patient I have in mind was worked up twice (different dates)
before going on one particular course of treatment (although he was "worked
up" for yet 2 other treatments), and has since into long-term followup.

 

[mnature]

As a suggestion:

tbl_Patients
PatientID (PK)
PatientInfo,etc.

tbl_Teatments
TreatmentID (PK)
TreatmentInfo,etc.

tbl_Screenings
ScreeningID (PK)
PatientID [relate from tbl_Patients]
TreatmentID [relate from tbl_Treatments]
ScreeningsInfo,etc.

tbl_TreatmentPlan
TreatmentPlanID
ScreeningID [relate from tbl_Screenings]
TreatmentPlanInfo,etc.

Every screening is associated with a patient and a treatment. You are
screening them to see if they are eligible/need the treatment. A screening
may lead to a treatment plan. The screening already determines the patient
and the treatment, so an entry in the treatment plan table just shows that
the patient was eligible and needed the treatment. Not every screening ends
up with a treatment plan, but each treatment plan refers back to a specific
screening.

 

[Tim Ferguson]

One particular patient I have in mind was worked up twice (different
dates) before going on one particular course of treatment (although he
was "worked up" for yet 2 other treatments), and has since into
long-term followup.

To me, this suggests you actually have a new entity of Programmes, which
would apply to details of a patient's progress through a particular
workup (okay, you could call it WorkUps if you prefer..!), looking
something like

ProgrammeID primary key
PatientNum references Patients not Null
AcceptedOnto references Treatments can be Null
DateStarted
Comments, FinalOutcome, etc etc

Status examples:
Workup
On study
Off study
Longterm followup
Dead

This suggests a sequence too, so the status table could look something
like this, with the PK set to (Programme, DateSet). The Programme column
would be a FK referencing Programmes, and the Description column would
be a FK referencing Statuses.

Programme DateSet Description
========= ========== -----------
10209 2004-01-12 On study
10209 2004-07-30 Longterm FU
10209 2006-03-31 Off study


This would be able to reconstruct the entire history of each Programme,
and it would be dead easy to find the "current" status by a query using
MAX(DateSet), and so on.

Hope that helps


Tim F

 

[Jeff Boyce]

If your "practice" is in the US, there are some stringent new regs covering
what/how data is collected/stored about patients. There may be similar
regulations and laws governing the topic in other jurisdictions.

Before you proceed, are you confident that your approach/abilities can
comply with the local/state/national requirements?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mscertified]

There are legal restrictions on keeping personal health information (HIPAA
rules) so you better make sure this data is absolutely secure from prying
eyes. Preferably separate any personally identifying information (names,
ssn's) from the actual health data. You need to be aware of all the HIPAA
regulations before designing this.

-Dorian

 

[Tim Ferguson]

Preferably separate any personally identifying information (names,
ssn's) from the actual health data. You need to be aware of all the
HIPAA regulations before designing this.

There are times I am really glad that I live in the UK... I feel so much
more secure! <g>

Tim F

 

[Jeff Boyce]

Tim

?!You feel more secure?! Because you don't have your government regulating
how your hospitals capture/store data about you?!

<G> Jeff

 

[Tim Ferguson]

?!You feel more secure?! Because you don't have your government
regulating how your hospitals capture/store data about you?!

No: it's the opposite[1]. European data protection legislation has had to
write in special accommodation for the USA, where the regulations do not
come up to EU standards, because otherwise there would be no data
exchange across the Atlantic at all.

[1] I am by no means an expert in HIPAA rules -- I tried to read the web
site once but found it very hard to work out what the perspective was.
From the quotation above, however, I would be very worried. This kind of
sophistry ("separating" identifying information from data) is explicitly
outlawed as a solution.

UK data protection law refers to "relevant filing systems" which includes
any files that can be accessed by a personal identity -- whether that is
a shoe box full of index cards (if it's in alphabetical order) or a
singing-and-dancing Oracle-served database. Data Processing includes
storing and manipulating anky kind of personal data: this means that
printing out a database and deleting the electronic file still qualifies
because current scanners and OCR technology is such that the pile of
paper is still capable of being automatically processed. If Personal Data
_can_ be constructed from the system, then it's subject to DPA
registration.

Schedule Two data (eg data on health, religon, employment etc) carry
extra burdens of consent and subject access. Overall, there is very
little that one is not allowed to do; but registration is dependent on
very high standards of probity and quality, and data processors can be
held _personally_ responsible for failure or damage to subjects. To be
sure, the Data Registrar has made some laughably disastrous judgements
but not nearly as many as are made in her name by lazy or defensive
administrators - the Soham tragedy is but one example.

Sorry: bit off topic for a tables-db-design group!

All the best


Tim F

 

[Jeff Boyce]

Thanks, Tim. I wasn't aware that US regs were not rigorous enough to meet
European standards! The Wild West, I suppose...

Jeff

?!You feel more secure?! Because you don't have your government
regulating how your hospitals capture/store data about you?!

No: it's the opposite[1]. European data protection legislation has had to
write in special accommodation for the USA, where the regulations do not
come up to EU standards, because otherwise there would be no data
exchange across the Atlantic at all.

[1] I am by no means an expert in HIPAA rules -- I tried to read the web
site once but found it very hard to work out what the perspective was.
From the quotation above, however, I would be very worried. This kind of
sophistry ("separating" identifying information from data) is explicitly
outlawed as a solution.

UK data protection law refers to "relevant filing systems" which includes
any files that can be accessed by a personal identity -- whether that is
a shoe box full of index cards (if it's in alphabetical order) or a
singing-and-dancing Oracle-served database. Data Processing includes
storing and manipulating anky kind of personal data: this means that
printing out a database and deleting the electronic file still qualifies
because current scanners and OCR technology is such that the pile of
paper is still capable of being automatically processed. If Personal Data
_can_ be constructed from the system, then it's subject to DPA
registration.

Schedule Two data (eg data on health, religon, employment etc) carry
extra burdens of consent and subject access. Overall, there is very
little that one is not allowed to do; but registration is dependent on
very high standards of probity and quality, and data processors can be
held _personally_ responsible for failure or damage to subjects. To be
sure, the Data Registrar has made some laughably disastrous judgements
but not nearly as many as are made in her name by lazy or defensive
administrators - the Soham tragedy is but one example.

Sorry: bit off topic for a tables-db-design group!

All the best


Tim F

 

[Tim Ferguson]

The Wild West, I suppose...

<g>


Tim F