Design problem and suggestions...

[onedaywhen]

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

Thanks Tim, You post neatly puts forward the case point I was trying to
make but you put it much better than I ever could.

Jamie.

--

 

[onedaywhen]

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.

Try me.

Jamie.

--

 

[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?