3 tables with common field - What relationship do I use?

[Raj Patel]

Hello,

I am a newbite to Access and have a question.

I have three tables in a medical database that are of concern:

tblQuestions
------------
PK - QuestionID
FK - ExamID
Number

tblEncounters
-------------
PK - EncounterID
FK - ExamID
Date
Location

tblHospitalizations
-------------------
PK - HospitalizationID
FK - PatientID
AdmitDate
LocationType
LocationName
KeywordPresent

All of these tables relate to a final table that looks like this:

tblDiagnoses
------------
DiagnosisID
Reason
Code
SP

Each on of the tables can had multiple diagnoses. Right now I have
created many-to-many relationships between these three tables and a
single tblDiagnoses table. This way each record in the three tables
can have multiple diagnoses. I am not sure whether this is the proper
way to setup this relationship. How else can/should this be done? A
one-to-many relationship seems right, but that will not work since
there are three tables that all relate to tblDiagnoses.

Thanks.

 

[Jeff Boyce]

From the fields you described in the four tables, I didn't see the "common
field"...

Rather than speak in "tables", could you describe your underlying data? It
might help 'group readers to understand the relationships among your
entities.

 

[Rajesh B. Patel]

Sorry.

BACKGROUND - I am trying to design a medical research db. We have many
patients in this study. We will peruse their records and pull specific data
from the records. The data is obtained from three separate places (all paper
records) -

1. Official Hospital Records.
2. Forms submitted by patient detailing medical history.
3. Forms administered when patients came into our clinic.

We are interest in pulling all "official" medical diagnoses from the three
sources.

DATA - Each hospital record, patient-submitted form, and study-administered
form can detail several diagnoses. Each diagnosis should have the actual
diagnosis name, it's code, and a bit flag (for other purposes). I have three
tables for the different data sources since each form has differences in the
data it contains, but as I said before they all have diagnoses. What is the
proper way to design the relationships between the three data sources tables
and the single diagnosis table.

As I mentioned in my other post I have many-to-many relationships between
each of the data source tables and the single diagnoses table. This allowed
me to associate sone hospital record with several diagnoses without having a
associated record in the patient-submitted form or study-administered form
that corresponded to that same diagnosis (originally I tried three
one-to-many relationships between the data source tables and the diagnoses
table, but that neither made sense nor was it allowed since every diagnoses
then must be associated with a record in all three data source tables...).

Does this clarify Jeff? Thanks.

 

[RSGinCA]

One of the first steps in building a database is analyzing the data. It isn't
until later that you can decide how each records is designed. Sometimes the
data is pretty simple and the analysis is very simple and you can quickly jump
to the record design process. But even then, especially if you're a newbie you
can come up with a poor design.

That's why Jeff was asking about the underlying data. Let me expand on that.
You've provided some information about the information that will be used as
input to the database. It also helps to look at the information that you
expect to get from the database.

So can you tell us how you plan on using this database and what information you
want to get out of it. Are you planning on producing reports that can be
analyzed? If so, what kind of information do you want on those reports. Are
you looking for detail info? Summary info? Are you interested in detail
information for each patient?

Rick

 

[Rajesh B. Patel]

Ok here goes...

We will use all of the diagnoses for a particular patient to generate a
detailed report with all of that patient's diagnoses. In addition, we will
probably want reports that detail the number of occurences of a particular
disease in the entire patient population. The analysis phase of the study
has not been considered yet since it will take several years to collect all
the data so I cannot expand more on what exact reports we will need. Much of
this data might be moved into a SAS dataset so there may be no need to
generate much more than the summary reports for each patient.

Again sorry I cannot tell you the exact the reports since those will be
determined later by investigators on the study.

Thanks.

raj

 

[Rajesh B. Patel]

Rick and Jeff,

Perhaps if I describe in detail part of my data and what type of output I
might want, you can give me the appropriate model and I can apply to the
rest of application...

There are many study participants. Each patient has two identifiers -
ParticipantID and Date of Birth. Each participant has a known, fixed number
of visits into our clinic. These are called exams and there is a number
associated with each exam (i.e. Exam 1, Exam 2, Exam 3, ...). Participant
population we are considering now, there are exactly 7 exams. Each exam, in
turn, has several fields of interest. The number of fields varies based on
the exam. For each exam there is a known number of fields that we are
interested in (i.e. for Exam 1 there are 3 fields and for Exam 2 there are 6
fields ...). Finally each one of the fields can have many diagnoses in it.
For example, one example of a field that we are collecting from a Exam is
called "Clinical Diagnostic Impression". In this space on the paper form
there are many diagnoses that can be listed and we are interested in
collecting them all.

