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|...)