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

Critique needed for contact-DB draft

From: "Felix E(dot) Klee" <felix(dot)klee(at)inka(dot)de>
To: pgsql-novice(at)postgresql(dot)org
Subject: Critique needed for contact-DB draft
Date: 2004-07-15 00:27:37
Message-ID: 20040715022737.20446b36.felix.klee@inka.de (view raw or flat)
Thread:
Lists: 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|...)

Responses

pgsql-novice by date

Next:From: Mark DexterDate: 2004-07-15 01:21:57
Subject: Calling Functions and Stored Procedures
Previous:From: Bruce MomjianDate: 2004-07-14 20:06:47
Subject: Re: resend: Trouble with pg_dump in 7.3.4

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