For this data I will be interested in generating a detail report for each
participant that lists all their diagnoses and which exam they occured in.
Also I will want a totals reports for each different diagnoses and the
number of particpants that diagnosis occurred in.

I hope this is something like what you are looking for. I appreciate your
help. I designed this with no Access experience and I don't feel I have the
tables/relationships right. I can give you more detailed information on my
current tables and relationships if that will help.

Thanks.

raj

 

[RSGinCA]

Note: In the spirit of full disclosure let me say that I'm also a newbie to
Access. I have experience with database design and with other database
systems, but I only have about 2 months experience with Access. Fortunately,
your inquiry is in the area of database design where I do have experience.

After looking at the additional info that you provided, I now understand your
original question better.

Simplifying a bit, as I understand it, you have 3 different sources of data to
obtain patient diagnoses (hospital records, medical history form completed by
the patient, and clinic forms completed by the clinic at each of the patient's
exams). In your db, you need to record these diagnoses, and other info from
these 3 sources of data. At least some of the types of information that you
want to record will be different depending on which source, i.e., each source
contains 'fields' not contained on the other sources. However, each source
could identify 1 or more diagnoses for the patient.

In the approach that you came up with, and which you had doubt about, you had a
Patient Diagnosis table, which contained one record for each diagnosis for a
patient. You also had 1 table for each of the 3 different sources of data.
These 3 'source data' tables were related to the Patient Diagnosis table in a
1-to-Many relationship. In this design, any record on one of the 3 'source
data' tables woulkd be related to 1 or more records on the Patient Diagnosis
table. And, any record on the Patient Diagnosis table would be related to 1
(and only 1) record on 1 of the 3 tables. (In your 1st posting you described
this as 3 Many-to-Many relationships. I don't know that I understand that, so
maybe I'm missing something... If I do understand it, the approach I present
can be adjusted to eliminate that M-to-M relationship.)

Your design had:

PatientDiagnosis <<---> Source1 (M-1)
PatientDiadnosis <<---> Source2 (M-1)
PatientDiagnosis <<---> Source3 (M-1)


I'll suggest an alternative. It doesn't eliminate the problem of 3 different
sources of data, but it does isolate it a bit. It will replace your three
1-to-Many relationships with a single 1-to-Many relationship and three 1-to-1
relationships.

My alternative involves an intermediate 'generic' source record table. A
source record would be generated on that table for each of the 3 sources of
data. A single record would also be generated on one of the specific source
data tables (thus there would be a 1-to-1 relationship between the generic
source table and any of the 3 specific source tables).

The 'generic' source table would be related to the Patient Diagnosis table as
follows:

PatientDiagnosis <<---> GenericSource (M-1)


The 'generic' source table would have 3 1-to-1 relationships:

GenericSource <---> Source1 (1-1)
GenericSource <---> Source2 (1-1)
GenericSource <---> Source3 (1-1)


This is generally the approach that I would take, however the situation you
have described still leaves a lot of questions in my mind. For instance, I
would presume that the same identical diagnosis would be repeated on different
source of data for a patient. I was in an auto accident a few years ago and
went to the hospital with a concussion. That would be on my hospital records
and on the medical history that I would fill out. How would this be handled?
Would there be a single Patient Diagnosis record for this concussion? If so,
would you still want your database to be able to associate it with the 2
separate source of data?

Below is a tentative first-stab and partial table design of some of the
pertinent records. (If this were my database, I'd still have a lot more
questions.)


tblPatientDiagnosis
___________________
*PatientID
*DiagnosisCode
*DiagnosisDate
DataSourceID (this is a unique ID assigned to a source record)


tblPatientDataSource (this is the 'generic' source table)
--------------------
*DataSourceID (a unique ID assigned to a source record)
DataSourceType
PatientID
Date
(other data common to all 3 sources)


tblPatientHospitalRecord ( 'Source1' )
------------------------
*DataSourceID
PatientID
(other data unique to Source1)


tblPatientMedicalHistory ( 'Source2' )
------------------------
*DataSourceID
PatientID
(other data unique to Source2)


tblPatientClinicExam ( 'Source3' )
--------------------
*DataSourceID
PatientID
ExamNbr
(other data unique to Source3)


tblDiagnosis
------------
*DiagnosisCode
DiagnosisDescription



Rick