Re: Design problem and suggestions...

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Jeff Boyce (JeffBoyce_IF_at_msn.com-DISCARD_HYPHEN_TO_END)
Date: 02/14/05 

Date: Mon, 14 Feb 2005 04:34:40 -0800

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" <jamiecollins@xsmail.com> wrote in message
news:1108371911.126800.21880@z14g2000cwz.googlegroups.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.
>
> --
>

Relevant Pages

  • Re: move data from list box or combo box to another list
    ... employees is because each employee gets paid commission on the service they ... The customer and dates are ... Lets address your problem with your report which seems to be your main ... Oh yeah and also you should/could have a filter in your query so that your ...
    (microsoft.public.access.forms)
  • Re: Design problem and suggestions...
    ... ('Homo sapiens sapien'), species_type, genus_type ... ... An employee is not normally barred from being a customer; ... please post details of your proposed schema e.g. the new ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Simple question
    ... I have customer class, employee class. ... In that case the semantics of address may be the same. ... This is fine as long as a Person is always either a Customer or an Employee in the problem context. ...
    (comp.object)
  • Re: Databinding - Best Practice (object-oriented)
    ... > Because it does not expose individual objects like Employee or Customer, ... OO is not for data management. ...
    (microsoft.public.dotnet.framework.windowsforms.databinding)
  • Re: Databinding - Best Practice (object-oriented)
    ... > Because it does not expose individual objects like Employee or Customer, ... OO is not for data management. ...
    (microsoft.public.dotnet.framework.windowsforms)