Design problem and suggestions...

Hello,

I need some design advice.
I have a database that takes care of clients; it also has a table of doctors.
I have a many to many relationship between the two obviously.

The challenge I have come across is that every client has a PCP (Primary
care provider.) Each client also has a neurologiest, therapist, dentist, etc.
I have these specialties defined in the doctor table.
I can't think of any other way than to create a PCP, Neurologist, etc. field
in the client table, then have these fields somehow pull the information from
the doctor table.
Is this the only way to do this, or is there a better way? Doesn't doing
what I have mentioned above violate good DB design?
Puzzled,

Rookie.

Use the junction table that you already have, and add another field to it to
show the "specialty" of the physician. Thus, your "unique" index for the
table would be three fields: DoctorID, DoctorTypeID, ClientID.

--

Ken Snell
MS ACCESS MVP

"Access rookie" wrote in message
...
Hello,

I need some design advice.
I have a database that takes care of clients; it also has a table of
doctors.
I have a many to many relationship between the two obviously.

The challenge I have come across is that every client has a PCP (Primary
care provider.) Each client also has a neurologiest, therapist, dentist,
etc.
I have these specialties defined in the doctor table.
I can't think of any other way than to create a PCP, Neurologist, etc.
field
in the client table, then have these fields somehow pull the information
from
the doctor table.
Is this the only way to do this, or is there a better way? Doesn't doing
what I have mentioned above violate good DB design?
Puzzled,

Rookie.

On Wed, 9 Feb 2005 19:13:27 -0800, "Access rookie"
wrote:

Hello,

I need some design advice.
I have a database that takes care of clients; it also has a table of doctors.
I have a many to many relationship between the two obviously.

A hint. When your doctor gets sick . . .


--
Mike Sherrill
Information Management Systems

Mike Sherrill wrote:
When your doctor gets sick . . .

.... she is will also be a Patient entity. I was thinking separate
tables for Doctors, Specialisms and SpecialistDoctors respectively but
you seem to be suggesting a 'base' table for Person entities i.e. a
Doctor is a Person, a Patient is a Person. I think this would only have
limited advantages in the data model described. Now, how to model the
business rule, ' Physician, heal thyself' ...

Jamie.

--

Hey Ken,

Thanks for your reply; I didn't understand it though.
The specialty for each doctor is indicated in the doctor table as one of the
fields.
I do have a specialty table that the specialty field looks up its values from.
If I add the SpecialtyID to the junction table, what does that do? I know
that when I pull the doctor information up, it will show up that the doctor
is a cardiologist, etc. but that would work without adding the SpecialtyID
field to the junction table because the specialty is part of the doctor
profile.

Also, that doesn't take care of the PCP problem...because a client's primary
care provider could be a cardiologist, neurologist, etc. As I have a many to
many relationship, how do I specify who is the Primary Care Provider?

In a dark tunnel running out of oxygen,

Rookie.

"Ken Snell [MVP]" wrote:

Use the junction table that you already have, and add another field to it to
show the "specialty" of the physician. Thus, your "unique" index for the
table would be three fields: DoctorID, DoctorTypeID, ClientID.

--

Ken Snell
MS ACCESS MVP

"Access rookie" wrote in message
...
Hello,

I need some design advice.
I have a database that takes care of clients; it also has a table of
doctors.
I have a many to many relationship between the two obviously.

The challenge I have come across is that every client has a PCP (Primary
care provider.) Each client also has a neurologiest, therapist, dentist,
etc.
I have these specialties defined in the doctor table.
I can't think of any other way than to create a PCP, Neurologist, etc.
field
in the client table, then have these fields somehow pull the information
from
the doctor table.
Is this the only way to do this, or is there a better way? Doesn't doing
what I have mentioned above violate good DB design?
Puzzled,

Rookie.

Instead of using the DoctorTypeID as the specialty, then use it to indicate
PCP, etc. status. Same principle; just different use of the field.

For example, you could use a value of 1 for DoctorTypeID to indicate that
that record shows the DoctorID for the client's PCP.

--

Ken Snell
MS ACCESS MVP

"Access rookie" wrote in message
...
Hey Ken,

