In Step 1, we made the case that the addressbook will hold names, addresses, and phone numbers. We also made the case that each person may have more than one phone number or address, and that each phone number or address may relate to more than one person.

For now, ignore the image field in the person table. Later in the tutorial I plan to demonstrate how to upload an image through a form, but for now just pretend it's not there.

The funny looking arrows that connect the tables together indicate a one-to-many relationship. For example, between the number and person_number tables, there may be many of the same number_id's in the latter, but only one record corresponding to that particular number_id in the former. However, since the person table also has a one-to-many relationship with the person_number table, we can say that there is a many-to-many relationship between the person and number tables.

The schema code

Here is the code to generate this schema in PostgreSQL:

  create table person (
  person_id serial,
  last_name varchar(50) not null,
  first_name varchar(50),
  middle_name varchar(50),
  image varchar(50),
  admin_user varchar(80) not null default user,
  admin_date timestamp not null default now(),
  PRIMARY KEY ("person_id"));

  create index person_last_name_idx on person (last_name);
  create index person_first_name_idx on person (first_name);

  create table number (
  number_id serial,
  descr varchar(50) not null,
  area_code int2 not null,
  prefix int2 not null,
  suffix int2 not null,
  extension varchar(20),
  admin_user varchar(80) not null default user,
  admin_date timestamp not null default now(),
  PRIMARY KEY ("number_id"));

  create unique index area_code_prefix_suffix_udx on number (area_code, prefix, suffix);

  create table address (
  address_id serial,
  descr varchar(50) not null,
  address1 varchar(50),
  address2 varchar(50),
  city varchar(50) not null,
  state char(2) not null,
  zip varchar(10),
  admin_user varchar(80) not null default user,
  admin_date timestamp not null default now(),
  PRIMARY KEY ("address_id"));

  create index address_city_idx on address (city);
  create index addrees_state_idx on address (state);

  create table person_number (
  person_number_id serial,
  person_id int4 not null,
  number_id int4 not null,
  admin_user varchar(80) not null default user,
  admin_date timestamp not null default now(),
  PRIMARY KEY ("person_number_id"));

  create unique index person_id_number_id_udx on person_number (person_id, number_id);

  create table person_address (
  person_address_id serial,
  person_id int4 not null,
  address_id int4 not null,
  admin_user varchar(80) not null default user,
  admin_date timestamp not null default now(),
  PRIMARY KEY ("person_address_id"));

  create unique index person_id_address_id_udx on person_address (person_id, address_id);

  alter table person_number
  add constraint person_number_person_id_fk
  foreign key (person_id)
  references person (person_id);

  alter table person_number
  add constraint person_number_number_id_fk
  foreign key (number_id)
  references number (number_id);

  alter table person_address
  add constraint person_address_person_id_fk
  foreign key (person_id)
  references person (person_id);

  alter table person_address
  add constraint person_address_address_id_fk
  foreign key (address_id)
  references address (address_id);
  

The admin_user and admin_date fields are not necessary. When I worked at Marconi, the Oracle gurus insisted that every table include these fields. It's a habit I developed then and it has not been broken (not yet, anyway). With PostgreSQL, these fields are automatically filled by the default parameters.

The descr field in the address and number tables is for a description of the particular record (ie.- cell, fax, home, office, etc). Since we decided we'll want to search by last_name, first_name, area_code, prefix, city, and state, we index these fields to speed up searches. A unique index is put on the id fields in each of the connecting tables to prevent duplicate relationships. There is a unique index on the area_code, prefix, and suffix fields in the number table to prevent duplicate phone numbers. Finally, we create foreign keys between the tables to guarantee data integrity. For example, if I try to insert a record in the person_number table when the person and number tables are empty, I get this response:

  addressbook=> insert into person_number (person_id, number_id) values(1, 1);
  ERROR:  person_number_number_id_fk referential integrity violation 
  - key referenced from person_number not found in number
  addressbook=> 
  

Similarly, the foreign key will prevent a person from being deleted unless the record that connects it to the address or phone number is deleted 1st:

  addressbook=> insert into person (last_name, first_name) values('Osbourne', 'Ozzy');
  INSERT 162663 1
  addressbook=> insert into number (descr, area_code, prefix, suffix) values('Cell', 813, 555, 1212);
  INSERT 162664 1
  addressbook=> insert into person_number (person_id, number_id) values(1, 1);
  INSERT 162665 1
  addressbook=> delete from person where person_id=1;
  ERROR:  person_number_person_id_fk referential integrity violation 
  - key in person still referenced from person_number
  addressbook=> 
  

