In the food and beverage industry, a key differentiator is restaurants' ability to offer a highly customizable menu.

Today, customers expect flexibility in their orders, whether they choose pizza toppings, sandwich bread, or dish spice levels.

To meet these demands, modern menu systems must support complex structures like product modifiers, modifier groups, and even nested relationships between them.

This article provides a detailed guide on designing such a system using a relational database model. We’ll explore the relationships between products, modifiers, and modifier groups and demonstrate how to implement this design using SQLAlchemy and SQLModel, two powerful Python libraries for database management.

Key Concepts

Before diving into the technical implementation, let’s define the key concepts involved in designing a menu system:

Product: A menu item that customers can order, such as a pizza, burger, or salad.
Modifier: An option or customization for a product. For example, "Extra Cheese" or "Gluten-Free Bread".
Modifier Group: A collection of modifiers that can be applied to a product. For example, a "Toppings" group might include modifiers like "Pepperoni", "Mushrooms", and "Olives".
Nested Relationships: Modifier groups can contain modifiers, and modifiers can belong to multiple modifier groups. Additionally, modifiers can have their modifier groups, creating a hierarchical structure. For example, the modifier "Extra Cheese" might have a nested modifier group for "Cheese Type" with options like "Mozzarella" or "Cheddar".

Database Design Overview

The database design is implemented using SQLAlchemy and SQLModel, which provide an object-relational mapping (ORM) framework for Python.

This allows us to define our database schema using Python classes and easily manage relationships between tables.

Below is a breakdown of the key tables and their relationships:

1. Menu

The Menu table represents the menu itself. Each menu belongs to a specific location and contains products, categories, and modifiers.

class Menu(SQLModel, table=True):

    __tablename__ = f'{context_prefix}_menu'

    id: Optional[str] = Field(primary_key=True, default_factory=pk.get_tsid)

    menu_id: Optional[str] = Field(unique=True)

    location_id: Optional[str] = Field(foreign_key='dlv_location.location_id', nullable=False)

    menu_en: str  # Menu name in English

    menu_ar: str  # Menu name in Arabic

    currency: str  # Currency for pricing

    is_active: bool = Field(default=False)  # Whether the menu is active


    # Relationships

    location: Optional['DlvLocation'] = Relationship(back_populates='menus')

    categories: List['DlvCategory'] = Relationship(back_populates='menu')

2. Category

The Category table represents a category within the menu, such as "Pizzas", "Burgers", or "Desserts". Categories can have parent-child relationships, allowing for hierarchical organization.


class Category(SQLModel, table=True):
    __tablename__ = f'{context_prefix}_category'
    id: Optional[str] = Field(primary_key=True, default_factory=pk.get_tsid)
    category_id: Optional[str] = Field(unique=True)
    category_parent_id: Optional[str] = Field(foreign_key="dlv_category.category_id", nullable=True)
    name_en: str  # Category name in English
    name_ar: str  # Category name in Arabic
    menu_id: str = Field(foreign_key='dlv_menu.menu_id')  # Associated menu

    # Relationships
    children: List['DlvCategory'] = Relationship(back_populates='parent_category')
    parent_category: Optional['DlvCategory'] = Relationship(back_populates='children')
    products: List["DlvProduct"] = Relationship(back_populates='category')

3. Product

The Product table represents a specific menu item, such as "Margherita Pizza" or "Cheeseburger". Products can belong to a category and have associated modifier groups.


class Product(SQLModel, table=True):
    __tablename__ = f'{context_prefix}_product'
    id: Optional[str] = Field(primary_key=True, default_factory=pk.get_tsid)
    product_id: Optional[str] = Field(unique=True)
    name_en: str  # Product name in English
    name_ar: str  # Product name in Arabic
    category_id: str = Field(foreign_key='dlv_category.category_id', nullable=True)  # Associated category

    # Relationships
    category: Optional["DlvCategory"] = Relationship(back_populates="products")
    modifier_groups: List["DlvModifierGroup"] = Relationship(link_model=DlvProductModifierGroup)