Thanks for your reply; I didn't understand it though.
The specialty for each doctor is indicated in the doctor table as one of
the
fields.
I do have a specialty table that the specialty field looks up its values
from.
If I add the SpecialtyID to the junction table, what does that do? I know
that when I pull the doctor information up, it will show up that the
doctor
is a cardiologist, etc. but that would work without adding the SpecialtyID
field to the junction table because the specialty is part of the doctor
profile.

Also, that doesn't take care of the PCP problem...because a client's
primary
care provider could be a cardiologist, neurologist, etc. As I have a many
to
many relationship, how do I specify who is the Primary Care Provider?

In a dark tunnel running out of oxygen,

Rookie.

"Ken Snell [MVP]" wrote:

Use the junction table that you already have, and add another field to it
to
show the "specialty" of the physician. Thus, your "unique" index for the
table would be three fields: DoctorID, DoctorTypeID, ClientID.

--

Ken Snell
MS ACCESS MVP

"Access rookie" wrote in message
...
Hello,

I need some design advice.
I have a database that takes care of clients; it also has a table of
doctors.
I have a many to many relationship between the two obviously.

The challenge I have come across is that every client has a PCP
(Primary
care provider.) Each client also has a neurologiest, therapist,
dentist,
etc.
I have these specialties defined in the doctor table.
I can't think of any other way than to create a PCP, Neurologist, etc.
field
in the client table, then have these fields somehow pull the
information
from
the doctor table.
Is this the only way to do this, or is there a better way? Doesn't
doing
what I have mentioned above violate good DB design?
Puzzled,

Rookie.

Hey Ken,

I got it...thanks so much...I have never used a table with three primary
keys before...I guess I'm slowly moving past the tip of the Access Iceberg!

Thanks again,

John.

"Ken Snell [MVP]" wrote:

Instead of using the DoctorTypeID as the specialty, then use it to indicate
PCP, etc. status. Same principle; just different use of the field.

For example, you could use a value of 1 for DoctorTypeID to indicate that
that record shows the DoctorID for the client's PCP.

--

Ken Snell
MS ACCESS MVP

"Access rookie" wrote in message
...
Hey Ken,

Thanks for your reply; I didn't understand it though.
The specialty for each doctor is indicated in the doctor table as one of
the
fields.
I do have a specialty table that the specialty field looks up its values
from.
If I add the SpecialtyID to the junction table, what does that do? I know
that when I pull the doctor information up, it will show up that the
doctor
is a cardiologist, etc. but that would work without adding the SpecialtyID
field to the junction table because the specialty is part of the doctor
profile.

Also, that doesn't take care of the PCP problem...because a client's
primary
care provider could be a cardiologist, neurologist, etc. As I have a many
to
many relationship, how do I specify who is the Primary Care Provider?

In a dark tunnel running out of oxygen,

Rookie.

"Ken Snell [MVP]" wrote:

Use the junction table that you already have, and add another field to it
to
show the "specialty" of the physician. Thus, your "unique" index for the
table would be three fields: DoctorID, DoctorTypeID, ClientID.

--

Ken Snell
MS ACCESS MVP

"Access rookie" wrote in message
...
Hello,

I need some design advice.
I have a database that takes care of clients; it also has a table of
doctors.
I have a many to many relationship between the two obviously.

The challenge I have come across is that every client has a PCP
(Primary
care provider.) Each client also has a neurologiest, therapist,
dentist,
etc.
I have these specialties defined in the doctor table.
I can't think of any other way than to create a PCP, Neurologist, etc.
field
in the client table, then have these fields somehow pull the
information
from
the doctor table.
Is this the only way to do this, or is there a better way? Doesn't
doing
what I have mentioned above violate good DB design?
Puzzled,

Rookie.

On 10 Feb 2005 06:10:07 -0800, "onedaywhen"
wrote:

Mike Sherrill wrote:
When your doctor gets sick . . .

... she is will also be a Patient entity.

Will she have one key in the table Patients, and a different key in
the table Doctors?

I was thinking separate
tables for Doctors, Specialisms and SpecialistDoctors respectively but
you seem to be suggesting a 'base' table for Person entities i.e. a
Doctor is a Person, a Patient is a Person.

Is a doctor a person? Is a patient a person? Is a nurse a person?
Of course.

Distinguish what each person does from what a person is. And
distinguish a relationship between people from what a person is.
("Patient" describes a relationship between two people; you can't have
a patient without a doctor or something like a doctor.)

