Design question - one or many tables?

  • Thread starter Thread starter tc2004
  • Start date Start date
T

tc2004

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.
 
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.
Click to expand...

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

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.
 
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.
 
Back
Top