Design Question

[Guest]

I am designing a database where I have a table called Symptoms in this table
I have a field called Visits which is a drop down list from a lookup table.
Also in this table are fields – Patient ID, Symptom, Intensity and Comments.
The visit lookup table contains Baseline, Day 2, Day 15, Day 29, Day 36 and
Final. I need to use the visit dates in a couple of more places but I’m not
sure if I should use this lookup table or create a separate table. I need to
have a table that records FISH results and Cytology results for each visit.
Should I use the lookup table for this as well or should I create a table
that would have FISH Result Baseline, FISH Result Day 2 etc. as fields or a
table that has only 2 fields FISH Result and the dropdown for the Visits?
Sorry for the long question.

Thanks in advance for your help.

Best regards,
Dee

 

[John Vinson]

I am designing a database where I have a table called Symptoms in this table
I have a field called Visits which is a drop down list from a lookup table.
Also in this table are fields – Patient ID, Symptom, Intensity and Comments.

Well... that might be ok if you limit your patients to *ONE* symptom,
and *ONE* comment per visit. What if the patient comes in with
symptomology of cough, fever, headache, itchy skin rash, and nausea?

The visit lookup table contains Baseline, Day 2, Day 15, Day 29, Day 36 and
Final. I need to use the visit dates in a couple of more places but I’m not
sure if I should use this lookup table or create a separate table.

I'd suggest NEVER using Microsoft's misleading and all-but-useless
so-called "Lookup Field" datatype. See

http://www.mvps.org/access/lookupfields.htm

for a critique. Lookup TABLES are fine and routine; lookup Fields in a
table are more hassle than benefit!

Instead, normalize your data: keep Patient information in the Patients
table, one row per patient; Visits information in the Visit table, one
row per visit; etc.

I need to
have a table that records FISH results and Cytology results for each visit.
Should I use the lookup table for this as well or should I create a table
that would have FISH Result Baseline, FISH Result Day 2 etc. as fields or a
table that has only 2 fields FISH Result and the dropdown for the Visits?

ANY time you have fields named Something1, Something2, Something3 then
your table is not in proper form. You're embedding a one to many
relationship (which should be in two tables) into a single record.

I'd see the following table structure:

Patients
PatientID Primary Key <a unique ID, probably something you already
have in a manual system, or use a manually or programmatically
assigned sequential number; preferably *not* an autonumber since
you'll be printing it on charts and such>
LastName
FirstName
MiddleName
<other biographical info>

Symptoms
SymptomCode Primary Key <some unique identifier, maybe autonumber>
Symptom Text <e.g. "Cough", "Fever", etc.>

Visits
VisitID Primary Key <Autonumber>
PatientID <link to Patients, who's visiting>
VisitDate <store a Date/Time and *calculate* the days since the
first visit, don't store them; someone might come in on the 40th day
for their Day39 visit>
Comments Memo <or link one-to-many to a Comments table>

SymptomsObserved
VisitID <link to Visits and thence to Patients>
SymptomCode <link to Symptoms>
Severity <perhaps a link to a small lookup table, but not a Lookup
Field>
Comments Memo

FISHResults
ResultID Primary Key <Autonumber>
VisitID
Result <any fields you need to report results>

and similarly for Cytology.

DO NOT use table datasheets for data entry or display, they are
severly limited in capability. Use a Form instead.

Do note that personal health databases are covered by the *very*
stringent HIPAA regulations in the United States, and by similar laws
in Europe and other countries. I'd suggest that you hire a good
database designer... or a good lawyer, if you don't.


John W. Vinson[MVP]