Laboratory test orders

[Dave]

I'm going to be shortly starting on my second database. This one will
include ordering laboratory tests, printing worklists and inputting the
results. But I'm a bit confused over table structure. Should I have
different tables for each test? The important items are collection
date, received date, test results, and an accession number assigned to
the specimens. Each of these items would be many, eg. many samples on
the same collection date. Or should every one of these fields be a
separate table, somehow linked? My brain hurts.

Dave B

 

[John Vinson]

I'm going to be shortly starting on my second database. This one will
include ordering laboratory tests, printing worklists and inputting the
results. But I'm a bit confused over table structure. Should I have
different tables for each test?

CERTAINLY NOT!

Storing data in tablenames or fieldnames is *very bad design*.

The important items are collection
date, received date, test results, and an accession number assigned to
the specimens. Each of these items would be many, eg. many samples on
the same collection date. Or should every one of these fields be a
separate table, somehow linked? My brain hurts.

Read (or reread) the Database Design 101 links at Jeff Conrad's
resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

You do need more tables - but not a table per test. Instead, you
should have a table of Tests - just a list of all the tests which
might be taken. This table would have a TestID. If each sample is
taken for only one test, you would have a TestID in the table of
samples. If a single sample can be used for multiple tests you'll need
another table, with the SampleID and the TestID. You'll possibly need
an additional Results table (I'm not sure what the data in results
looks like).

The collection date *is just data*, just a field in the sample table
recording the date on which that sample was collected. There is no
reason to worry about multiple samples on the same date.

John W. Vinson[MVP]

 

[Smartin]

I'm going to be shortly starting on my second database. This one will
include ordering laboratory tests, printing worklists and inputting the
results. But I'm a bit confused over table structure. Should I have
different tables for each test? The important items are collection
date, received date, test results, and an accession number assigned to
the specimens. Each of these items would be many, eg. many samples on
the same collection date. Or should every one of these fields be a
separate table, somehow linked? My brain hurts.

Dave B

I've posted a (very) rudimentary laboratory db design here off the top
of my head, as it were. Please view the pdf here:

http://vfdrake.home.comcast.net/files/laboratory.pdf

Insofar as your questions are concerned it's important to realize the
many-many relationship between tests and requisitions. You can see how I
represented this with "ReqTests" between those entities. Arrows point
from the "many" side to the "one" side of a relationship.

Hope this helps.