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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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