Design problem and suggestions...

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

--
Good luck

Jeff Boyce
Access MVP

"onedaywhen" wrote in message
oups.com...
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.

--

Jeff Boyce wrote:
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 ...?


Say this healthcare data model encompassed medial trials which are
inevitably conducted on animals (I wish it wasn't so). Our Persons
table now becomes a Subspecies table with columns for subspecies_type
('Homo sapiens sapien'), species_type ('Homo sapien'), genus_type ...
What, did I go too far?

Let's take the simpler example of the database with Customers and
Employees tables and assume all customers and employees are people
(although in reality they could be other legal entities such as
companies, which would require further subclassing tables).

An employee is not normally barred from being a customer; in fact, the
relationship is often encouraged via the offer of a staff discount. So
there appears to be scope for a Persons table to hold the data common
the both employee and customer.

The example of their address has been suggested; both customer and
employee entities are people and need to have an address. But let's
look closer: a customer needs a billing address and a delivery address
whereas the employee address needs to be a home address. So they are
not exactly the same things. An employee's address is an attribute of
the employee, therefore should normally be found in the employee table.


What would you use to key the Persons table? Some countries impose a
legal requirement to key employee data by social security number but
this information is not usually captured for customers. You can't key
your Persons table on customer_reference because an employee may choose
not to become a customer. (Before responding, remember an autonumber is
a tie-breaker, not a key in itself.)

Let me come at this from a practical point of view. What is the
advantage of modelling the few common entities in a base table? I think
a column in the Customer table to flag an employee would suffice.

However, as I said before, I find this interesting. I just I don't
see any detail about how this would be implemented. So, using Northwind
as an example, please post details of your proposed schema e.g. the new
Persons table and any changes to the existing Employees and Customer
tables.

Jamie.

--

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

"onedaywhen" wrote in message
ups.com...

Jeff Boyce wrote:
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 ...?


Say this healthcare data model encompassed medial trials which are
inevitably conducted on animals (I wish it wasn't so). Our Persons
table now becomes a Subspecies table with columns for subspecies_type
('Homo sapiens sapien'), species_type ('Homo sapien'), genus_type ...
What, did I go too far?

Let's take the simpler example of the database with Customers and
Employees tables and assume all customers and employees are people
(although in reality they could be other legal entities such as
companies, which would require further subclassing tables).

An employee is not normally barred from being a customer; in fact, the
relationship is often encouraged via the offer of a staff discount. So
there appears to be scope for a Persons table to hold the data common
the both employee and customer.

The example of their address has been suggested; both customer and
employee entities are people and need to have an address. But let's
look closer: a customer needs a billing address and a delivery address
whereas the employee address needs to be a home address. So they are
not exactly the same things. An employee's address is an attribute of
the employee, therefore should normally be found in the employee table.


What would you use to key the Persons table? Some countries impose a
legal requirement to key employee data by social security number but
this information is not usually captured for customers. You can't key
your Persons table on customer_reference because an employee may choose
not to become a customer. (Before responding, remember an autonumber is
a tie-breaker, not a key in itself.)

Let me come at this from a practical point of view. What is the
advantage of modelling the few common entities in a base table? I think
a column in the Customer table to flag an employee would suffice.

However, as I said before, I find this interesting. I just I don't
see any detail about how this would be implemented. So, using Northwind
as an example, please post details of your proposed schema e.g. the new
Persons table and any changes to the existing Employees and Customer
tables.

Jamie.

--

Jeff Boyce wrote:
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.

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. And how many of your clients' databases have you
built in this way g?

Jamie.

--

See comments in-line below...

"onedaywhen" wrote in message
oups.com...

Jeff Boyce wrote:
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.

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



Jamie.

--

Jeff Boyce wrote:

It only takes one satisfied customer to make it necessary

Are you saying one size fits all ...?

the flexibility their situation demanded

.... or that this kind of model is only justified in some situations?
(I'm going with the latter.)

Jamie.

--

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.

--
Good luck!

Jeff Boyce
Access MVP

"onedaywhen" wrote in message
oups.com...
Jeff Boyce wrote:

It only takes one satisfied customer to make it necessary

Are you saying one size fits all ...?

the flexibility their situation demanded

... or that this kind of model is only justified in some situations?
(I'm going with the latter.)

Jamie.

--

On 11 Feb 2005 02:54:03 -0800, "onedaywhen"
wrote:

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.

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.

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
Information Management Systems

On 14 Feb 2005 09:34:25 -0800, "onedaywhen"
wrote:

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

--
Mike Sherrill
Information Management Systems

Mike Sherrill wrote in
news
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