Learning MySQL

Designing and Creating the Wedding Database

Let’s say Jack and Jill are getting married and would rather not receive the traditional 7 toasters and 11 electric kettles. Instead, they’ve decided to lists things they actually do want on a web site and let wedding guests select something useful to buy from the list.

Thinking about the problem carefully, we come across several requirements. Our application should:

• Allow Jack and Jill to add to or modify the list of required gifts

• Allow users to view the gifts that can be selected

• Allow users to select gifts to buy, or to deselect gifts they have previously reserved

• Not allow users to select gifts that are already reserved

• Authenticate users to identify them and prevent one user modifying the selections of another

There are two entities here: users and gifts. Each user has a unique username and password, while each gift has a unique gift ID number, a description, desired quantity, color, place of purchase, and price. A gift may be reserved by a user, so each gift record can have a username associated with it. A one-to-many relationship is maintained between the two tables: each gift can be reserved by only one user; each user can reserve zero or more gifts. Figure 15-1 shows the ER model in the MySQL Workbench.

Figure 15-1. The wedding registry ER model using the MySQL Workbench

Our database needs to contains only two tables—users, which stores ...