SQL Server Magazine

Scheduling and reservations are part of everyday life. When you make a reservation for dinner at your favorite restaurant, schedule a weekend getaway at a hotel in the mountains, or plan your next business trip, you deal with reservations systems and scheduling packages. The idea and core of the design for a reservations system metamodel—the fourth in my series about metamodeling—came from my associate Bill Lennon, a software developer at 5280 Solutions in Denver. I challenged him with a case study that required a data model for a restaurant reservations system, and he did such a good job that—with his permission—I want to share it with you.

A reservations system can be elegantly simple or incredibly complex. Let's start at the beginning with the easiest solution for a reservations system—the list. Figure 1 shows a metamodel of the list, which is the kind of structure that you expect to find in small restaurants and lodging houses—private operations that don't need sophisticated reservations systems. The list doesn't have to be computerized, much less put into a database; a simple notebook or tablet works fine. Figure 1 is actually a complex list that contains more information than we ever kept at our family restaurant when I was growing up. The information includes the customer's name (the primary identifier) as well as the customer's phone number, the reservation's date and time, and the number of people in the party. This list also provides space to capture preferences such as seating area (e.g., the indoor dining room or outdoor veranda), seating type (e.g., a table, booth, or alcove), number of booster chairs or high chairs that the party needs, and smoking or nonsmoking section. When employees take reservations, they can also add their name, the time they took the reservation, and the preassigned table number.

Although most small operations don't need a system that's more complicated than a list, if you manage a large restaurant operation that has multiple dining rooms and facilities for gatherings such as parties, conferences, and receptions, you need to improve on this simple reservations system. To enhance the metamodel that Figure 1 represents, let's develop two additional kinds of lists: a customer list that contains no duplicates and a resource list that shows the items (e.g., tables, seating areas, seating types) that are available. Then, when you want to retrieve customer-name or resource information, you can simply consult one short list instead of scanning through thousands of reservations records.

Figure 2, page 68, shows how I expanded Figure 1's basic metamodel to include the customer and resource lists. I also replaced CustomerName in the Reservation table with ReservationID because not only is ReservationID an identity data type but it's also a better primary identifier than CustomerName. (To discover why ReservationID is a better primary identifier, see the sidebar "What Makes a Good Primary Key?" page 68.) CustomerName is now part of the Customer table, along with more attributes that help you understand and better serve your customer. Note that your customer base includes ReferredBy, the referring customer, so that you can track the names of patrons who recommend your restaurant. The ReferredBy report forms the basis of a favored-customer or frequent-patron program that you can integrate into your marketing program.

CustomerID, Customer's primary key, is now a foreign key in the Reservation table. The relationship between Customer and Reservation is mandatory one-to-many (1:M); a customer can make one or many reservations, but each reservation must be associated with an existing customer. For each record in the Reservation table, the CustomerID field must be populated with a valid customer number. Figure 2's model enforces a business rule that I arbitrarily imposed for this project: Customers aren't customers until they make a reservation. If you want to loosen this rule so that you can manage the prospective customer (a person who hasn't made a reservation but who might be a good candidate for doing so), you can make the relationship optional, like the relationship between Table and Reservation.

You might think that you can't enforce the mandatory relationship between Customer and Reservation. In fact, you can enforce the relationship in most database environments, and you certainly can enforce relationships with SQL Server. If you use data-modeling software that generates Data Definition Language (DDL) code, you should be able to direct the software to generate code that enforces the mandatory 1:M relationship. You can send this directive in two ways: by declarative referential integrity (DRI) or by triggers. If you write the DDL manually, you need to add only a few extra lines of code to invoke DRI, as the following T-SQL example shows:

ALTER TABLE Reservation

ADD CONSTRAINT fkey_Reservation2Customer 
FOREIGN KEY(CustomerID)

REFERENCES Customer(CustomerID)

For more information about and code for foreign keys and DRI, see SQL by Design, "The Foreign Key," August 1999.

   Prev. page   [1] 2     next page