Need help with relationship????

T

TotallyConfused

I am not sure if I need to use a junction table or not. I have the following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as well as
many diseases. Do I have to have a junction table? Thank you.
 
A

Allen Browne

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in this
table.

You could use the combination of both field (PtID + DoctorID) as the primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a combo
box for the DoctorID. Now you can add as many doctors as you need in the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
T

TotallyConfused

Can my DisCd table be the junction table? DisCD can ref many doc and many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor ID be
in this DISCD table.
 
S

Steve

You actually need another table and possibly TWO junction tables. A patient
sees a doctor(s) for a disease so you need a disease table:
TblDisease
DiseaseID
DiseaseName
etc

A patient could have multiple diseases so you need a patient-disease
junction table:
TblPatientDisease
PatientDiseaseID
PatientID
DiseaseID

You're not done yet! If in ALL cases a patient only sees one doctor for each
disease he has, TblPatientDisease needs to include DoctorID:
TblPatientDisease
PatientDiseaseID
PatientID
DiseaseID
DoctorID

If there's a possibility a patient may see more than one doctor for a
disease he has, TblPatientDisease needs to be:
TblPatientDisease
PatientDiseaseID
PatientID
DiseaseID

and you need a Patient-Disease-Doctor junction table:
TblPatientDiseaseDoctor
PatientDiseaseDoctorID
PatientDiseaseID
DoctorID

Steve
(e-mail address removed)
 
G

Graham Mandeno

Diseases are not related to doctors (unless doctors specialise in certain
diseases, or unless the doctors HAVE the diseases, in which case they would
be patients!)

A simple setup for you would be to have a fourth table for Cases:
CaseID (PK)
CasePatient (foreign key in a 1-M relationship with Patients)
CaseDisease (FK in a 1-M relationship with Diseases)
CaseDoctor (FK in a 1-M relationship with Doctors)
CaseOpenDate
CaseCloseDate
... etc

This design would not allow for such complexities as a patient presenting
with more that one disease, or more than one doctor being involved with a
case, but I think that trying to handle those complexities at this stage
would make you even more than TotallyConfused!

BTW, if you did want to relate doctors with their specialty diseases, then
that would be another many-many relationship and would therefore require a
junction table:
SpecID
SpecDoctor
SpecDisease
 
J

Jeff Boyce

Oooh! oooh! What about a doctor that specializes in a disease that s/he
has?!

Jeff B.

Graham Mandeno said:
Diseases are not related to doctors (unless doctors specialise in certain
diseases, or unless the doctors HAVE the diseases, in which case they
would be patients!)

A simple setup for you would be to have a fourth table for Cases:
CaseID (PK)
CasePatient (foreign key in a 1-M relationship with Patients)
CaseDisease (FK in a 1-M relationship with Diseases)
CaseDoctor (FK in a 1-M relationship with Doctors)
CaseOpenDate
CaseCloseDate
... etc

This design would not allow for such complexities as a patient presenting
with more that one disease, or more than one doctor being involved with a
case, but I think that trying to handle those complexities at this stage
would make you even more than TotallyConfused!

BTW, if you did want to relate doctors with their specialty diseases, then
that would be another many-many relationship and would therefore require a
junction table:
SpecID
SpecDoctor
SpecDisease

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

TotallyConfused said:
Can my DisCd table be the junction table? DisCD can ref many doc and
many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor ID
be
in this DISCD table.
Click to expand...
Click to expand...
 
G

Graham Mandeno

Trust you to make things difficult, Skippy <g>

--
Cheers,
Graham

Jeff Boyce said:
Oooh! oooh! What about a doctor that specializes in a disease that s/he
has?!

Jeff B.

Graham Mandeno said:
Diseases are not related to doctors (unless doctors specialise in certain
diseases, or unless the doctors HAVE the diseases, in which case they
would be patients!)

A simple setup for you would be to have a fourth table for Cases:
CaseID (PK)
CasePatient (foreign key in a 1-M relationship with Patients)
CaseDisease (FK in a 1-M relationship with Diseases)
CaseDoctor (FK in a 1-M relationship with Doctors)
CaseOpenDate
CaseCloseDate
... etc

This design would not allow for such complexities as a patient presenting
with more that one disease, or more than one doctor being involved with a
case, but I think that trying to handle those complexities at this stage
would make you even more than TotallyConfused!

BTW, if you did want to relate doctors with their specialty diseases,
then that would be another many-many relationship and would therefore
require a junction table:
SpecID
SpecDoctor
SpecDisease

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

TotallyConfused said:
Can my DisCd table be the junction table? DisCD can ref many doc and
many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor ID
be
in this DISCD table.

:

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in
this
table.

You could use the combination of both field (PtID + DoctorID) as the
primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a
combo
box for the DoctorID. Now you can add as many doctors as you need in
the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as
well
as
many diseases. Do I have to have a junction table? Thank you.
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

We each have our talents...

S.
Graham Mandeno said:
Trust you to make things difficult, Skippy <g>

--
Cheers,
Graham

Jeff Boyce said:
Oooh! oooh! What about a doctor that specializes in a disease that s/he
has?!

Jeff B.

Graham Mandeno said:
Diseases are not related to doctors (unless doctors specialise in
certain diseases, or unless the doctors HAVE the diseases, in which case
they would be patients!)

A simple setup for you would be to have a fourth table for Cases:
CaseID (PK)
CasePatient (foreign key in a 1-M relationship with Patients)
CaseDisease (FK in a 1-M relationship with Diseases)
CaseDoctor (FK in a 1-M relationship with Doctors)
CaseOpenDate
CaseCloseDate
... etc

This design would not allow for such complexities as a patient
presenting with more that one disease, or more than one doctor being
involved with a case, but I think that trying to handle those
complexities at this stage would make you even more than
TotallyConfused!

BTW, if you did want to relate doctors with their specialty diseases,
then that would be another many-many relationship and would therefore
require a junction table:
SpecID
SpecDoctor
SpecDisease

--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

message Can my DisCd table be the junction table? DisCD can ref many doc and
many
pts. Can the DIScd tble have a PK of autonumber and pT ID and DOctor
ID be
in this DISCD table.

:

Yes: you will have a PatientDoctor table with fields like this:
- PtID relates to the p.k. of your patient table
- DoctorID relates to the primary key of your doctor table

So if a patient has 3 doctors, their PtID will appear in 3 records in
this
table.

You could use the combination of both field (PtID + DoctorID) as the
primary
key of this table if you wish.

To interface this, your patient form will have a subform bound to the
PatientDoctor table. Show the subfom in Continuous Form view. Use a
combo
box for the DoctorID. Now you can add as many doctors as you need in
the
subform for the patient in the main form (one per row.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
I am not sure if I need to use a junction table or not. I have the
following
tables:

Doctor ID (PK)
DocLNM
DocFNM
etc.

PtID(PK)
PTFNM
PTLNM
etc.

DISCD(PK)
DISNM
StartDT
etc

Doctor can have many patients and patients can have many doctors as
well
as
many diseases. Do I have to have a junction table? Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with Tbls 1
relationships of 3 tables 6
Relational Database Join types 4
table design advice needed 3
Tracking Residents Dr. Appointments 7
Table relationships 3
Assistance with Many-to-Many Relationships 6
Military Unit Database 1

Top