DB design question

[Beringer]

I want to create a DB with tables that hold laboratory data for patients.
Laboratory data is typically something like: Glucose, Thyroid function, and
White blood cell count, etc.
Now there are many, many lab tests available and could all potentially be in
a database.
Secondly, often times labs are grouped together. For instance something
called a BMP is a "panel" of 8 different labs.
Lastly as new labs become available they should be included in the database
(this doesn't seem too hard, just create a new table).

My question is: is it efficent to create a "zillion" tables to hold every
single lab test? (I really don't see anyother way to do it but want to ask
anyway.) And then create tables that model the "panel" tests which
basically hold keys to the individual tables?

Thanks in advance,
eric

 

[Jeff Boyce]

Eric

I would advise against trying to create one table per test.

What is in common among all those tests, and all the zillions of potential
tests? Forget about the "panels" for a moment...

Just a guess, but every test probably has a TestName, a category
(Circulatory, Pulmonary, ...) based on ?body function, a "units of measure",
and a normal "range" of outcome values (perhaps age/gender-related).

If you don't need to know more than this about the tests, use a single table
to hold the test info, and a single table to hold the lookup category info.

Now, for results...

When someone is given a test, they have ... TestResults, right? So a single
table that holds a PersonID, a TestID, a Results, and probably a
TestDateTime gets you close.

Back to a panel. It sounds like you are describing one/more tests that are
collected together under a "family" name. Create a "Panels" table that
lists all the PanelNames (like BMP), and a PanelTest table that holds the
PanelID plus the TestID for each related pair. In your example, there'd be
8 rows in the table, each with the PanelID of the BMP Panel, and one TestID
in each row, for each of the 8 tests.

Or have I totally misunderstood your situation?

 

[Beringer]

You didn't misunderstand at all. Infact, you have helped me quite a bit.
Thank you.

Now I'm thinking of creating one table with the following columns:
TestID, PatientID, ExternalID, TestCategory, Result, NormalRange, Comment

ExternalID would be an ID used to cross reference with the ID used by the
lab that generated the lab result and TestCategory would be a foreign key to
a table that lists names of tests and Comment would be a note about the test
result.

Another table would just hold Panel identification like "BMP" and an ID.

Another table would link tests with a panel and would have the following
columns:
PanelID, TestID.

Did I understand you correctly?

Eric

 

[Jeff Boyce]

Eric

I would advise against a table like:

Now I'm thinking of creating one table with the following columns:
TestID, PatientID, ExternalID, TestCategory, Result, NormalRange, Comment

This design would require the entry of "NormalRange" and "TestCategory" for
every PatientID. One of the concepts behind normalizing your table
structure is to avoid having to duplicate data in more than one table.

Put the information about the test (i.e., NormalRange, TestCategory) into a
table that has information about the tests.

Your PatientResults table need only have PatientID, TestID, TestDateTime,
Result, and Comment.

The report that you generate can rely on a query that joins this
PatientResults table back to the Patient table to see FirstName, ..., and
joins to the Test table to see NormalRange, ...

 

[Beringer]

Thank you very much for your help and time on the matter!
I will use your advice well.
Eric

 

[Beringer]

I have incroprated your ideas but I am stuck again.

Now it has to do with the results of a "Panel." Do I have to create another
table say "PanelTestResults" that contains columns like: PanelID, PatientID,
Date?

The PanelID would be a link to PanelTests so then a join could be done to
get all the individual results for the panel based on the Date. The problem
I have here is what if a patient has two of the same panel tests done on the
same day?

Thanks again,
Eric

 

[Jeff Boyce]

One approach might be to have the individual tests' results rather than a
"panel". You already know that a group of tests were done (you have results
for them). If you also need to note that a "panel" was done, you might be
able to get by with a table like you've suggested.

As to having more than one of the same test or panel run the same day, you
could decide to use Date AND Time, rather than just Date information.

Does that help?

Jeff Boyce
<Access MVP>

 

[GVaught]

Don't name a field 'Date'. This is a reserved word in Access and will cause
problems down the road. Put a subject with the name Date, such as PanelDate
or StartDate etc.

As far as your other question, I am coming into the end of your problem and
therefore can't help in this regard.

 

[Beringer]

While each test of a panel can be ordered seperately and not part of a
panel, a panel is the most common way. So ideally, it would be nice to say:
"Give me the BMP results for 'Patient' on 'Date'."
That is how I came up with the "PanelTestResults" table; is it a proper
table in the since of good design?

Also I think I can get around the two tests on the same day because tests
are usually asigned an "Accession Number" that is unique to the test. So if
I store the Accession number in the PanelTestResult and individual test
result I could join the tables that way.

Thanks again,
Eric