APS2

Tables (13)

Categories

  • Category_ID — INT
  • Category_Name — VARCHAR(100) — NOT NULL
  • Description — TEXT
  • Parent_Category_ID — INT
  • Level — INT

Customers

  • Customer_ID — INT
  • First_Name — VARCHAR(50) — NOT NULL
  • Last_Name — VARCHAR(50) — NOT NULL
  • Email — VARCHAR(100)
  • Phone_Number — VARCHAR(20)
  • Billing_Address — JSON
  • Shipping_Address — JSON
  • Registration_Date — TIMESTAMP
  • Loyalty_Points — INT
  • VIP_Status — BOOLEAN

Employees

  • Employee_ID — INT
  • Name — VARCHAR(100)
  • Position — VARCHAR(50)
  • Contact_Info — VARCHAR(100)
  • Assigned_Resource — INT
  • Assigned_Warehouse — INT

Inventory

  • Inventory_ID — INT
  • Product_ID — INT
  • Warehouse_ID — INT
  • Quantity — INT — NOT NULL
  • Minimum_Stock_Level — INT
  • Last_Updated — TIMESTAMP
  • Reorder_Point — INT
  • Reorder_Quantity — INT

Order_Details

  • Detail_ID — INT
  • Order_ID — INT
  • Product_ID — INT
  • Quantity — INT — NOT NULL
  • Unit_Price — DECIMAL(10, 2) — NOT NULL
  • Discount_Percent — DECIMAL(5, 2)
  • Line_Total — DECIMAL(12, 2)

Orders

  • Order_ID — INT
  • Customer_ID — INT
  • Order_Date — DATETIME
  • Required_Date — DATETIME
  • Shipped_Date — DATETIME
  • Shipping_Method_ID — INT
  • Total_Amount — DECIMAL(12, 2)
  • Discount_Amount — DECIMAL(12, 2)
  • Final_Amount — DECIMAL(12, 2)
  • Status — ENUM('Pending', 'Processed', 'Shipped', 'Cancelled')

Payments

  • Payment_ID — INT
  • Order_ID — INT
  • Payment_Method — ENUM('Credit Card', 'Bank Transfer', 'PayPal', 'Cash') — NOT NULL
  • Payment_Date — DATETIME
  • Amount — DECIMAL(12, 2) — NOT NULL
  • Status — ENUM('Pending', 'Completed', 'Failed')

Products

  • Product_ID — INT
  • Product_Name — VARCHAR(100) — NOT NULL
  • Product_Description — TEXT
  • Category_ID — INT
  • SKU_Code — VARCHAR(50)
  • Unit_Price — DECIMAL(12, 2) — NOT NULL
  • Manufacture_Date — DATE
  • Expiry_Date — DATE
  • Weight — DECIMAL(10, 2)
  • Dimensions — JSON

Resources

  • Resource_ID — INT
  • Resource_Name — VARCHAR(100)
  • Resource_Type — ENUM('Machine', 'Operator', 'Vehicle') — NOT NULL
  • Capacity — INT
  • Status — ENUM('Available', 'In Use', 'Under Maintenance')
  • Location — VARCHAR(100)

Schedules

  • Schedule_ID — INT
  • Task_ID — INT
  • Resource_ID — INT
  • Start_Time — DATETIME — NOT NULL
  • End_Time — DATETIME — NOT NULL
  • Status — ENUM('Scheduled', 'In Progress', 'Completed')

Shipping_Methods

  • Shipping_Method_ID — INT
  • Method_Name — VARCHAR(50) — NOT NULL
  • Description — TEXT
  • Cost — DECIMAL(10, 2)

Tasks

  • Task_ID — INT
  • Order_ID — INT
  • Task_Name — VARCHAR(100)
  • Task_Type — ENUM('Assembly', 'Packaging', 'Quality Check')
  • Estimated_Duration — TIME
  • Actual_Duration — TIME
  • Priority_Level — ENUM('Low', 'Medium', 'High')
  • Status — ENUM('Pending', 'In Progress', 'Completed')

Warehouses

  • Warehouse_ID — INT
  • Warehouse_Name — VARCHAR(100)
  • Location — VARCHAR(100)
  • Size — VARCHAR(50)
  • Temperature_Control — BOOLEAN
  • Manager_ID — INT