Design problem and suggestions...

[Access rookie]

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.

 

[Ken Snell [MVP]]

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.

 

[Mike Sherrill]

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 . . .

 

[Access rookie]

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]]

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.

 

[Access 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.

 

[Mike Sherrill]

... 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.

 

[Ken Snell [MVP]]

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>

 

[Jeff Boyce]

I suspect that the reason for considering that all those roles mentioned as
roles of persons is that any system would have to handle the exception. You
mentioned that it would be rare for an employee (?nurse) to also be a
patient.

I disagree, and point out that it only takes one to break a system that
doesn't handle that. How many nurses (or administrators, or doctors, or
x-ray techs) have a primary physician, who they see for wellness, for their
blood pressure or diabetes, or following a skiing accident, or ...?

 

[Jeff Boyce]

A solution to the "person-as-customer-with-one-address" and
"person-as-employee-with-another-address" is to avoid connecting an address
directly to a person-type table.

A "context" table can hold person & role. An address table can hold
addresses. A "context-address" table would hold all the valid addresses (if
more than one) for any given "context". The "Address Type" (billing,
shipping, ...) could be included in this junction table.

This way, a person could show up multiple times in different contexts, and
each context could have multiple addresses.

Jeff Boyce
<Access MVP>

 

[Jeff Boyce]

See comments in-line below...

So now that you have split all these attributes and mixed them together
in new tables, what benefit do you now gain?

The obvious advantage is that one person can hold many roles, and each role
can have many addresses, phone numbers, email addresses, ... (i.e.,
"contact" information).

The disadvantage is that
you have to create a series of joins to get the simple 'employee
address' attribute.

Your assumption being that there is, in the real world being modeled, a
"simple, single 'employee address' attribute" -- not true in the situation I
was asked to model.

And how many of your clients' databases have you
built in this way <g>?

The customer for whom I designed this model and system was unable to find a
commercial product that had the flexibility their situation demanded. That
one customer has been relying on the system (and model) for over 5 years
now, without finding a web-based or commercial product that they can use to
replace it. It only takes one satisfied customer to make it necessary!

This is a little like one of your other threads asking 'when would an
employee ever be a patient?' -- how 'bout anyone who works for an HMO and
uses that HMO for their medical insurance coverage?!

 

[Jeff Boyce]

Jamie

What's with the "what about A...?" (what about B? ... what about C? ....)
Is this your learning style?

You asked why? I provided an answer -- I had a customer who asked for it.

You are the one suggesting one size fits all -- I don't believe that, nor
did I write that.

The notion of a model is that it represents something (*not everything*) in
the real world. I jumped in on this thread when you asked "what percentage
of patients are employees...?" The gist of my example is that there are
real world situations which call for customized models, rather than the
simple "one person, one address" model.

 

[Mike Sherrill]

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?

Here's another way to look at it. Every patient is a person, and
every employee is a person. So is every doctor. That gives you 100%
intersection between patients and employees, between patients and
doctors, and between employees and doctors.

how much information is common
to both at the base level?

All of it--all information that applies to persons. For example,
mailing addresses and home phone numbers. Height and weight.
Driver's license numbers. SSANs.

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.

Northwind doesn't demonstrate good database design; it demonstrates
some of Access's features.

Employees and customers are concrete; "person" is also concrete.
"Parties" is an abstraction encompassing persons and organizations,
but I get ahead of myself. (Think "Some customers are persons, and
other customers are businesses".)

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.

No, it simply reflects the reality that every person has zero or more
mailing addresses, regardless of their profession or health. That
reality is independent of a programming methodology. It's even
independent of computers.

An alternative conceptual model can be expressed as

1) Every doctor has zero or more mailing addresses.
2) Every patient has zero or more mailing addresses.
3) Every employee has zero or more mailing addresses.
4) Doctors, patients, and employees have nothing in common.

If you're doing database design for a living, don't present that to
your clients.

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.

At the conceptual level (that is, in the real world), a doctor and a
patient are both persons; they both have zero or more mailing
addresses; their mailing address are drawn from the same domain; and
their mailing addresses have the same semantics (that is, they mean
the same thing).

It doesn't often get easier to understand than that.

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.

I've read a lot of statutes. I've seen statutes that constrain who is
allowed to see what. I've never seen a statute that says "You can't
store doctor's addresses in the same table as patient's addresses."

I'd be a little surprised to find legislation that said "Accounts
receivable can send bills to patients (see their addresses), but if
the patient is an employee, then accounts receivable can't send them a
bill (see their address)". But even legislation that stupid can be
accommodated with a simple view.

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

If you can't understand "Every doctor is a person, and every person
has zero or more mailing addresses", the odds you'll understand DDL
are slim. Maybe later.

 

[Mike Sherrill]

So now that you have split all these attributes and mixed them together
in new tables, what benefit do you now gain? The disadvantage is that
you have to create a series of joins to get the simple 'employee
address' attribute.

