the boxes

Tables (5)

history

  • history_id — INT — Primary key
  • created_at — TIMESTAMP
  • user_id — INT — Foreign key → users.user_id
  • item_id — INT — Foreign key → item.item_id
  • in_out_id — INT — Foreign key → in_out.in_out_id

in_out

  • in_out_id — INT — Primary key
  • order_type — ENUM('INCOMING', 'OUTGOING') — NOT NULL
  • quantity — INT — NOT NULL
  • status — ENUM('PENDING', 'APPROVED', 'SHIPPED', 'DELIVERED') — NOT NULL
  • created_at — TIMESTAMP
  • user_id — INT — Foreign key → users.user_id
  • item_id — INT — Foreign key → item.item_id
  • change_type — ENUM('STOCK_ADDITION', 'STOCK_REMOVAL', 'DAMAGED', 'OTHER') — NOT NULL
  • reason — VARCHAR(100)

inventory

  • inventory_id — INT — Primary key
  • previous_quantity — INT
  • current_quantity — INT
  • created_at — TIMESTAMP
  • user_id — INT — Foreign key → users.user_id
  • item_id — INT — Foreign key → item.item_id

item

  • item_id — INT — Primary key
  • item_name — VARCHAR(100) — NOT NULL
  • manufacturer — VARCHAR(100)
  • item_location — VARCHAR(100)
  • classification — VARCHAR(100)
  • status — ENUM('IN_USE', 'NOT_IN_USE') — NOT NULL
  • created_at — TIMESTAMP
  • user_id — INT — Foreign key → users.user_id

users

  • user_id — INT — Primary key
  • username — VARCHAR(50) — NOT NULL
  • password — VARCHAR(60) — NOT NULL
  • name — VARCHAR(20) — NOT NULL
  • birthdate — DATE
  • phone — VARCHAR(20)
  • address — VARCHAR(255)
  • email — VARCHAR(100) — NOT NULL
  • position — ENUM('ADMIN', 'MANAGER', 'EMPLOYEE', 'INTERN')
  • status — ENUM('WORKING', 'RETIRED') — NOT NULL
  • created_at — TIMESTAMP