# 9. Konsep Skema Database

## Konvensi

- Tabel: **plural**, snake_case (`orders`, `order_items`).
- Primary key: `id` (bigint, auto increment).
- Foreign key: `{table_singular}_id` (e.g. `order_id`, `outlet_id`).
- Wajib: `created_at`, `updated_at`; soft delete: `deleted_at`.
- Multi-tenant/outlet: kolom `outlet_id` di tabel operasional.

## Tabel Inti (Konseptual)

### Auth & User

- `users` — id, name, email, password, role_id, ...
- `roles` — id, name, slug
- `permissions` — id, name, slug
- `role_permission` — role_id, permission_id
- `user_outlets` — user_id, outlet_id (multi-outlet access)

### Outlet

- `outlets` — id, name, address, timezone, currency, ...
- `outlet_settings` — outlet_id, key, value (atau kolom terpisah)
- `outlet_devices` — outlet_id, type (printer/kds), name, config (JSON)

### Menu

- `categories` — id, outlet_id, name, sort_order
- `products` — id, outlet_id, category_id, name, price, is_available, ...
- `modifiers` — id, product_id, name, price
- `ingredients` — id, outlet_id, name, unit
- `recipes` — product_id, ingredient_id, quantity (BOM)

### Order (Core)

- `orders` — id, outlet_id, order_number, type (dine_in/takeaway/delivery), status, table_no, customer_id, subtotal, tax, total, created_at, ...
- `order_items` — id, order_id, product_id, qty, unit_price, modifiers (JSON atau detail table)
- `order_payments` — id, order_id, method, amount, reference, paid_at
- `order_status_logs` — order_id, status, user_id, created_at

### POS

- `pos_sessions` — id, outlet_id, user_id, opened_at, closed_at, opening_cash, closing_cash
- `payment_methods` — id, outlet_id, name, type (cash/card/transfer)
- `transactions` — id, pos_session_id, order_id, amount, method_id, ...

### Index Rekomendasi

- `orders(outlet_id, created_at)`, `orders(status)`, `orders(order_number)`
- `order_items(order_id)`
- `products(outlet_id, category_id)`, `products(is_available)`
- `user_outlets(user_id, outlet_id)`

## Relasi

- Order → Outlet (belongsTo); Order → OrderItems (hasMany); Order → OrderPayments (hasMany).
- Product → Category (belongsTo); Product → Modifiers (hasMany).
- User ↔ Outlets (many-to-many via user_outlets).
