Critique needed for contact-DB draft - Mailing list pgsql-novice

From Felix E. Klee
Subject Critique needed for contact-DB draft
Date
Msg-id 20040715022737.20446b36.felix.klee@inka.de
Whole thread Raw
Responses Re: Critique needed for contact-DB draft  (Bruno Wolff III <bruno@wolff.to>)
Re: Critique needed for contact-DB draft  (Steve Crawford <scrawford@pinpointresearch.com>)
List pgsql-novice
Hi,

I'm a bit under temporal pressure and I don't have much experience with
data base design (only did some stuff with DBase a couple of years ago)
and am a total newbie to PostgreSQL. That's why I bluntly ask you to
criticize the design attached below. The database is for storing
contacts to persons and organizations, basic relations between them (who
is a member of whom), and events organized by them. More tables/fields
may be added later, but that's pretty much what I need right now.

I'm especially interested on your take of my use of arrays. They avoid
the need for additional tables, but maybe they are not good,
nevertheless?

BTW, what I'm really missing is as a newbie is a way to define new data
types as "structures" as known from e.g. the C programming language (or
is this supported by PostgreSQL?). That would IMHO be a more natural
concept for storing data (for example, then I could create an array of
addresses for each contact).

Felix

The draft:

"Major" tables:

    CONTACTS (not used "standalone"):
    contact_id, locations (location_id[]), phones (phone_id[]), emails
    (email_id[]), urls (url_id[]), description

        PERSONS (inherited from CONTACTS):
        surname, given_names, pseudonyms, prefix, suffix

        ORGANIZATIONS (inherited from CONTACTS):
        type, name

    CONTACT_ORGANIZATION_RELATIONS:
    member (contact_id), umbrella_organization (contact_id of organization),
    position_in_organization (position_id)

    EVENTS:
    event_id, name, date, location (location_id), description, organizers
    (contact_id[])

"Minor" tables:

    LOCATION:
    location_id, street1, street2, street3, city, postal_code,
    country (iso3166), subcountry (iso3166)

    PHONES:
    phone_id, number, type (tel|fax|mob|etc.), location (location_id|NULL)

    EMAILS:
    email_id, email_address, send_info (yes|no), put_in_ml (yes|no)

    URLS:
    url_id, url, put_in_links_list (yes|no)

    POSITIONS:
    position_id, position (CEO|CTO|subsidiary|...)

pgsql-novice by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: resend: Trouble with pg_dump in 7.3.4
Next
From: Bruno Wolff III
Date:
Subject: Re: Critique needed for contact-DB draft