4. ModifierGroup

The DlvModifierGroup table represents a group of modifiers, such as "Toppings" or "Crust Type". Modifier groups can be associated with products and contain multiple modifiers.


class ModifierGroup(SQLModel, table=True):
    __tablename__ = f'{context_prefix}_modifier_group'
    id: Optional[str] = Field(primary_key=True, default_factory=pk.get_tsid)
    name_en: str  # Modifier group name in English
    name_ar: str  # Modifier group name in Arabic
    menu_id: str  # Associated menu

    # Relationships
    modifiers: List["DlvModifier"] = Relationship(link_model=DlvModifierGroupModifier)

5. Modifier

The DlvModifier table represents an individual modifier, such as "Extra Cheese" or "Gluten-Free Bread". Modifiers can belong to multiple modifier groups and can also have nested modifier groups.


class DlvModifier(SQLModel, table=True):
    __tablename__ = f'{context_prefix}_modifier'
    id: Optional[str] = Field(primary_key=True, default_factory=pk.get_tsid)
    name_en: str  # Modifier name in English
    name_ar: str  # Modifier name in Arabic
    menu_id: str  # Associated menu

    # Relationships
    modifier_groups: List["DlvModifierGroup"] = Relationship(link_model=DlvModifierModifierGroup)

Nested Relationships

One of the most powerful features of this design is its support for nested relationships between modifier groups and modifiers. This allows for highly flexible and customizable menu structures. Here’s how it works:

Modifier Groups Contain Modifiers: A modifier group like "Toppings" can contain modifiers such as "Extra Cheese", "Pepperoni", and "Mushrooms".
Modifiers Belong to Multiple Groups: A modifier like "Extra Cheese" can belong to multiple modifier groups, such as "Toppings" and "Premium Add-ons".
Modifiers Can Have Nested Modifier Groups: A modifier like "Extra Cheese" can have its own modifier group, such as "Cheese Type", with options like "Mozzarella" or "Cheddar".

This hierarchical structure is achieved using many-to-many relationships and a link table (DlvModifierGroupModifier) to connect modifiers and modifier groups.

Example Use Case: Designing a Pizza Menu

Let’s walk through an example of designing a pizza menu using this system.

Scenario

Menu: "Pizza Menu"
Category: "Classic Pizzas"
Product: "Margherita Pizza"
Modifier Groups:
- "Toppings" (contains "Extra Cheese", "Pepperoni")
- "Crust Type" (contains "Thin Crust", "Thick Crust")
Nested Modifiers:
- "Extra Cheese" has a nested modifier group "Cheese Type" with options "Mozzarella" and "Cheddar."

Database Representation

The Product table stores the "Margherita Pizza".
The ModifierGroup table stores "Toppings" and "Crust Type".
The Modifier table stores "Extra Cheese", "Pepperoni", "Thin Crust", and "Thick Crust".
The ModifierGroupModifier link table connects modifiers to their groups.
The ProductModifierGroup link table connects products to their modifier groups.

Benefits of This Design

Flexibility: Supports complex menu structures with nested relationships.
Scalability: Easily add new products, modifiers, or modifier groups.
Localization: Supports multiple languages (e.g., name_en and name_ar).
Soft Deletes: Uses deleted_at for soft deletion, preserving historical data.
Efficient Data Management: Relationships are managed through link tables, ensuring data integrity and reducing redundancy.

Conclusion

Designing a food menu with nested product modifiers and modifier groups requires a well-thought-out database schema. By leveraging SQLAlchemy and SQLModel, we can create a flexible and scalable system that accommodates complex menu structures.

This approach ensures that restaurants can offer extensive customization options while maintaining efficient data management. Whether you’re building a system for a small café or a large restaurant chain, this design provides the foundation for a robust and user-friendly menu management solution.

#backend-projects

Designing a Food Menu App in Python