No, in this case the number of joins is determined by the use of
surrogate keys, not by modeling persons as persons and addresses as
addresses.

Done correctly, building a physical model (database) by adding
surrogate keys to a logical model is strictly a space/time tradeoff. A
surrogate key usually reduces the space required for storage, and it
usually increases the time to retrieve data, because you have to do a
join (or several joins) to get the data you want.

Using natural keys, I can fetch employee addresses with just one join.
This isn't true for *every* use of natural keys--if you need a non-key
attribute from a referenced table, you still need a join to get it.
But it is true of addresses.

And how many of your clients' databases have you
built in this way <g>?

All of them since about 1990. Fabian Pascal has a chapter on
supertype and subtype modeling in his book _Practical Issues in
Database Management_.

 

[Tim Ferguson]

At the conceptual level (that is, in the real world), a doctor and a
patient are both persons; they both have zero or more mailing
addresses; their mailing address are drawn from the same domain; and
their mailing addresses have the same semantics (that is, they mean
the same thing).

The thing that has bothered me about this thread is that it seems to lack
any basis in the real world. I just cannot imagine the business need that
requires doctors and their patients to be considered the same entities.
And this particular paragraph sums up why it's wrong:

A doctor has a clinic slots and qualifications and hire dates and
CPD requirements and appraisal needs;

A patient has diagnoses and treatment plans and heights and weights
dietary preferences and religions;

What they have in common is trivial. The names are different -- my
mum, in common with many women, continued to use her maiden name at
work, and used her married name for everything else, including when
she was receiving care as a patient. Please don't tell me you are
suggesting a one-to-many relationship for last names too! The mailing
addresses are not drawn from the same domain: professional addresses
have DepartmentName and JobTitle and ExtensionNumber and PagerNumber,
while home addresses have streets and cities.

I agree that DB design is about semantics -- but frankly
ProfessionalAddresses and HomeAddresses are completely different things,
in any sensible kind of business need analysis. It's childish over-
enthusiasm to force them into a complex supertype-subtype arrangement
when it's unlikely to bring any benefit to the system except showing what
big cojones the designer has. KISS!

Just a thought...


Tim F

 

[Mike Sherrill]

The thing that has bothered me about this thread is that it seems to lack
any basis in the real world.

That's funny. I'm pretty sure that I've been talking *only* about the
real world, and not about tables and databases and DDL.

I just cannot imagine the business need that
requires doctors and their patients to be considered the same entities.

In general, I find it pretty easy to imagine things. In this specific
case, it was easy for me, because I already knew that doctors and
patients were people.

And this particular paragraph sums up why it's wrong:

A doctor has a clinic slots and qualifications and hire dates and
CPD requirements and appraisal needs;

A patient has diagnoses and treatment plans and heights and weights
dietary preferences and religions;

What they have in common is trivial.

I think the problem is that, while you're quite good at identifying
how doctors and patients are different, you're ignoring how they're
alike.

Doctors and patients have, literally, hundreds of things in common.
Doctors have names; patients have names. Doctors have mailing
addresses, patients have mailing addresses. Doctors have phone
numbers; patients have phone numbers.

And doctors can be patients; patients can be doctors.

Doctors and patients have these things in common because doctors are
people, and patients are people. People have names; people have
mailing addresses; people have phone numbers. Some people are doctors
by profession. Some people see a doctor for treatment.

The names are different -- my
mum, in common with many women, continued to use her maiden name at
work, and used her married name for everything else, including when
she was receiving care as a patient. Please don't tell me you are
suggesting a one-to-many relationship for last names too!

Every doctor has a name. Every patient has a name. (And every
patient has a doctor, too, but not every doctor sees patients.) I'm
not sure what you mean when you say "the names are different".

If a person uses more than one name, and that's important to you, then
model it. In the US, we usually call them either aliases or akas (for
"also know as"). How you implement them depends on the conceptual
model. I've worked on legal systems that used two different
conceptual models. But I don't think they're relevant to this topic.

The mailing
addresses are not drawn from the same domain:

A simple test is to try to mail something to a mailing address. Does
the post office try to deliver it? If they do, the mailing address is

professional addresses
have DepartmentName and JobTitle and ExtensionNumber and PagerNumber,
while home addresses have streets and cities.

Can I send mail to professional addresses and to home addresses? I'm
pretty sure I can.

I agree that DB design is about semantics -- but frankly
ProfessionalAddresses and HomeAddresses are completely different things,
in any sensible kind of business need analysis.

Yes, but that has nothing to do with mailing addresses, which are yet
another thing.

And while we're here, "professional addresses" and "home addresses"
are *not* completely different things. I'm pretty sure I can send
mail through the USPS to either a home address or a professional
address, and I don't have to know which is a home address and which is
a professional address to do that.

It's childish over-
enthusiasm to force them into a complex supertype-subtype arrangement
when it's unlikely to bring any benefit to the system except showing what
big cojones the designer has. KISS!

Who said anything about supertypes?