Serving Delicious Food (and Data) – A Data Model for Restaurants

What part does database design play in running a restaurant? What might the data model for a restaurant database look like? Find out in this article.

A restaurant serves people with ready-made food. This is a type of business that is thriving all over the world, and often with a lot of flare. People feel very comfortable going to restaurants, and they are starting to expect a wide range of options when it comes to their next meal.

In New York City alone, there are more than 24,000 dining establishments. These include takeaways (i.e. pizza, sub shops, Chinese takeout), delis, cafes, and fine dining restaurants. The following saying fits the restaurant industry very well; it’s practically their universal mission statement:

Do what you do so well that they will want to see it again and bring their friends and family along.

Walt Disney

Why Do Restaurants Need Databases?

Restaurant management is not an easy task. When it comes to keeping track of and excuting day-to-day tasks, even the most experienced restaurateur may well have more than they can easily manage. Running a profitable restaurant requires managing inventory/stock, minimizing waste, managing tables (especially in peak hours), maintaining a customer-friendly menu, executing orders efficiently, and overseeing restaurant staff. That’s quite a lot!

A restaurant management system must perform most of these activities with minimal manual intervention. It has to present the managers with accurate information so that they can keep customers happy. This can mean making appropriate changes to the menu and even the way the restaurant functions.

The Restaurant Data Model

This article is all about designing a full-fledged data model for a restaurant (dine-in or takeaway). We’ll also address two big problems that people in the restaurant business encounter in their day-to-day activities. Finally, we’ll think about the changes needed to build those capabilities into an existing system.

As we dive into the data model, I will mention certain user roles. These roles are actually for staff members, such as:

• Manager – Manages inventory, payroll, employee scheduling, and metrics for the restaurant
• Host – Seats guests and assigns servers to tables
• Waiter (also known as server) – Takes customers’ orders to the kitchen and delivers the prepared order to the customer
• Supervisor (also known as chef or head cook) – Supervises tasks in the kitchen and assigns tasks to cooks
• Cook – Reads the order details received from the supervisor, prepares the food, and informs the supervisor when it is ready
• Busboy – Keeps track of which tables are being used; cleans tables and updates their status as necessary

A data model for a restaurant business must have the following elementary features:

• KOT (Kitchen Order Token) Management
• KOD (Kitchen Order Delivery) Management
• Menu Management

Let’s look at each of these features in detail.

KOT (Kitchen Order Token) Management

This is the most important part of our data model: it is all about collecting order details from customers through various channels. Why various channels? Because there are several ways that orders can be made – online or via mobile app, by phone calls, or through waiters or other employees. Whenever an order is placed by a customer, a KOT (Kitchen Order Token) is generated. Eventually, the KOT will be prepared by the kitchen staff.

I’ll create a table, kot, to hold the preliminary order details. This table has the following columns:

I’d like to point out that an order collected from one table at one time is tagged under one kot_id. If the same table later orders more items, the system will generate another kot_id and tag all these new items under that ID. In the end, all kot_ids for the same table will be added together in the final bill.

KOT management requires additional static and transactional tables, which are:

• order_channel – This table contains details about the channels a restaurant uses to accept orders. Common examples include online, dine in, take away (carry out), etc.

• dine_in_table_sitting – This is a transactional table that stores table occupancy data. Its columns include dine_in_table_id, dine_in_time, dine_out_time, num_person_sitting, and customer_id. As soon as the host assigns a customer to a table and enters the information into the system, a record is inserted into this table. To fetch the current occupancy status of tables at any given time, this is the table that will be used.

  Suppose you want to build this feature. Here is the SQL that will tell you the current occupancy status for all restaurant tables:

  SELECT 
    b.id as table_id,
    c.area_desc,
    CASE 
      WHEN a.dine_in_table_id IS NULL THEN ‘VACANT’ 
      ELSE ‘OCCUPIED’
    END AS current_table_status
  FROM dine_in_table_sitting a, dine_in_table b, dine_in_table_area
  WHERE a.dine_in_table_id (+) = b.id
  	AND b.dine_in_table_area = c.id
  	AND a.dine_out_time IS NULL;
  

• kot_status – This table holds all possible statuses for a KOT: order received, order in process, order delivered, etc.
• kot_menu_item – This transactional table stores the details of all the items in a KOT. It also defines the relationship between the KOT and a menu_item. The menu_item_id and quantity fields against a kot_id signify the item on order and how much of it is needed.

KOD (Kitchen Order Delivery) Management

A large part of how well a restaurant performs boils down to managing KOT inside the kitchen. Usually a supervisor collects KOTs from waiters, other employees, or an online system. Then the supervisor assigns the menu items to one or more cooks. The cook prepares the items and hands them over to the supervisor. Then the waiter or another staff member collects the order and delivers it to the customer.

But that’s not all that KOD management includes. Managing resources, stocking ingredients, regularly updating remaining inventory, and requesting new inventory as needed is also part of the daily operation of the kitchen. The supervisor plays a prominent role in the kitchen’s seamless running, especially during peak hours. A system is considered ‘smart’ or ‘intelligent’ if it can replicate a supervisor’s job functions – which is close to impossible in most places.

To build a model for this complex piece of management, I’ll create another table, named KOD. This table consists of the following columns:

Menu Management

This component is as important as KOT and KOD management. The menu – both in its visual presentation and in the dishes it offers – is one of the first things that attracts customers. So every restaurateur tries to keep their menu as enticing as possible.

Let’s create another table to hold menu details. I will add columns for all the details we usually see on a menu:

Solving Real-World Restaurant Problems with Data

Some problems are extremely common in the food service world. In particular, I am thinking of long waiting times, both to sit at a table and to get your food. These problems can often be at least partially solved by better organizing and using restaurant data.

In a dine-in setting, few things are more annoying to customers than having to wait a long time for a table. Minimizing customers’ wait times during peak hours requires keeping a close watch on the status of individual tables. If there is no proper management of tables and staff, customer wait times start to grow. If wait times are too long, customers may leave and look for another restaurant that will serve them quickly.

One can address this concern by introducing certain changes to this data model. These changes would:

1. Add real-time table management, a digitized way to manage table availability, status tracking, and utilization rates.
2. Reduce table turnaround time by measuring staff efficiency and enabling effective workforce planning – for example, by assembling a cleaning crew and assigning staff to a table or a group of tables.
3. Publish the real-time status of individual tables to the managers’ screens, so that they can keep an eye on any long-pending activities.

Another problem is making customers wait for their food. For both dine-in and takeaway customers, this can be helped by providing status updates directly to the diner. Monitoring the status of individual KOTs is vital here. As the KOT progresses in the kitchen, its status gets updated in the KOT table. This mechanism gives a real-time update to customers about the status of their orders.

How Can We Make This Restaurant Data Model Better?

There are so many innovative ideas that restaurant owners and operators are coming up with to attract and retain their customers. For example:

• Many run customer loyalty programs, which often include an NPS calculation formula. These maintain a loyalty account for customers and give guests points for each visit, purchase, etc. Diners can cash in these points as and when they like for various rewards (usually some free food, a percentage off their check, or a free meal).
• Some eateries make their menu items as customizable as possible. They allow their diners to choose ingredients for salads or pastas, or they substitute foods to meet certain dietary restrictions.

Inventory management is another area that plays a prominent role in making a restaurant profitable.

Can we build these capabilities into this data model? Share your thoughts in the comment section below.