-- 1. Warehouse Table
CREATE TABLE Warehouse (
Warehouse_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Location VARCHAR(150),
Capacity INT
);
-- 2. Item Table
CREATE TABLE Item (
Item_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Description TEXT,
Unit VARCHAR(50),
Category VARCHAR(100),
Reorder_Level INT
);
-- 3. Supplier Table
CREATE TABLE Supplier (
Supplier_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Contact VARCHAR(100),
Address VARCHAR(200)
);
-- 4. Customer Table
CREATE TABLE Customer (
Customer_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Contact VARCHAR(100),
Address VARCHAR(200)
);
-- 5. Stock Table
CREATE TABLE Stock (
Stock_ID INT PRIMARY KEY,
Warehouse_ID INT,
Item_ID INT,
Quantity INT NOT NULL,
Last_Updated DATE,
FOREIGN KEY (Warehouse_ID) REFERENCES Warehouse(Warehouse_ID),
FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)
);
-- 6. Purchase Order Tables
CREATE TABLE PurchaseOrder (
PO_ID INT PRIMARY KEY,
Supplier_ID INT,
Date DATE,
Status VARCHAR(50),
FOREIGN KEY (Supplier_ID) REFERENCES Supplier(Supplier_ID)
);
CREATE TABLE PurchaseOrderDetail (
PO_ID INT,
Item_ID INT,
Quantity INT,
Price DECIMAL(10,2),
PRIMARY KEY (PO_ID, Item_ID),
FOREIGN KEY (PO_ID) REFERENCES PurchaseOrder(PO_ID),
FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)
);
-- 7. Sales Order Tables
CREATE TABLE SalesOrder (
SO_ID INT PRIMARY KEY,
Customer_ID INT,
Date DATE,
Status VARCHAR(50),
FOREIGN KEY (Customer_ID) REFERENCES Customer(Customer_ID)
);
CREATE TABLE SalesOrderDetail (
SO_ID INT,
Item_ID INT,
Quantity INT,
Price DECIMAL(10,2),
PRIMARY KEY (SO_ID, Item_ID),
FOREIGN KEY (SO_ID) REFERENCES SalesOrder(SO_ID),
FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID)
);
-- 8. Stock Movement Table
CREATE TABLE StockMovement (
Movement_ID INT PRIMARY KEY,
Item_ID INT,
Warehouse_ID INT,
Date DATE,
Movement_Type VARCHAR(10) CHECK (Movement_Type IN ('IN','OUT')),
Quantity INT,
Ref_Order_ID INT,
FOREIGN KEY (Item_ID) REFERENCES Item(Item_ID),
FOREIGN KEY (Warehouse_ID) REFERENCES Warehouse(Warehouse_ID)
);
