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

On Wed, 14 Jul 2004 22:30:26 -0700 Steve Crawford wrote:
> [arrays are bad]
> I know you are under "temporal pressure" but this is truely a case of
> "pay me now or pay me later".

That's why I'm asking for advice on this list. It's not that I have to
have the database today, but I don't want to spend days reorganizing it
over and over again. For now, I'm aiming at the perfect database for our
current needs. Planing for all possible future needs, OTOH, is is a bad
idea, IMHO. It would probably complicate things considerably and in the
end one has to reorganize anyways.

> By way of example, say you want to write a query showing all contacts
> for a particular location. With an array of 0..n location elements
> that is very hard to do.

Well, it is possible even for me as a newbie, but it is probably not as
elegant and efficient as otherwise. Here's an example that I just built:

SELECT * FROM contacts WHERE (SELECT location_id FROM locations WHERE name = 'Hannover') = ANY(locations)

> You may want to check out "Database Design for Mere Mortals".

Hm, I'm somewhat sceptical that a book on that topic needs to have 672
pages. I guess that *basic* database design principles and maybe also
basic common naming conventions can be summarized on a dozen or so
pages. If database design proves to be of further importance for me,
I'll first investigate what other books there are.

> [An example for a contacts scheme]

Thanks a lot for your recommendations. I just build a new draft (see
below). Note that I removed the inheritance scheme used in my last draft
because I first like to get a feeling for basic database design
features. Also note that in the current design an organization is an
umbrella term that also includes single persons. I did that because
persons and companies, etc. share many properties.

Felix

The new draft:

"Major" tables:
    ORGANIZATIONS:
    contact_id, offers, demands, description, type (person|non-person),
    type_id (a person_id or a non-person_id)

    PERSONS:
    person_id, surname, given_names, pseudonyms, prefix, suffix, sex (M|F)

    NON-PERSONS:
    non-person_id, name, type (company|non-profit|...)

    EVENTS:
    event_id, name, date, address (an address_id), description

"Minor" tables:
    ADDRESSES:
    address_id, street1, street2, street3, city, postal_code,
    country (iso3166), subcountry (iso3166)

    EMAIL_ADDS:
    email_id, address, send_info (yes|no), put_in_ml (yes|no)

    PHONE_ADDS:
    phone_id, number, type (tel|fax|mob|etc.), address (an address_id|NULL)

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

Link tables:
    ORGANIZATION_ADDRESSES:
    organization, address

    ORGANIZATION_EMAIL_ADDS:
    organization, email

    ORGANIZATION_PHONE_ADDS:
    organization, phone

    ORGANIZATION_URLS:
    organization, url

    EVENT_ORGANIZORS:
    event, organization

    PERSON_NON-PERSONS:
    person, non-person, type (CEO|CTO|Supporter|...)

    NON-PERSON_NON-PERSONS:
    non-person, non-person, type (subsidiary|member|...)