Schema documentation for Master Data and Operational Tables.
| Column Name | Type | Description |
|---|---|---|
| user_id | INT(11) | Primary Key, Auto Increment |
| username | VARCHAR(50) | Unique Login ID |
| password_hash | VARCHAR(255) | Encrypted Password |
| full_name | VARCHAR(100) | Staff Full Name |
| VARCHAR(100) | For Password Reset | |
| phone_number | VARCHAR(20) | Contact Number |
| profile_image_url | VARCHAR(255) | URL path to user photo |
| role | ENUM | 'Head', 'Manager', 'Purchase', 'Finance', 'Admin' |
| outlet_id | INT(11) | FK to outlets (Optional) |
| created_at | DATETIME | Record creation timestamp |
| Column Name | Type | Description |
|---|---|---|
| vendor_id | INT(11) | Primary Key, Auto Increment |
| vendor_name | VARCHAR(100) | Company Name |
| payment_terms | ENUM | 'CBD', 'COD', 'Credit Terms' |
| credit_term_days | INT(11) | Days for Credit Terms (e.g. 30) |
| bank_name | VARCHAR(50) | Bank Name |
| bank_account_num | VARCHAR(50) | Account Number |
| bank_account_holder | VARCHAR(100) | Account Holder Name |
| address | TEXT | Physical Address (Optional) |
| pic1_name | VARCHAR(100) | 1st PIC Name |
| pic1_position | VARCHAR(50) | 1st PIC Position (e.g. Sales) |
| pic1_phone | VARCHAR(20) | 1st PIC WhatsApp |
| pic2_name | VARCHAR(100) | 2nd PIC Name (Optional) |
| pic2_position | VARCHAR(50) | 2nd PIC Position |
| pic2_phone | VARCHAR(20) | 2nd PIC WhatsApp |
| office_phone | VARCHAR(20) | Office Phone Number (Optional) |
| Column Name | Type | Description |
|---|---|---|
| item_id | INT(11) | Primary Key, Auto Increment |
| sku | VARCHAR(20) | Unique Stock Keeping Unit |
| item_name | VARCHAR(150) | Name of the item |
| image1_url | VARCHAR(255) | Item Picture 1 (Max 3) |
| image2_url | VARCHAR(255) | Item Picture 2 |
| image3_url | VARCHAR(255) | Item Picture 3 |
| category | VARCHAR(50) | e.g. 'Raw Material', 'Packaging' |
| unit | VARCHAR(20) | Inventory Base Unit (e.g. gram, ml, pcs) |
| min_stock_level | DECIMAL(10,2) | Reorder Point |
| storage_section | VARCHAR(50) | e.g. 'Kitchen', 'Bar' (Maps to Head Section) |
| is_active | BOOLEAN | 1 = Active, 0 = Inactive |
| Column Name | Type | Description |
|---|---|---|
| id | INT(11) | Primary Key |
| item_id | INT(11) | FK to items |
| vendor_id | INT(11) | FK to vendors |
| vendor_sku | VARCHAR(50) | Vendor's Item Code |
| uom_purchase | VARCHAR(20) | Purchase Unit (e.g. Pack, Box) |
| conversion_factor | DECIMAL(10,2) | Qty of Base Unit per Purchase Unit |
| price | DECIMAL(15,2) | Price per Purchase Unit |
| is_preferred | BOOLEAN | Auto-set for lowest price |
| last_updated | DATETIME | Last Price Update |
| Column Name | Type | Description |
|---|---|---|
| outlet_id | INT(11) | Primary Key |
| outlet_name | VARCHAR(100) | e.g. 'Pudak', 'Eyckman' |
| address | TEXT | Location |
| Column Name | Type | Description |
|---|---|---|
| id | INT(11) | Primary Key |
| category_name | VARCHAR(50) | 'Regular', 'Buffer', 'Event' |
| description | VARCHAR(255) | Description of the category |
| is_active | BOOLEAN | 1 = Active |
| Column Name | Type | Description |
|---|---|---|
| ro_id | INT(11) | Primary Key |
| ro_number | VARCHAR(20) | e.g. RO-2023-001 |
| outlet_id | INT(11) | FK to outlets |
| created_by | INT(11) | FK to users (Head Section) |
| status | ENUM | 'Draft', 'Pending', 'Approved', 'Processed' |
| created_at | DATETIME | Request Date |
| Column Name | Type | Description |
|---|---|---|
| po_id | INT(11) | Primary Key |
| po_number | VARCHAR(20) | e.g. PO-2023-001 |
| vendor_id | INT(11) | FK to vendors |
| category_id | INT(11) | FK to po_categories |
| total_amount | DECIMAL(15,2) | Grand Total |
| status | ENUM | 'Ordered', 'Received', 'Cancelled' |
| payment_status | ENUM | 'Pending', 'Paid' |
| Column Name | Type | Description |
|---|---|---|
| id | INT(11) | Primary Key |
| outlet_id | INT(11) | FK to outlets |
| item_id | INT(11) | FK to items |
| quantity | DECIMAL(10,2) | Current On-Hand Stock |
| last_updated | DATETIME | Last Movement Timestamp |
| Column Name | Type | Description |
|---|---|---|
| trans_id | INT(11) | Primary Key |
| type | ENUM | 'In', 'Out', 'Adjust', 'Return' |
| item_id | INT(11) | FK to items |
| qty_change | DECIMAL(10,2) | Positive or Negative change |
| reference_id | INT(11) | PO ID, Opname ID, or Usage ID |
| date | DATETIME | Transaction Date |
| Column Name | Type | Description |
|---|---|---|
| return_id | INT(11) | Primary Key |
| po_id | INT(11) | FK to purchase_orders |
| item_id | INT(11) | FK to items |
| quantity | DECIMAL(10,2) | Returned Qty |
| reason | VARCHAR(255) | e.g. 'Damaged', 'Wrong Item' |
| refund_status | ENUM | 'Pending', 'Completed' |
| refund_amount | DECIMAL(15,2) | Amount refunded by vendor |