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 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?.") 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.
In addition to a customer list, the second metamodel contains three lists of resources—tables, seating areas, and seating types. These three new tables are related to the Reservation table in an optional 1:M relationship. Therefore, if a customer doesn't have a seating-area or seating-type preference, or if you don't want to bother assigning customers to a table until they arrive for dinner, you don't have to enter values into any of these fields.
Notice that Figure 2's metamodel contains two more relationships that I haven't mentioned yet—SeatingArea-to-Table and SeatingType-to-Table. These relationships are mandatory 1:M because you want to know where each entry in the Table list is located (i.e., SeatingArea) and which type of table it is (i.e., SeatingType). SeatingArea also has an attribute for PercentSmoking, whereas Table has an attribute for SmokingOK and Reservation has an attribute for SmokingYorN. Given today's cultural climate, in which no-smoking laws are common, I enhanced the metamodel with ways to track the names of customers who want to eat in the restaurant's nonsmoking section, the percentage of space in a seating area that's available for smokers, and a table's smoking status.
For many restaurant operations, Figure 2's metamodel works fine. However, for larger operations, the metamodel is missing two important capabilities: It doesn't let you reserve more than one table for a single reservation, and it doesn't let you assign wait staff to serve a party. Figure 3 shows a metamodel that extends Figure 2's metamodel to include these capabilities. The metamodel contains five new tables. One new table—ReserveTable—lets you reserve more than one table for a single party. The table contains just a few attributes—the ReserveTableID identifier; one foreign key from Reservation and one foreign key from Table; a table sequence number; and TotalTables, which gives you the total table count for a party's reservation. Let's look at a real-world example. If a party of 20 requests a dinner reservation, and you don't have a table with a MaxCapacity value of 20 or more to accommodate them, you'll want to seat these 20 people at adjacent tables. If you have five adjoining tables, each with MaxCapacity equal to 4, you can reserve those five tables for the large party. In this model, the Reservation table holds one record for the reservation, but ReserveTable contains five records, one for each table reserved. Each of these five entries has the same ReservationID but a different TableID. The TotalTables value for all five records is 5, and the TableSeqNo value changes for each record, beginning with 1 and ending with 5. The records in ReserveTable read something like "table 1 of 5, table 2 of 5 ... table 5 of 5." By incorporating the ReserveTable table in Figure 3's metamodel, you meet the need for reserving more than one table in a reservation.
Figure 3's remaining four new tables—Employee, EmpSchedule, Shift, and Station—form the basis of a simple workday scheduling system. These tables let you assign an employee to a specific location within the restaurant for a specific period of time. Traditionally, restaurants group their tables into stations and assign each server to a station. One station can hold zero-to-many (0:M) tables, and each table is associated with one station. The zero condition exists for the cash register or the reception desk, to which you can assign employees. Serving in a restaurant is almost always shift work, and managers assign their wait staff to work various shifts throughout the week. An employee might work the morning shift (e.g., 7:00 a.m. to 3:00 p.m.), the evening shift (e.g., 2:00 p.m. to 10:00 p.m.), or the split shift (e.g., 7:00 a.m. to 11:00 a.m. and 5:00 p.m. to 9:00 p.m.). Note that the split shift requires two record entries in the Shift table. For example, a manager might assign a server to work the morning shift Monday through Wednesday, give that person Thursday and Friday off, then assign the server to work the split shift on Saturday and Sunday. This metamodel accommodates all these shift variations. In addition, by using a datetime data type for the shift start time and end time, you can query the database to ensure that you have full coverage throughout the day. In the SQL Server environment, you can use any of the Date functions—such as DateAdd(), DateDiff(), DatePart(), or DateName()—to help with this task.
You can easily modify and extend this metamodel for restaurant reservations systems to accommodate similar enterprises. These entities might include lodging organizations (e.g., hotels, motels, bed-and-breakfast businesses, dude ranches), entertainment venues (e.g., train rides, four-wheel excursions, guided tours), and theater presentations. The metamodel lends itself to any situation that requires you to keep track of the number of people who attend an event, who the attendees are, and where, within the limits of the event, they're located.
