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
.
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), ...
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.