I guess I should state here that any data used in this tutorial is for demonstration purposes only. Don't think for a moment that I have Ozzy Osbourne's phone number! Also note that this addressbook is designed for phone numbers and addresses in the United States, otherwise we would have to include a country_code field in the number table and probably change the zip field to postal_code in the address table.

MySQL schema

To create the database in MySQL:

  create table person (
  person_id bigint unsigned not null,
  last_name varchar(50) not null,
  first_name varchar(50),
  middle_name varchar(50),
  image varchar(50),
  admin_user varchar(80) not null,
  admin_date timestamp(14) not null);

  alter table person
  modify person_id bigint unsigned not null auto_increment,
  add index (last_name),
  add index (first_name),
  add primary key (person_id);

  create table number (
  number_id bigint unsigned not null,
  descr varchar(50) not null,
  area_code smallint unsigned not null,
  prefix smallint unsigned not null,
  suffix smallint unsigned not null,
  extension varchar(20),
  admin_user varchar(80) not null,
  admin_date timestamp(14) not null);

  alter table number
  modify number_id bigint unsigned not null auto_increment,
  add unique index (area_code, prefix, suffix),
  add primary key (number_id);

  create table address (
  address_id bigint unsigned not null,
  descr varchar(50) not null,
  address1 varchar(50),
  address2 varchar(50),
  city varchar(50) not null,
  state char(2) not null,
  zip varchar(10),
  admin_user varchar(80) not null,
  admin_date timestamp(14) not null);

  alter table address
  modify address_id bigint unsigned not null auto_increment,
  add index (city),
  add index (state),
  add primary key (address_id);

  create table person_number (
  person_number_id bigint unsigned not null,
  person_id bigint not null,
  number_id bigint not null,
  admin_user varchar(80) not null,
  admin_date timestamp(14) not null);

  alter table person_number
  modify person_number_id bigint unsigned not null auto_increment,
  add unique index (person_id, number_id),
  add primary key (person_number_id);

  create table person_address (
  person_address_id bigint unsigned not null,
  person_id bigint not null,
  address_id bigint not null,
  admin_user varchar(80) not null,
  admin_date timestamp(14) not null);

  alter table person_address
  modify person_address_id bigint unsigned not null auto_increment,
  add unique index (person_id, address_id),
  add primary key (person_address_id);
  

Unfortunately, MySQL does not have the capability to create foreign keys. This means that MySQL users will have to write extra code to guarantee their data's integrity (I'll indicate when this will be necessary). Also, I have not been able to get the default user parameter to work with the admin_user fields, which means every time a record is inserted or updated, MySQL users will need to include extra data to accomodate the admin_user field. For example, PostgreSQL users can use this code:

  addressbook=> insert into person (last_name, first_name) values('Osbourne', 'Ozzy');
  INSERT 162663 1
  addressbook=>
  

which will yield when selected:

  addressbook=> select * from person;     
   person_id | last_name | first_name | middle_name | image | admin_user |       admin_date       
  -----------+-----------+------------+-------------+-------+------------+------------------------
           1 | Osbourne  | Ozzy       |             |       | fliptop    | 2001-06-09 15:38:00-04
  (1 row)

  addressbook=> 
  

But MySQL users will have to do this:

  mysql> insert into person (last_name, first_name, admin_user) values('Osbourne', 'Ozzy', user());
  Query OK, 1 row affected (0.02 sec)

  mysql> select * from person;
  +-----------+-----------+------------+-------------+-------+-------------------+----------------+
  | person_id | last_name | first_name | middle_name | image | admin_user        | admin_date     |
  +-----------+-----------+------------+-------------+-------+-------------------+----------------+
  |         1 | Osbourne  | Ozzy       | NULL        | NULL  | fliptop@localhost | 20010609163355 |
  +-----------+-----------+------------+-------------+-------+-------------------+----------------+
  1 row in set (0.00 sec)

  mysql> 
  

Of course, I may be wrong about this, so if anyone knows of a way to automatically insert a default user() value in MySQL, let me know.

Obviously, if you choose not to use the admin_user and admin_date fields, then you won't have to worry about any of this.

Coming Next: Step 3 - Creating perl classes and our first cgi