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