Building a Real-Time Kitchen Data Model with OLTP

In the earlier article, I explained about the roles of the data architects and how they approach the design by understanding the User Interface(UI).

What is Online Transactional Processing?

Online Transaction Processing (OLTP) systems are designed for fast, real-time processing of high volumes of transactional data, supporting critical operations like banking, retail, and online booking. They use highly normalized databases to ensure data integrity, reduce redundancy, and enable efficient, concurrent updates.

Third Normal Form (3NF), introduced by Dr. Edgar F. Codd in 1971, is a database design principle that eliminates transitive dependencies, ensuring every non-key attribute depends only on the primary key. This reduces data redundancy and anomalies, making databases more efficient and reliable.

The initial step is to understand the overall business flow. To do this, I’m considering two scenarios: one from the customer’s process and another from the restaurant’s process. I created flowcharts for that.

Left:- Customer Process and Right:- Restaurant Process

A Pizza World customer browses the menu, signs in or continues as a guest, checks offers, and adds items to the cart. The system checks inventory; unavailable items trigger an alert. After reviewing the cart and applying rewards, the customer selects delivery (enters address) or pickup (provides name/phone), chooses a tip and payment method, and confirms the order. Payment is processed securely, and an order number and timeline are generated. For pickup, the restaurant prepares and quality-checks the order. For delivery, a driver is assigned and tracked in real time. The order is marked complete once collected or delivered. Once we understand the flow of the business, we create a diagram called an Entity-Relationship.

Translating the requirement into an entity relationship (ER) diagram maps key entities like Customer, Order, and Passenger, and shows their connections through relationships such as “places” and “belongs to.” Entities represent real-world objects, while relationships define their interactions. This mapping guides database design. Relationships can be identifying (the child depends on the parent) or non-identifying (the child is independent of the parent).

The above image clearly explains the structure of entity-relationship (Source erwin.com)

The entities are depicted in the boxes, and their relationships are interconnected. When data in one entity appears multiple times in another entity, this is called Cardinality.

What is Cardinality?

Cardinality explains how many occurrences of one entity can be associated with instances of another entity in a given relationship. The following table provides examples related to different types of cardinality.

Types of Cardinality

After learning what is entity, a relationship, and the cardinalities are, let us understand the overall design for the Pizza World user. To ensure clarity and shared understanding among stakeholders, the conceptual model presented below explains what core entities they can expect in the system and how those entities are related to each other, without diving into technical level details.

Let’s take a closer look at the Conceptual Data Model for the Restaurant Platform.

Conceptual Data Model for Restaurant Platform

What is a Conceptual Data Model?

A conceptual data model is a high-level, abstract representation of the essential data elements and relationships within a system. It emphasizes what information is important to the business without delving into technical specifics such as data types or storage details.

Let’s see what the conceptual model looks like a little closer

a. Customer — Cart (0:1)

* Entities: Customer — Cart

• Relationship:
• A Customer may have zero or one active Cart.
• Each Cart belongs to exactly one Customer.
• Cardinality: Customer (1) → Cart (0.1)
• Business Rule: A customer is allowed to maintain only one active cart at a time to ensure consistent and trackable ordering behavior.
• Meaning: Each customer can build one active cart at a time while browsing or ordering.

b. Order-Payment (1:1)

* Entities: Order — Payment

• Relationship:
• Each Order must have one Payment.
• Each Payment belongs to one Order.
• Cardinality: Order (1) → Payment (1)
• Business Rule: Payment is mandatory per order.
• Meaning: An order is only considered valid and processed once payment has been made.

c. Customer_Type-Customer(1:M)

* Entities: Customer_Type — Customer( for ex:-1)

• Relationship:
• A Customer must be part of one Customer_Type.
• A Customer_Type can be associated with many Customers.
• Cardinality: Customer_Type (1) → Customer (1:M)
• Business Rule: Every customer must be assigned to a classification.
• Meaning: “Customers are categorized (e.g., Regular, VIP) for segmentation and benefit tracking, and enable targeted marketing”.

c.i Payment-Customer_Card

* Entities: Payment — Card

• Relationship:
• A payment is made using one card; a card can be used for multiple payments.
• Cardinality: Payment(0:1) → Card(1:M)
• Business Rule: Customers may use their card details, and each card can be used for zero or one payment. Each payment must be linked to one card.
• Meaning: This ensures traceability of payments to specific cards, supporting auditability.

d. Order_Movement_Status-Service_Mode_Event (M:1)

* Entities: Order_Movement_Status — Service_Mode_Event

• Relationship:
• Each status is linked to one Service_Mode_Event.
• A Service_Mode_Event is linked to many statuses.
• Cardinality: Order_Movement_Status (1.M) → Service_Mode_Event (1)
• Business Rule: Statuses must be tracked according to the specific service mode.
• Meaning: Order statuses vary depending on whether the order is placed, packed, or delivered, allowing the system to distinguish and manage each mode appropriately.

e. Order-Menu(M:M)

* Entities: Order — Menu

• Relationship:
• An order includes one or more menu items.
• Cardinality: Order ←→ Menu (M:M)
• Business Rule: Each order can include multiple menu items, and each menu item can be part of many orders.
• Meaning: This captures the selection of food items in a customer’s order.

Up to now, we understood the entities that are nouns and their relationships and the cardinalities of the relationships using the conceptual data model explained above.

Once we get a sign-off from the business, the next step is to elaborate on these entities. Here we turn it into a logical model which introduces the structure, normalization, and business rules. In the next article, this will be discussed.