Re: Critique needed for contact-DB draft

On Thu, 15 Jul 2004 13:52:38 -0500 Bruno Wolff III wrote:
> > > "Major" tables:
> > > ORGANIZATIONS:
> > > contact_id, offers, demands, description, type (person|non-person),
> > > type_id (a person_id or a non-person_id)
> >
> > contact_id should be substituted by organization_id.
>
> That doesn't sound right.

It's only a cosmetic change. I used to use to name that table
CONTACTS. But after renaming it to ORGANIZATIONS, I forgot to rename the
field contact_id into organization_id in order to keep notation
consistent.

> This table looks a lot like a contact table.
> Unless there is exactly one contact per organzation you probably want
> two tables.

An organization can be used as a contact (but it has other uses as well,
therefore the name change). It *is* either a person (a "one man
organization") or a non-person (a company, a non-profit organization,
etc.). Actual contact informations (addresses, email addresses, etc.)
are stored in different tables.

Now, I wonder what is the best scheme to create an "is a" relation:

Scheme A:
ORGANIZATIONS: organization_id, ...
PERSONS: person_id, organization_id, ...
NON-PERSONS: non-person_id, organization_id, ...
Scheme B:
ORGANIZATIONS: organization_id, type (person|non-person), type_id
(a person_id or a non-person_id)
PERSONS: person_id, ...
NON-PERSONS: non-person_id, ...
Scheme C:
ORGANIZATIONS: organization_id, person_id (or NULL, if it is a
non-person), non-person_id (or NULL, if it is a person), ...
PERSONS: person_id, ...
NON-PERSONS: non-person_id, ...
Scheme D:
ORGANIZATIONS: organization_id, ...
PERSONS (inherits from ORGANIZATIONS): person_id, ...
NON-PERSONS (inherits from ORGANIZATIONS): non-person_id, ...
Scheme E:
<something else>

Actually, the person_id and non-person_id field may be superfluous for
most of the schemes.

Sorry, for bothering you with this trivial stuff. I think I should
really get some manual to teach me the basics (but which one?).

Felix