Skip site navigation (1) Skip section navigation (2)

Re: Critique needed for contact-DB draft

From: "Felix E(dot) Klee" <felix(dot)klee(at)inka(dot)de>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: scrawford(at)pinpointresearch(dot)com, pgsql-novice(at)postgresql(dot)org
Subject: Re: Critique needed for contact-DB draft
Date: 2004-07-15 19:34:30
Message-ID: 20040715213430.724cda96.felix.klee@inka.de (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Oliver FrommeDate: 2004-07-15 20:45:28
Subject: Re: Extended query: prepared statements list?
Previous:From: Bruno Wolff IIIDate: 2004-07-15 18:52:38
Subject: Re: Critique needed for contact-DB draft

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group