erDiagram
COUNTRIES ||--o{ PROVINCES : "contains"
PROVINCES ||--o{ DISTRICTS : "contains"
DISTRICTS ||--o{ SUB_DISTRICTS : "contains"
PROVINCES ||--o{ BRANCHES : "locates"
DISTRICTS ||--o{ BRANCHES : "locates"
SUB_DISTRICTS ||--o{ BRANCHES : "locates"
ITEM_CATEGORIES ||--o{ PRODUCTS : "classifies"
ITEM_UNITS ||--o{ PRODUCTS : "measures"
BRANCHES ||--o{ EMPLOYEES : "employs"
BRANCHES ||--o{ BRANCH_TARGETS : "sets_target"
EMPLOYEES ||--o{ SALESPERSON_COMMISSION_CONFIGS : "configures"
EMPLOYEES ||--o{ USER_ACCOUNTS : "owns"
USER_ACCOUNTS ||--o{ USER_SESSIONS : "authenticates"
USER_ACCOUNTS ||--o{ SYSTEM_AUDIT_LOGS : "acts"
ROLES ||--o{ ROLE_PERMISSIONS : "defines"
PERMISSIONS ||--o{ ROLE_PERMISSIONS : "grants"
BRANCHES ||--o{ SALES_TRANSACTIONS : "records"
EMPLOYEES ||--o{ SALES_TRANSACTIONS : "sells"
SALES_TRANSACTIONS ||--|{ SALES_TRANSACTION_ITEMS : "contains"
PRODUCTS ||--o{ SALES_TRANSACTION_ITEMS : "sold_in"
EMPLOYEES ||--o{ MONTHLY_COMMISSIONS : "earns"
COUNTRIES {
int country_id PK
string country_code
string country_name
}
PROVINCES {
int province_id PK
int country_id FK
string province_code
string province_name_th
}
DISTRICTS {
int district_id PK
int province_id FK
string district_code
string district_name_th
}
SUB_DISTRICTS {
int sub_district_id PK
int district_id FK
string sub_district_name_th
string zipcode
}
ITEM_CATEGORIES {
int category_id PK
string category_code
string category_name
}
ITEM_UNITS {
int unit_id PK
string unit_code
string unit_name
}
BRANCHES {
int branch_id PK
string branch_code "BR-01..10"
string branch_name
string address_line
int province_id FK
int district_id FK
int sub_district_id FK
string zipcode
string phone
}
PRODUCTS {
int product_id PK
string product_code "Item-01..20"
string product_name
int category_id FK
int unit_id FK
decimal standard_cost_pct "35%..70%"
decimal base_price
}
EMPLOYEES {
int employee_id PK
int branch_id FK
string employee_code "ID-001..010"
string full_name
string role
}
SALES_TRANSACTIONS {
int transaction_id PK
int branch_id FK
int employee_id FK
string invoice_no "Header"
date transaction_date
decimal total_amount
decimal total_cost
decimal total_margin
}
SALES_TRANSACTION_ITEMS {
int item_id PK
int transaction_id FK
int product_id FK
int quantity
decimal unit_price
decimal standard_cost_pct
decimal subtotal_margin
}
MONTHLY_COMMISSIONS {
int commission_id PK
int employee_id FK
int period_year
int period_month
decimal total_sales_amount
decimal total_margin_amount
decimal commission_rate_pct
decimal calculated_commission "Margin * Rate %"
string approval_status "APPROVED/PENDING"
}