Managing & Using MySQL, 2nd Edition

The Wedding Gift Registry Database

Example 11-1 is a file that contains the SQL statements (and the MySQL use command) to create and populate the wedding database. The database contains only two tables: presents, which stores data about gifts, including a unique identifier, description, desired quantity, color, place of purchase, price, and the user who reserved the gift, and people, which stores a unique username and password for each guest.

A one-to-many relationship is maintained between the two tables; each guest stored in the people table can reserve zero or more gifts in the presents table. When the gifts are initially inserted in the wedding database using the statements in Example 11-1, the people_id in the presents table is set to NULL so that all gifts are unreserved. If a guest reserves a gift, the NULL value is replaced with the guest’s people_id. For example, if the guest hugh reserves the gift with a present_id of 2 (such as the Richmond Tigers autographed print (unframed)), the people_id of that gift is set to hugh.

Example 11-1. The statements to create and populate the wedding database

create database wedding; use wedding; CREATE TABLE people ( people_id varchar(30) DEFAULT '' NOT NULL, passwd varchar(30), PRIMARY KEY (people_id) ); INSERT INTO people VALUES ('hugh','huw8o3cEvVS8o'); CREATE TABLE presents ( present_id int(11) DEFAULT '0' NOT NULL auto_increment, present varchar(255), shop varchar(100), quantity varchar(30), colour varchar(30), price varchar(30), ...