WMS_ERD
Tables (18)
Customer
- customer_id — int — Primary key — NOT NULL
- customer_name — varchar(100)
- customer_contact_info — varchar(15)
- customer_address — varchar(50)
- customer_email — varchar(50)
- customer_phone — varchar(15)
- isDeleted — boolean
- created_time — DATETIME
- update_time — DATETIME
Inbound (inbound)
- inbound_id — int — Primary key — NOT NULL
- product_id — int — Foreign key → Product.product_id — NOT NULL
- supplier_id — int — Foreign key → Supplier.supplier_id — NOT NULL
- warehouse_location_id — int — Foreign key → WareHouse_Location.warehouse_location_id — NOT NULL
- warehouse_id — int — Foreign key → WareHouse_Location.warehouse_id — NOT NULL
- quantity — varchar(10)
- lot_number — varchar(255)
- received_date — DATETIME
- status (wait,success) — varchar(10)
- isDeleted — boolean
- created_time — DATETIME
- update_time — DATETIME
- created_by — varchar(50)
- updated_by — varchar(50)
Inventory (inventory)
- inventory_id — int — Primary key — NOT NULL
- product_id — int — Foreign key → Product.product_id — NOT NULL
- warehouse_location_id — int — Foreign key → WareHouse_Location.warehouse_location_id — NOT NULL
- warehouse_id — int — Foreign key → Warehouse.warehouse_id — NOT NULL
- quantity — varchar(10) — NOT NULL
- lot_number — varchar(255)
- received_date — DATETIME
- expiration_date — DATETIME
- isDeleted — boolean
- created_time — DATETIME
- update_time — DATETIME
- created_by — varchar(50)
- updated_by — varchar(50)
InventoryHistory
- inventory_history_id — int — Primary key — NOT NULL
- inventory_id — int — Foreign key → Inventory.inventory_id — NOT NULL
- product_id — int — Foreign key → Inventory.product_id — NOT NULL
- warehouse_location_id — int — Foreign key → Inventory.warehouse_location_id — NOT NULL
- warehouse_id — int — Foreign key → Inventory.warehouse_id — NOT NULL
- isDeleted — boolean
- created_time — DATETIME
- update_time — DATETIME
- created_by — varchar(50)
- updated_by — varchar(50)
- reason (inbound,outbound,modify) — varchar(50)
Notification
- notification_id — int — Primary key — NOT NULL
- user_id — int — Foreign key → User.user_id — NOT NULL
- notification_date — DATETIME
- message — varchar(255)
- status (read,unread) — varchar(6)
Order
- order_id — int — Primary key — NOT NULL
- customer_id — int — Foreign key → Customer.customer_id — NOT NULL
- order_date — varchar(30)
- isDeleted — boolean
- created_time — DATETIME
- update_time — DATETIME
- status (wait,success) — varchar(10)
Order_Detail
- order_detail_id — int — Primary key — NOT NULL
- order_id — int — Foreign key → Order.order_id — NOT NULL
- product_id — int — Foreign key → Product.product_id — NOT NULL
- quantity — varchar(10)
- unit_price — varchar(10)
- total_price (quantity * unit price) — varchar(10)
- created_time — DATETIME
- update_time — DATETIME
Outbound
- outbound_id — int — Primary key — NOT NULL
- product_id — int — Foreign key → Product.product_id — NOT NULL
- order_id — int — Foreign key → Order.order_id — NOT NULL
- warehouse_id — int — Foreign key → Warehouse.warehouse_id — NOT NULL
- warehouse_location_id — int — Foreign key → WareHouse_Location.warehouse_location_id — NOT NULL
- quantity — varchar(10)
- shipped_date — DATETIME
- isDeleted — boolean
- created_time — DATETIME
- update_time — DATETIME
- status (wait,success) — varchar(10)
Permission
- permission_id — int — Primary key — NOT NULL
- permission_name — varchar(50)
- created_time — DATETIME
- update_time — DATETIME
- created_by — varchar(50)
- updated_by — varchar(50)
- description — varchar(255)
PermissionRole
- permission_role_id — int — Primary key — NOT NULL
- permission_id — int — Foreign key → Permission.permission_id — NOT NULL
- role_id — int — Foreign key → Role.role_id — NOT NULL
- created_time — DATETIME
- update_time — DATETIME
- created_by — varchar(50)
- updated_by — varchar(50)
Product (product)
- product_id — int — Primary key — NOT NULL
- product_code — varchar(50)
- product_name — varchar(50) — NOT NULL
- category — varchar(10)
- description — varchar(255)
- isDeleted — boolean
- created_time — DATETIME
- updated_time — DATETIME
Return
- return_id — int — Primary key — NOT NULL
- order_id — int — Foreign key → Order.order_id — NOT NULL
- quantity — varchar(10)
- return_date — varchar(20)
- isDeleted — boolean
- status (wait,success) — varchar(10)
- reason — varchar(255)
- created_time — DATETIME
- update_time — DATETIME
Role
- role_id — int — Primary key — NOT NULL
- role_name (admin,manager,engineer,technician, operator) — varchar(50)
- created_time — DATETIME
- update_time — DATETIME
- created_by — varchar(50)
- updated_by — varchar(50)
Supplier
- supplier_id — int — Primary key — NOT NULL
- supplier_name — varchar(15)
- supplier_contact_Info (contact_Info) — varchar(15)
- supplier_address — varchar(50)
- supplier_email — varchar(50)
- supplier_phone — varchar(15)
- isDeleted — boolean
- created_time — DATETIME
- update_time — DATETIME
User
- user_id — int — Primary key — NOT NULL
- user_name — varchar(50)
- password — varchar(255)
- created_time — DATETIME
- update_time — DATETIME
- isDeleted — boolean
- isLocked — boolean
- email — varchar(50)
- department — varchat(50)
- created_by — varchar(50)
- updated_by — varchat(50)
UserRole
- user_role_id — int — Primary key — NOT NULL
- user_id — int — Foreign key → User.user_id — NOT NULL
- role_id (role_id2) — int — Foreign key → Role.role_id — NOT NULL
- created_time — DATETIME
- update_time — DATETIME
- created_by — varchar(50)
- updated_by — varchar(50)
WareHouse_Location (warehouse_location)
- warehouse_location_id — int — Primary key — NOT NULL
- warehouse_id — int — Foreign key → Warehouse.warehouse_id — NOT NULL
- warehouse_location_zone — varchar(5)
- warehouse_location_row — varchar(5) — NOT NULL
- warehouse_location_shelf — varchar(5)
- warehouse_location_position — varchar(5)
- isAvailable — boolean
- created_time — DATETIME
- updated_time — DATETIME
Warehouse
- warehouse_id — int — Primary key — NOT NULL
- warehouse_code — varchar(50)
- warehouse_name — varchar(50) — NOT NULL
- warehouse_address — varchar(10)
- isDeleted — boolean
- created_time — DATETIME
- updated_time — DATETIME