Planning and Creating the Database Tables

When you think of an address book, the obvious fields come to mind: name, address, telephone number, email address. However, if you look at your own paper-based address book, you may note that you have several entries for one person. Maybe that person has three telephone numbers, or two email addresses, and so forth. In your online address book, a set of related tables will help alleviate the redundancy and repetition of information.

Table 20.1 shows sample table and field names to use for your online address book. In a minute, you'll use actual SQL statements to create the tables, but first you should look at this information and try to see the relationships appear. Ask yourself which of the fields should be primary or unique keys.

Table 20.1. Address Book Table and Field Names

Table Name	Field Names
master_name	id, date_added, date_modified, f_name, l_name
address	id, master_id, date_added, date_modified, address, city, state, zipcode, type
telephone	id, master_id, date_added, date_modified, tel_number, type
fax	id, master_id, date_added, date_modified, fax_number, type
email	id, master_id, date_added, date_modified, email, type
personal_notes	id, master_id, date_added, date_modified, note

Notice the use of date-related fields; each table has a date_added and date_modified field in it. The fields will help maintain your data; you may at some point want to issue a query that removes all records that are older than a certain number of months or years, or that removes all records that haven't been updated within a certain period of time.

As you can see in the following SQL statements, the master_name table has two fields besides the ID and date-related fields: f_name and l_name, for first name and last name. The id field is the primary key. No other keys need to be primary or unique, unless you really want to limit your address book to one John Smith, one Mary Jones, and so forth.

By the Way

The field lengths for the text fields in the following statements are arbitrary; you can make them as long or as short as you want, within the allowable definition of the field type.

The following SQL statement creates the master_name table:

 mysql> CREATE TABLE master_name (      -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,      -> date_added DATETIME,      -> date_modified DATETIME,      -> f_name VARCHAR (75),      -> l_name VARCHAR (75)      -> ); Query OK, 0 rows affected (0.01 sec)

Next, you'll create the supplementary tables, which all relate back to the master_name table. For instance, the address table has the basic primary key id field and the date_added and date_modified fields, plus the field through which the relationship will be madethe master_id field.

The master_id will be equal to the id field in the master_name table, matching the person whose address this is. The master_id field is not a unique key because it is a perfectly valid assumption that one person may have several address entries. We see this in the type field, which is defined as an enumerated list containing three options: home, work, or other. A person may have one or more of all three types, so no other keys are present in this table besides the primary key id. Assuming that this particular address book contains only United States addresses, we round out the table with address, city, state, and zipcode fields:

mysql> CREATE TABLE address (     -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     -> master_id INT NOT NULL,     -> date_added DATETIME,     -> date_modified DATETIME,     -> address VARCHAR (255),     -> city VARCHAR (30),     -> state CHAR (2),     -> zipcode VARCHAR (10),     -> type ENUM ('home', 'work', 'other')     -> ); Query OK, 0 rows affected (0.01 sec)

The telephone, fax, and email tables are all variations on the same theme:

mysql> CREATE TABLE telephone (     -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     -> master_id INT NOT NULL,     -> date_added DATETIME,     -> date_modified DATETIME,     -> tel_number VARCHAR (25),     -> type ENUM ('home', 'work', 'other')     -> ); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE fax (     -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     -> master_id INT NOT NULL,     -> date_added DATETIME,     -> date_modified DATETIME,     -> fax_number VARCHAR (25),     -> type ENUM ('home', 'work', 'other')     -> ); Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE email (     -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,     -> master_id INT NOT NULL,     -> date_added DATETIME,     -> date_modified DATETIME,     -> email VARCHAR (150),     -> type ENUM ('home', 'work', 'other')     -> ); Query OK, 0 rows affected (0.00 sec)

The personal_notes table also follows the same sort of pattern, except that master_id is a unique key and allows only one notes record per person:

mysql> CREATE TABLE personal_notes (     -> id int NOT NULL PRIMARY KEY AUTO_INCREMENT,     -> master_id INT NOT NULL UNIQUE,     -> date_added DATETIME,     -> date_modified DATETIME,     -> note TEXT     -> ); Query OK, 0 rows affected (0.00 sec)

Now that your tables are created, you can work through the forms and scripts for managing and viewing your records.