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.
End of Article
Prev. page
1
[2]
next page -->
