Feedback on my db's logical relationship design

  • Thread starter Thread starter ESheehan
  • Start date Start date
E

ESheehan

[Using Access 2007]

Would like to run this relationship design by the community for some feedback:

I am building a db that tracks a patient's test results over the course of
12 months. I have set-up a "Patient Data" table that includes a few
demographic variables, as well as a unique Patient ID field. That field is
the Primary Key for that table.

Then, there are a series of tables that represent the various tests that
will be repeated at regular intervals (i.e., a "distance walked" test at
Visit 1, Visit 2, Visit 3, etc.). Each of these tables also has a "Subject
ID" field. However, I've made an AutoNumber field the primary key for these
tables. Each patient should have only one record in each unique Visit/Test
table. We eventually will want to run queries that pull a patient's results
from each visit per test to see any improvement or degradation on their test
results.

I thought this would be a One-to-Many relationship but would like any
feedback on other ways to represent a logical relationship to ensure that
queries run correctly in the future.

Thanks for any input or ideas.
 
You should have only one table of test results with a field that identifies
the type of test. If one test will be performed multiple times for one
patient based on which visit the test will be performed, then you need a
field to identify the visit.
 
But wouldn't that one table of test results be huge? I suspect the number of
tests and the data results being recorded from each test would be unwieldy
for one table. There are at leasts 10 tests each being performed a minimum
of six times. Some of the forms for these tests which the tables are based
on have dozens of questions on them.

I tried an earlier design where I did one table for each test with a field
that included a drop-down pick list for each visit, but I had a hard time
isolating the variables to compare across visits.



Klatuu said:
You should have only one table of test results with a field that identifies
the type of test. If one test will be performed multiple times for one
patient based on which visit the test will be performed, then you need a
field to identify the visit.
--
Dave Hargis, Microsoft Access MVP


ESheehan said:
[Using Access 2007]

Would like to run this relationship design by the community for some feedback:

I am building a db that tracks a patient's test results over the course of
12 months. I have set-up a "Patient Data" table that includes a few
demographic variables, as well as a unique Patient ID field. That field is
the Primary Key for that table.

Then, there are a series of tables that represent the various tests that
will be repeated at regular intervals (i.e., a "distance walked" test at
Visit 1, Visit 2, Visit 3, etc.). Each of these tables also has a "Subject
ID" field. However, I've made an AutoNumber field the primary key for these
tables. Each patient should have only one record in each unique Visit/Test
table. We eventually will want to run queries that pull a patient's results
from each visit per test to see any improvement or degradation on their test
results.

I thought this would be a One-to-Many relationship but would like any
feedback on other ways to represent a logical relationship to ensure that
queries run correctly in the future.

Thanks for any input or ideas.
Click to expand...
Click to expand...
 
But wouldn't that one table of test results be huge?
Click to expand...

Depends: what do you mean by "huge"? Do you expect over 20,000,000 rows in the
result table? That's getting huge. If you're talking about fewer than 100,000
Access should have NO problem.
I suspect the number of
tests and the data results being recorded from each test would be unwieldy
for one table. There are at leasts 10 tests each being performed a minimum
of six times. Some of the forms for these tests which the tables are based
on have dozens of questions on them.

I tried an earlier design where I did one table for each test with a field
that included a drop-down pick list for each visit, but I had a hard time
isolating the variables to compare across visits.
Click to expand...

More details please. It's a lot easier to create forms to adapt to a properly
normalized design than to work with a flawed table structure!
 
To add to John's reply: The total number of records is the same, no matter
how many tables are used. And it is much easier and more efficient, both in
app design and db performance, to operate on fewer tables.
 
Back
Top