Design question - one or many tables?

tc2004 · External Since: Sep 15, 2009 Posts: 1

I am building a DB for a medical study that collects data approximately every
6 months (lab values and imaging results). In addition to recording the
values, I would also like to build a functionality that will remind the study
coordinator to schedule labs and tests shortly before the 6 month mark comes
up.

Is it better to have one table with multiple records per study patient (one
for each 6 month lab/imaging result) or multiple tables (one for each 6 month
interval) with one record per patient?

I can picture how to do the reminder with multiple tables, but maybe there
is a better way with putting all lab/imaging results in one table? It would
definitely make the design easier.

Thanks.

Piet Linden · External Since: Jan 31, 2009 Posts: 17

On Sep 15, 12:01 pm, tc2004 <tc2....TakeThisOut@discussions.microsoft.com> wrote:
> I am building a DB for a medical study that collects data approximately every
> 6 months (lab values and imaging results). In addition to recording the
> values, I would also like to build a functionality that will remind the study
> coordinator to schedule labs and tests shortly before the 6 month mark comes
> up.
>
> Is it better to have one table with multiple records per study patient (one
> for each 6 month lab/imaging result) or multiple tables (one for each 6 month
> interval) with one record per patient?
>
> I can picture how to do the reminder with multiple tables, but maybe there
> is a better way with putting all lab/imaging results in one table? It would
> definitely make the design easier.
>
> Thanks.

This one:
Is it better to have one table with multiple records per study patient
(one
> for each 6 month lab/imaging result)

Then just add a date field for when the result is due.
This is how I did it:
CREATE TABLE Labs(
PatientID,
Test,
Result,
TestDate)
PK is PatientID, Test, TestDate - will work *assuming* a patient
cannot have the same test repeated twice on the same date.

Klatuu · External Since: Apr 06, 2005 Posts: 1360

One of the rules to live by is if you have two or more tables with the exact
same fields, you are doing it wrong (it almost all cases).

As to the best way to design this, it will depend on some other information.
For example, does one study = one patient, or are there multiple patients
for a study?
Do all patients have the same study date?
Do you currently have a Study table?
Are the tests in a study always the same, or do different studies have
different test included?
Do all patients take the same tests for a study?
Do you have a table that identifies the tests that can be performed?

And some I can't even ask because I don't know enough about your other data.
--
Dave Hargis, Microsoft Access MVP

"tc2004" wrote:

> I am building a DB for a medical study that collects data approximately every
> 6 months (lab values and imaging results). In addition to recording the
> values, I would also like to build a functionality that will remind the study
> coordinator to schedule labs and tests shortly before the 6 month mark comes
> up.
>
> Is it better to have one table with multiple records per study patient (one
> for each 6 month lab/imaging result) or multiple tables (one for each 6 month
> interval) with one record per patient?
>
> I can picture how to do the reminder with multiple tables, but maybe there
> is a better way with putting all lab/imaging results in one table? It would
> definitely make the design easier.
>
> Thanks.