RE: [GENERAL] Database Best Practices

Yes, Oliver is right.

We don't even have city here in Hong Kong. You know Hong Kong is now a city
of China, but Hong Kong also has its own Country Code.

I really hate to fill in a web form which force me to enter n/a into the Zip
Code, City etc...

R.

Terence Chan
Cetec Ltd.
Hong Kong


-----Original Message-----
From:    owner-pgsql-general@postgreSQL.org
[mailto:owner-pgsql-general@postgreSQL.org] On Behalf Of Oliver Elphick
Sent:    Tuesday, July 20, 1999 2:27 PM
To:    Gary Hoffman
Cc:    pgsql-general@postgreSQL.org
Subject:    Re: [GENERAL] Database Best Practices

Gary Hoffman wrote:
  >To all the database experts out there:
  >
  >As I begin to design my PostGreSQL tables, I'm looking for some
  >over-the-shoulder advice on the order of "best practices"
recommendations.
  >Has anyone gathered their experience together into such a book or
website?
  >
  >For example, I'd like to know, in designing a name-and-address table,
  >  - should a title field be provided? (Mr., Dr., etc.)
  >  - how about a suffix (Jr., III, etc.)
  >  - how many address lines should I have for a home address?
  >  - should all the fields be type 'text'? Should Zip be 'text' or 'int4'?
  >  - what schema handles Zip-plus-four best (including the blasted
hyphen)?
  >  - what is the best database schema for a universal, international
  >address table that includes individuals and companies?

If you want a database that can take international addresses, forget about
the rigid scheme of US addresses; most countries are much more free-form.

British postcodes are not numeric (mine is "PO30 1XP"), so a zip field that
is typed int4 would not accommodate British codes at all.

A full postal address might look like this:

  Mr A.B. Cousins                      name
  Personnel Manager                    position
  Contorted Widgets Ltd                company
  Contortion House                     building name
  5 Park Road                          street address
  Acme Industrial Estate               block
  Little Wittering                     village
  Trumpington                          post town
  Hexhamshire                          county
  TR12 5SQ                             post code
  UK                                   country

That's extreme, but I've seen some nearly as bad.  The Post Office
don't need all of that, but it helps someone who is driving around and
trying to find the place.

Putting them together is more of a problem, since the order of fields
differs between countries.  I believe German addresses go from larger
area to smaller rather than smaller to larger, for example.


Here's a sample schema that differentiates between individuals and
organisations.  It regards addresses as separate from people, because a
person can have more than one address, and people can share the same
address.

======================== schema ===================================
-- refint functions (from postgresql-contrib package).  These are
-- needed until foreign keys are implemented in PostgreSQL
\i /usr/lib/postgresql/modules/refint.sql

-------------------------- country --------------------------------
create table country
(
        id              char(2)         primary key
                                        check (id ~ '[A-Z]{2}'),
        name            text            not null,
        region          text,
        telcode         text
)
;

-- foreign key triggers
create trigger country_fref
        before DELETE or UPDATE on country
        for each row execute procedure
        check_foreign_key(1, 'restrict', 'id',
                'address', 'country'
                );

-- country contains the full list of ISO country codes, and the region of
-- the world where those countries are to be found.
-- id      = ISO country code
-- name    = country name
-- region  = continent or area where the country is
-- telcode = telephone code for the country

-------------------------- address --------------------------------
create table address
(
        id              int             primary key,
        house           text,
        number          text,
        street          text,
        village         text,
        town            text            not null,
        county          text,
        postcode        char(9),
        country         char(2)         references country (id),
        phone1_country  text,
        phone1_area     text,
        phone1_number   text,
        phone1_ext      text,
        phone2_country  text,
        phone2_area     text,
        phone2_number   text,
        phone2_ext      text,
        fax_country     text,
        fax_area        text,
        fax_number      text,
        fax_ext         text,
        comment         text
)
;

-- Use SPI triggers until foreign keys are implemented
create trigger address_pkref
create trigger address_pkref
        before INSERT or UPDATE on address
        for each row execute procedure
        check_primary_key('country', 'country', 'id');

create trigger address_fref1
        before DELETE or UPDATE on address
        for each row execute procedure
        check_foreign_key(1, 'restrict', 'id',
                'person_address', 'address');

create trigger address_fref2
        before DELETE or UPDATE on address
        for each row execute procedure
        check_foreign_key(3, 'setnull', 'id',
                'individual', 'address',
                'organisation', 'address',
                'person', 'address'
                );

