Multiple Phone Numbers (Phones) per Person

The following SQL commands create two tables in the phonebook database. One table contains information about the people in the phonebook. The second contains phone numbers. This allows each person to have more that one phone number.

DROP DATABASE IF EXISTS phonebook;

CREATE DATABASE phonebook;

USE phonebook;

/* ---------- create phonebook entry table */

CREATE TABLE     info(
infoid           int                not null auto_increment,
firstname        varchar(128),
lastname         varchar(128),
PRIMARY KEY      (infoid)
)                ENGINE=InnoDB;

/* ---------- create phone number table */

CREATE TABLE     phone(
phoneid          int                not null auto_increment,
infoid           int                not null,
phonenumber      varchar(32),
PRIMARY KEY      (phoneid),
FOREIGN KEY      (infoid)           REFERENCES info (infoid)
                 ON DELETE CASCADE
                 ON UPDATE CASCADE
)                ENGINE=InnoDB;

/* ---------- set auto increment initial values for info table */

ALTER TABLE info AUTO_INCREMENT = 10001;

/* ---------- inset data into the database */

INSERT INTO info (infoid,firstname,lastname) VALUES (10001,'Tim','Smith');
INSERT INTO info (infoid,firstname,lastname) VALUES (10002,'Judy','Jones');
INSERT INTO info (infoid,firstname,lastname) VALUES (10003,'John','Smith');

INSERT INTO phone (infoid,phonenumber) VALUES (10001,'666-666-6666');
INSERT INTO phone (infoid,phonenumber) VALUES (10001,'666-666-6667');
INSERT INTO phone (infoid,phonenumber) VALUES (10001,'666-666-6668');
INSERT INTO phone (infoid,phonenumber) VALUES (10002,'777-777-7771');
INSERT INTO phone (infoid,phonenumber) VALUES (10002,'777-777-7772');
INSERT INTO phone (infoid,phonenumber) VALUES (10003,'888-888-8888');

/* ---------- display data in database */

SELECT info.firstname,info.lastname,phone.phonenumber FROM info
    JOIN phone WHERE info.infoid=phone.infoid AND info.lastname='Smith';

SELECT phone.phonenumber FROM info,phone WHERE info.lastname='Smith';

SELECT * FROM info;

Test a Few Things

What happens? Why?

INSERT INTO phone (phonenumber) VALUES ('999-999-9999');

INSERT INTO phone (infoid,phonenumber) VALUES (9999,'999-999-9999');

SELECT * FROM phone;

INSERT INTO info (firstname,lastname) VALUES ('Lois','Lane');

SELECT * FROM info;