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