-- id       = unique identifier of this address
-- house    = house name
-- number   = house number
-- street   = street address (excluding house name or number)
-- village  = second part of street address, if any
-- town     = city or post town
-- county   = county (may sometimes be blank)
-- postcode = post or zip code
-- country  = country code
-- phone1_country   = primary telephone country code
-- phone1_area      = primary telephone area code
-- phone1_number    = primary telephone number
-- phone1_ext       = primary telephone extension
-- phone2_country   = secondary telephone country code
-- phone2_area      = secondary telephone area code
-- phone2_number    = secondary telephone number
-- phone2_ext       = secondary telephone extension
-- fax_country   = fax telephone country code
-- fax_area      = fax telephone area code
-- fax_number    = fax telephone number
-- fax_ext       = fax telephone extension
-- comment       = free text

-- A person (of any kind) can have one or more addresses.  All fields here
-- are associated with a particular physical location


-------------------------- person --------------------------------
create table person
(
        id              char(10)        primary key,
        name            text            not null,
        address         int             references address (id),
        salutation      text            default 'Dear Sir',
        envelope        text,
        email           text,
        www             text,
        comment         text
)
;

-- Use SPI triggers until foreign keys are implemented


create trigger person_pkref
        before INSERT or UPDATE on person
        for each row execute procedure
        check_primary_key('address', 'address', 'id');

create trigger person_fref_x
        before DELETE or UPDATE on person
        for each row execute procedure
        check_foreign_key(1, 'restrict', 'id',
                'person_address', 'person'
                );


-- Top-level class that describes persons (living or otherwise)
-- id         = identifier
-- address    = id of the primary address of this person
-- salutation = how to address this person in the start of a letter
--                  (e.g.: "Dear Fred")
-- envelope   = how to address this person on an envelope
--                  (e.g.: "Mr F.G. Bloggs")
-- email      = email address
-- www        = Web URL
-- comment       = free text

-- Dependencies: address


-------------------------- individual --------------------------------
create function name(bpchar, bpchar, bpchar) returns bpchar
        as '/usr1/proj/bray/sql/funcs.so' language 'c';

create table individual
(
        gender          char(1)         check (gender = 'M' or gender = 'F'
or gender is null),
        born            date        check ((born >= '1 Jan 1880' and born <=
'today') or born is null),
        surname         text,
        forenames       text,
        title           text,
        old_surname     text,
        mobile          text,
        ni_no           text

        constraint is_named check (not (surname isnull and forenames
isnull))
)
        inherits (person)
;

create trigger individual_pkref
        before INSERT or UPDATE on individual
        for each row execute procedure
        check_primary_key('address', 'address', 'id');

create trigger individual_fref_x
        before DELETE or UPDATE on individual
        for each row execute procedure
        check_foreign_key(1, 'restrict', 'id',
                'person_address', 'person',
                'organisation', 'contact'
                );

-- Table of living individuals (as opposed to firms or legal persons)
-- gender    = 'M' or 'F'
-- born      = date of birth
-- surname   = surname
-- forenames = forenames
-- title     = Mr, Mrs, etc
-- old_surname   = maiden name, etc.

-- Inheritance: person



-------------------------- organisation --------------------------------
create table organisation
(
        structure      char(1)         check (structure='L' or structure='C'
or structure='U' or structure='O'),
    contact        char(10)        references individual (id),
    old_name       text
)
        inherits (person)
;


create trigger organisation_pkref
        before INSERT or UPDATE on organisation
        for each row execute procedure
        check_primary_key('address', 'address', 'id');

create trigger organisation_pkref_c
        before INSERT or UPDATE on organisation
        for each row execute procedure
        check_primary_key('contact', 'individual', 'id');

create trigger organisation_fref_x
        before DELETE or UPDATE on organisation
        for each row execute procedure
        check_foreign_key(1, 'restrict', 'id',
                'person_address', 'person'
                );



-- Defines persons that are not individuals
-- contact      = id of the primary person to be contacted when dealing
--                with this organisation
-- structure    = L(imited), C(orporation), U(nincorporated) or O(ther)
-- old_name     = previous organisation name (if any)

-- Inheritance: person
-- Dependencies: individual, address

---------------- person_address relation ----------------------
create table person_address
(
        person          char(10)        not null
                                         -- references person*,
                                         references person(id),
        address         int             not null
                                         references address(id),
        function        text            not null,

        primary key (person, address, function)
)
;

-- refint triggers
create trigger person_address_pkref_1
        before INSERT or UPDATE on person_address
        for each row execute procedure
        check_primary_key('person', 'person*', 'id');

create trigger person_address_pkref_2
        before INSERT or UPDATE on person_address
        for each row execute procedure
        check_primary_key('address', 'address', 'id');

-- Relation of people to addresses (many-to-many relationship)
-- person   =  id of a person
-- address  =  id of an address
-- function =  use of address (e.g. accounts, deliveries...)

======================== schema ===================================
--
      Vote against SPAM: http://www.politik-digital.de/spam/
                 ========================================
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Behold, what manner of love the Father hath bestowed
      upon us, that we should be called the sons of God..."
                                I John 3:1