Sentra F&B — Database Design¶

Database dirancang untuk mengelola struktur data F&B yang kompleks, seperti variasi resep bahan baku, komposisi modifier menu, pengaturan meja makan (table layout), serta transaksi split bill.

4.1 Entity Relationship Diagram (ERD)¶

erDiagram
    OUTLETS ||--o{ TABLES : "contains"
    TABLES ||--o{ SEATING_SESSIONS : "holds"
    SEATING_SESSIONS ||--o{ ORDERS : "places"
    MENUS ||--o{ MENU_VARIANTS : "has"
    MENU_VARIANTS ||--o{ RECIPES : "composed_of"
    INGREDIENTS ||--o{ RECIPES : "used_in"
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    MENU_VARIANTS ||--o{ ORDER_ITEMS : "sold"
    ORDER_ITEMS ||--o{ ORDER_ITEM_MODIFIERS : "customized"
    MENU_MODIFIERS ||--o{ ORDER_ITEM_MODIFIERS : "applies"
    ORDERS ||--o{ TRANSACTIONS : "settled_by"
    OUTLETS ||--o{ INGREDIENT_STOCKS : "manages"
    INGREDIENTS ||--o{ INGREDIENT_STOCKS : "stores"

4.2 Skema Tabel DDL (PostgreSQL Dialect)¶

-- 1. Tabel Outlets
CREATE TABLE outlets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tabel Tables (Pengaturan Meja Restoran)
CREATE TABLE tables (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    outlet_id UUID REFERENCES outlets(id) ON DELETE CASCADE,
    table_number VARCHAR(10) NOT NULL,
    seating_capacity INT DEFAULT 4,
    status VARCHAR(20) CHECK (status IN ('AVAILABLE', 'OCCUPIED', 'RESERVED')) DEFAULT 'AVAILABLE',
    section VARCHAR(30) DEFAULT 'MAIN_HALL', -- Ruangan: AC, Smoking Area, VIP, dll.
    UNIQUE(outlet_id, table_number)
);

-- 3. Tabel Seating Sessions (Sesi Pelanggan di Meja)
CREATE TABLE seating_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_id UUID REFERENCES tables(id),
    customer_count INT DEFAULT 1,
    opened_by UUID NOT NULL, -- User ID pelayan yang membuka meja
    opened_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP WITH TIME ZONE,
    status VARCHAR(20) CHECK (status IN ('ACTIVE', 'CLOSED')) DEFAULT 'ACTIVE'
);

-- 4. Tabel Menus (Daftar Menu Utama)
CREATE TABLE menus (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(50) CHECK (category IN ('FOOD', 'BEVERAGE', 'DESSERT', 'OTHER')),
    base_price DECIMAL(15,2) NOT NULL, -- Harga Dasar Sebelum PPN/Service
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 5. Tabel Menu Variants (Varian Ukuran / Tipe Menu)
CREATE TABLE menu_variants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    menu_id UUID REFERENCES menus(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL, -- Regular, Large, Hot, Iced
    additional_price DECIMAL(15,2) DEFAULT 0.00
);

-- 6. Tabel Menu Modifiers (Kustomisasi Menu, e.g. Add Shot, Oatmilk)
CREATE TABLE menu_modifiers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    outlet_id UUID REFERENCES outlets(id),
    name VARCHAR(100) NOT NULL,
    price DECIMAL(15,2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE
);

-- 7. Tabel Ingredients (Bahan Baku Dasar, e.g. Biji Kopi, Susu, Gula)
CREATE TABLE ingredients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(150) NOT NULL,
    unit VARCHAR(20) NOT NULL, -- gram, ml, pcs, bag
    avg_cost_price DECIMAL(15,2) NOT NULL DEFAULT 0.00 -- HPP rata-rata per unit
);

-- 8. Tabel Recipes (Matriks Komposisi Menu dan Bahan Baku)
CREATE TABLE recipes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    menu_variant_id UUID REFERENCES menu_variants(id) ON DELETE CASCADE,
    ingredient_id UUID REFERENCES ingredients(id),
    quantity_required DECIMAL(12,4) NOT NULL, -- Jumlah pemakaian bahan baku
    yield_percentage DECIMAL(5,2) DEFAULT 100.00 -- Konversi susut bahan baku
);

-- 9. Tabel Ingredient Stocks (Pelacakan Stok Bahan per Outlet)
CREATE TABLE ingredient_stocks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    outlet_id UUID REFERENCES outlets(id) ON DELETE CASCADE,
    ingredient_id UUID REFERENCES ingredients(id) ON DELETE CASCADE,
    qty_actual DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
    minimum_alert DECIMAL(12,4) NOT NULL DEFAULT 100.0000,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(outlet_id, ingredient_id)
);

-- 10. Tabel Orders
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    seating_session_id UUID REFERENCES seating_sessions(id),
    outlet_id UUID REFERENCES outlets(id),
    order_number VARCHAR(100) UNIQUE NOT NULL,
    status VARCHAR(30) CHECK (status IN ('PENDING', 'KITCHEN_PREP', 'READY_TO_SERVE', 'SERVED', 'CANCELLED', 'PAID')) DEFAULT 'PENDING',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 11. Tabel Order Items (Daftar Menu Terpesan)
CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    menu_variant_id UUID REFERENCES menu_variants(id),
    qty INT NOT NULL,
    selling_price DECIMAL(15,2) NOT NULL,
    customer_notes TEXT,
    kds_status VARCHAR(20) CHECK (kds_status IN ('WAITING', 'COOKING', 'READY', 'SERVED')) DEFAULT 'WAITING',
    kitchen_station VARCHAR(30) DEFAULT 'KITCHEN' -- BAR, KITCHEN, DESSERT
);

-- 12. Tabel Order Item Modifiers (Kustomisasi Item Terpesan)
CREATE TABLE order_item_modifiers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_item_id UUID REFERENCES order_items(id) ON DELETE CASCADE,
    menu_modifier_id UUID REFERENCES menu_modifiers(id),
    qty INT NOT NULL DEFAULT 1
);