Inventory Management
Database Design
Designed and created a database in 3NF for inventory management business in automobile components industry via data definition language (DDL) in SQL Server
Maintained and managed data entries in multiple tables via data manipulation language (DML) in SQL Server
Visualized and analyzed over 1,000 inventory data from 2019 to 2020 using Power BI
Background
Inventory Management Database is used to manage large datasets of the flow of goods from manufacturers to warehouses and from facilities to sales points. A key purpose of inventory management database is to keep detailed records of each new or returned product when it enters or leaves a warehouse or sales point. Companies with supply chain businesses are always faced with a huge cost when manipulating data of logistics. Besides, many of the hard copies of the records could be damaged resulting in information loss. Inventory Management System would digitalize entire records and manipulate them efficiently.
Keywords: Database Design, Inventory Management, SQL Server, DDL, DML
Introduction
Inventory Management System is a system where one can track the number of sales and purchases of the goods. It helps to reduce the amount of time required for processing shipment. Furthermore, the scope of this project is to improve cash flow by managing stock requirements in real-time and increasing inventory efficiency. We want our system to efficiently oversee the constant flow of units in and out of inventory. This process usually involves controlling the transfer of units to prevent the inventory from becoming too high or dwindling to levels that could put the efficiency of operation into jeopardy.
Mission Statement
Synchronize orders with inventory and increase business efficiency from order creation to fulfillment.
Access and generate accurate sales reports that offer insights for better business decisions in real-time.
Require less paperwork and more accountability amongst sections of the organization.
Keep all customer and supplier data in a safe place.
Avoid inventory over-running that could result in loss.
Requirement 1: There is one-to-many relationship between CUSTOMER and ORDER.
CREATE TABLE [CUSTOMER] (
[CustomerID] INT NOT NULL IDENTITY(10001,1),
[FirstName] VARCHAR(50) NULL,
[LastName] VARCHAR(50) NULL,
[Contact] VARCHAR(50) NULL,
[Address] VARCHAR(100) NULL,
CONSTRAINT CUSTOMER_PK PRIMARY KEY ([CustomerID])
);
CREATE TABLE [ORDER] (
[OrderID] INT NOT NULL IDENTITY(2001,1),
[CustomerID] INT NULL,
[OrderTime] DATETIME,
[ExpectedDeliveryTime] AS DATEADD(DAY,3,[OrderTime]),
CONSTRAINT ORDER_PK PRIMARY KEY ([OrderID]),
);
CREATE INDEX [ORDER_FK] ON [ORDER] ([CustomerID]);
ALTER TABLE [ORDER] ADD CONSTRAINT ORDER_FK FOREIGN KEY ([CustomerID]) REFERENCES [Customer](CustomerID);
Customer Entity: CustomerID, FirstName, LastName, Contact, Address
Order Entity: OrderID, CustomerID, OrderTime
Business Requirements:
- One customer may have multiple orders.
- Order should be finished in 3 days.
Requirement 2: There is one-to-many relationship between WAREHOUSE and EMPLOYEE.
CREATE TABLE [WAREHOUSE] (
[WarehouseID] VARCHAR(50) NOT NULL,
[Address] VARCHAR(100) NULL,
CONSTRAINT WAREHOUSE_PK PRIMARY KEY ([WarehouseID])
);
CREATE TABLE [EMPLOYEE] (
[EmployeeID] INT NOT NULL IDENTITY(45001,1)
CONSTRAINT EMPLOYEE_CHK CHECK (EmployeeID > 45000 AND EmployeeID < 46001),
[FirstName] VARCHAR(50) NULL,
[LastName] VARCHAR(50) NULL,
[Contact] VARCHAR(50) NULL,
[WarehouseID] VARCHAR(50) NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY ([EmployeeID])
);
CREATE INDEX [EMPLOYEE_FK] ON [EMPLOYEE] ([WarehouseID]);
ALTER TABLE [EMPLOYEE] ADD CONSTRAINT EMPLOYEE_FK FOREIGN KEY ([WarehouseID]) REFERENCES [WAREHOUSE](WarehouseID);
Warehouse Entity: WarehouseID, Address
Employee Entity: Employee ID, FirstName, LastName, Contact, WarehouseID
Business Requirements:
- One warehouse may have multiple employees, one employee may not work in different warehouses.
- EmployeeID should be in [45001, 46000].
Requirement 3: There is binary many-to-many relationship between ORDER and PRODUCT associated by ORDER LINE.
CREATE TABLE [PRODUCT] (
[ProductID] VARCHAR(50) NOT NULL,
[Name] VARCHAR(50) NULL,
[Price] NUMERIC(10,2) NULL,
[Description] VARCHAR(250) NULL,
CONSTRAINT PRODUCT_PK PRIMARY KEY ([ProductID])
);
CREATE TABLE [ORDERLINE] (
[OrderID] INT NOT NULL,
[ProductID] VARCHAR(50) NOT NULL,
[Amount] NUMERIC(10,2) NULL,
[DeliveryTime] DATETIME,
CONSTRAINT ORDERLINE_PK PRIMARY KEY ([OrderID], [ProductID])
);
CREATE INDEX [ORDERLINE_PK_FK1] ON [ORDERLINE] ([OrderID]);
CREATE INDEX [ORDERLINE_PK_FK2] ON [ORDERLINE] ([ProductID]);
ALTER TABLE [ORDERLINE] ADD CONSTRAINT ORDERLINE_FK1 FOREIGN KEY ([OrderID])
REFERENCES [ORDER](OrderID);
ALTER TABLE [ORDERLINE] ADD CONSTRAINT ORDERLINE_FK2 FOREIGN KEY ([ProductID])
REFERENCES [PRODUCT](ProductID);
Business Requirements:
- Same product may be ordered by different customers.
- One order may contain multiple products.
Product Entity: ProductID, Name, Price, Description
Order Line Entity: OrderID, Product ID, Amount, DeliveryTime
Requirement 4: There is binary many-to-many relationship between PRODUCT and ITEM associated by ASSEMBLY.
CREATE TABLE [ITEM] (
[ItemID] VARCHAR(50) NOT NULL,
[Name] VARCHAR(50) NULL,
[Price] NUMERIC(10,2) NULL,
[Description] VARCHAR(250) NULL,
CONSTRAINT ITEM_PK PRIMARY KEY ([ItemID])
);
CREATE TABLE [ASSEMBLY] (
[ProductID] VARCHAR(50) NOT NULL,
[ItemID] VARCHAR(50) NOT NULL,
[Amount] NUMERIC(10,2) NULL,
CONSTRAINT ASSEMBLY_PK PRIMARY KEY ([ProductID], [ItemID])
);
CREATE INDEX [ASSEMBLY_PK_FK1] ON [ASSEMBLY] ([ProductID]);
CREATE INDEX [ASSEMBLY_PK_FK2] ON [ASSEMBLY] ([ItemID]);
ALTER TABLE [ASSEMBLY] ADD CONSTRAINT ASSEMBLY_FK1 FOREIGN KEY ([ProductID]) REFERENCES [PRODUCT](ProductID);
ALTER TABLE [ASSEMBLY] ADD CONSTRAINT ASSEMBLY_FK2 FOREIGN KEY ([ItemID]) REFERENCES [ITEM](ItemID);
Business Requirements:
- One product may contain multiple items.
- Same item may be component of different products.
Item Entity: ItemID, Name, Price, Description
Assembly Entity: ProductID, ItemID, Amount
Requirement 5: There is ternary many-to-many relationship among SUPPLIER, EMPLOYEE and ITEM associated by SUPPLY.
CREATE TABLE [SUPPLIER] (
[SupplierID] INT NOT NULL IDENTITY(76001,1),
[FirstName] VARCHAR(50) NULL,
[LastName] VARCHAR(50) NULL,
[Contact] VARCHAR(50) NULL,
[Address] VARCHAR(100) NULL,
CONSTRAINT SUPPLIER_PK PRIMARY KEY ([SupplierID])
);
CREATE TABLE [SUPPLY] (
[SupplierID] INT NOT NULL,
[ItemID] VARCHAR(50) NOT NULL,
[EmployeeID] INT NOT NULL,
[SupplyTime] DATETIME,
[Amount] NUMERIC(10,2) NULL,
CONSTRAINT SUPPLY_PK PRIMARY KEY ([SupplierID], [ItemID], [EmployeeID])
);
CREATE INDEX [SUPPLY_PK_FK1] ON [SUPPLY] ([SupplierID]);
CREATE INDEX [SUPPLY_PK_FK2] ON [SUPPLY] ([ItemID]);
CREATE INDEX [SUPPLY_PK_FK3] ON [SUPPLY] ([EmployeeID]);
ALTER TABLE [SUPPLY] ADD CONSTRAINT SUPPLY_FK1 FOREIGN KEY ([SupplierID]) REFERENCES [SUPPLIER](SupplierID);
ALTER TABLE [SUPPLY] ADD CONSTRAINT SUPPLY_FK2 FOREIGN
KEY ([ItemID]) REFERENCES [ITEM](ItemID);
ALTER TABLE [SUPPLY] ADD CONSTRAINT SUPPLY_FK3 FOREIGN KEY ([EmployeeID]) REFERENCES [EMPLOYEE](EmployeeID);
Supplier Entity: SupplierID, FirstName, LastName, Contact, Address
Supply Entity: SupplierID, ItemID, EmployeeID, SupplyTime, Amount
Business Requirements:
- One employee may connect with multiple suppliers, and vice versa.
- One supplier may supply multiple items.
- Same item can be supplied by different suppliers.
Requirement 6: There is binary many-to-many relationship between WAREHOUSE and ITEM associated by STOCK.
CREATE TABLE [STOCK] (
[WarehouseID] VARCHAR(50) NOT NULL,
[ItemID] VARCHAR(50) NOT NULL,
[Quantity] NUMERIC(10,2) NULL,
CONSTRAINT STOCK_PK PRIMARY KEY ([WarehouseID], [ItemID])
);
CREATE INDEX [STOCK_PK_FK1] ON [STOCK] ([WarehouseID]);
CREATE INDEX [STOCK_PK_FK2] ON [STOCK] ([ItemID]);
ALTER TABLE [STOCK] ADD CONSTRAINT STOCK_FK1 FOREIGN KEY ([WarehouseID]) REFERENCES [WAREHOUSE](WarehouseID);
ALTER TABLE [STOCK] ADD CONSTRAINT STOCK_FK2 FOREIGN KEY ([ItemID]) REFERENCES [ITEM](ItemID);
Stock Entity: WarehouseID, ItemID, Quantity
Business Requirements:
- One warehouse can store mutiple items.
- Same item can be stored in different warehouse.
Requirement 7: There is binary many-to-many relationship between WAREHOUSE and PRODUCT associated by LOCATION.
CREATE TABLE [LOCATION] (
[WarehouseID] VARCHAR(50) NOT NULL,
[ProductID] VARCHAR(50) NOT NULL,
[Quantity] NUMERIC(10,2) NULL,
CONSTRAINT LOCATION_PK PRIMARY KEY ([WarehouseID], [ProductID])
);
CREATE INDEX [LOCATION_PK_FK1] ON [LOCATION] ([WarehouseID]);
CREATE INDEX [LOCATION_PK_FK2] ON [LOCATION] ([ProductID]);
ALTER TABLE [LOCATION] ADD CONSTRAINT LOCATION_FK1 FOREIGN KEY ([WarehouseID]) REFERENCES [WAREHOUSE](WarehouseID);
ALTER TABLE [LOCATION] ADD CONSTRAINT LOCATION_FK2 FOREIGN KEY ([ProductID]) REFERENCES [PRODUCT](ProductID);
Location Entity: WarehouseID, ProductID, Quantity
Business Requirements:
- One warehouse can store multiple products.
- Same product can be stored in different warehouses.
Description of Inventory Management Database
- All the entities are in 3NF.
- Each entity contains primary key and unique keys.
- We have implemented indexing on each table of Inventory Management Database for fast query execution.