I think this would only have
limited advantages in the data model described.

Think about it some more.

To your "Doctor" entities and "Patient" entities, add "Nurses",
"Pharmacists", "X-Ray technicians", and "Administrators". Let one
administrator also be a doctor, and another administrator also be a
nurse. Let them all be patients. Then start recording their
addresses and phone numbers.

--
Mike Sherrill
Information Management Systems

You actually are not using three primary keys here. A table can have only
one primary key. However, a primary key can consist of one or more fields.
So what you're really using is a composite primary key (the primary key is
composed of three fields) -- this design is called using a "natural primary
key".

Note that you could use an autonumber field as the primary key -- a
surrogate primary key -- and then you'd use the three fields as a composite
unique index, where the combination of the three fields must be unique, but
your primary key would be a separate field.

There are advantages and disadvantages to either approach. Which one you use
depends upon the current design and the possible changes to that design that
might be required in the future.

--

Ken Snell
MS ACCESS MVP


"Access rookie" wrote in message
...
Hey Ken,

I got it...thanks so much...I have never used a table with three primary
keys before...I guess I'm slowly moving past the tip of the Access
Iceberg!

Thanks again,

John.

"Ken Snell [MVP]" wrote:

Instead of using the DoctorTypeID as the specialty, then use it to
indicate
PCP, etc. status. Same principle; just different use of the field.

For example, you could use a value of 1 for DoctorTypeID to indicate that
that record shows the DoctorID for the client's PCP.

--

Ken Snell
MS ACCESS MVP

"Access rookie" wrote in message
...
Hey Ken,

Thanks for your reply; I didn't understand it though.
The specialty for each doctor is indicated in the doctor table as one
of
the
fields.
I do have a specialty table that the specialty field looks up its
values
from.
If I add the SpecialtyID to the junction table, what does that do? I
know
that when I pull the doctor information up, it will show up that the
doctor
is a cardiologist, etc. but that would work without adding the
SpecialtyID
field to the junction table because the specialty is part of the doctor
profile.

Also, that doesn't take care of the PCP problem...because a client's
primary
care provider could be a cardiologist, neurologist, etc. As I have a
many
to
many relationship, how do I specify who is the Primary Care Provider?

In a dark tunnel running out of oxygen,

Rookie.

"Ken Snell [MVP]" wrote:

Use the junction table that you already have, and add another field to
it
to
show the "specialty" of the physician. Thus, your "unique" index for
the
table would be three fields: DoctorID, DoctorTypeID, ClientID.

--

Ken Snell
MS ACCESS MVP

"Access rookie" wrote in
message
...
Hello,

I need some design advice.
I have a database that takes care of clients; it also has a table of
doctors.
I have a many to many relationship between the two obviously.

The challenge I have come across is that every client has a PCP
(Primary
care provider.) Each client also has a neurologiest, therapist,
dentist,
etc.
I have these specialties defined in the doctor table.
I can't think of any other way than to create a PCP, Neurologist,
etc.
field
in the client table, then have these fields somehow pull the
information
from
the doctor table.
Is this the only way to do this, or is there a better way? Doesn't
doing
what I have mentioned above violate good DB design?
Puzzled,

Rookie.

Mike Sherrill wrote:
I think this would only have
limited advantages in the data model described.

Think about it some more.

To your "Doctor" entities and "Patient" entities, add "Nurses",
"Pharmacists", "X-Ray technicians", and "Administrators". Let one
administrator also be a doctor, and another administrator also be a
nurse. Let them all be patients.

In the healthcare scenario, I don't foresee much of an intersection
between Employees and Customers i.e. what percentage of patients are
employees of the healthcare provider? how much information is common
to both at the base level? Employees and Customers are modelled as
separate entities in the Northwind example database supplied with MS
Access and I would expect a typical newsgroup post to aspire to a
similar level of abstraction. The approach to which you elude is more
suited to OOP in the front end application than the relational model.
Doable but what is gained, I wonder.

Then start recording their
addresses and phone numbers.

Are a doctor's address and a patient's Address the same entity? Yes in
the physical sense but perhaps not in the data model. In my country, I
suspect Data Protection legislation (i.e. right of access to
information rather than database security) would require a greater
degree of physical separation.

You approach is interesting and thorough, though. Please post your
basic DDL so we can see how if measures up.

Jamie.

--