Re: Critique needed for contact-DB draft - Mailing list pgsql-novice
From | Felix E. Klee |
---|---|
Subject | Re: Critique needed for contact-DB draft |
Date | |
Msg-id | 20040715162554.31159c4a.felix.klee@inka.de Whole thread Raw |
In response to | Re: Critique needed for contact-DB draft (Steve Crawford <scrawford@pinpointresearch.com>) |
Responses |
Re: Critique needed for contact-DB draft
Re: Critique needed for contact-DB draft |
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|...)
pgsql-novice by date: