If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design question - one or many tables?
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. |
#2
|
|||
|
|||
Design question - one or many tables?
On Sep 15, 12:01*pm, 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. 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. |
#3
|
|||
|
|||
Design question - one or many tables?
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. |
Thread Tools | |
Display Modes | |
|
|