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(dot)Elphick(at)lfix(dot)co(dot)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