Graham Mandeno wrote:
duplicating the sub-type in the sub-tables and using
a two-field relationship is simple, elegant and brilliant. You say this is
"the standard approach" but I can't believe that I have never come across it
before, and it is so simple that I'm ashamed I never thought of it myself!

One question though - is there any reason not to put the "common" fields in
the superclass table?

Yes, the shared fields would be best in the superclass table. As I'm
sure you are aware, there is distinction between a shared attribute
(inheritance) and two attributes sharing a name (coincidence) e.g.
patient may have a legal name attribute and a medication may have a
legal name attribute but they do have a shared superclass.

why not combine the Contacts and
LegalPersons tables?

It's probably a 1:m relationship e.g. an organization may have more
than one contact. Also, an organization may have a contact that is an
individual who has a collection of contacts in the context of being a
contact for that organization as well as contacts in their own right
e.g. work, home (and they may have more than one home), etc. There is
certainly potential for the contacts to be a structu each contact
type - postal, email, web, telephone, fax, etc - should have probably
be in a table of their own, with perhaps shared super classes (e.g.
telephone and fax)... this can quickly get out of hand!

One issue here is whether all contact types share a superclass; if so,
what would the identifier be? There is a similar issue with the
proposed LegalPerson superclass: is there a real life identifier shared
by organizations and individuals?

Jamie.

--