Relational Supplier-Inventory Database

SQL
Project Overview
This project revolves around designing a  SQL database system with SQL Server to change on how a construction manufacturing company manages its supplier relationships and inventory across multiple production units.
Excel meme
Background
This idea came to me in 2020 when I was trying to manage supplier data and keeping track of inventory of each production unit. It was a real headache when the entire company used was Excel. Don't get me wrong —I love Excel as much as anyone. However, maintaining updated and accurate with spreadsheets, especially as the company grow was a huge task. That whole process was slow, prone to errors, and a bit of a nightmare. Furthermore, the construction industry itself has been notably slow in adopting digital transformation.

Since I've always been keen on making things more digital and efficient, I started to think about how I could make life easier. That's when I stumbled upon the power of SQL. I realized that by creating a dedicated database, I could streamline everything. No more messy spreadsheets, just a sleek, scalable system that could grow with the company and make our data management a breeze. That's why I decided to design a database that could handle all the supplier and inventory needs more effectively.
Database Design
The idea is to allow users to efficiently manage suppliers, materials, contracts, and inventory across multiple production units. Using Excel to perform such task would take a lot of time due to the enormous file sizes. Opening the file itself would slow down their personal computer and thus hampering productivity and increasing the risk of errors. To address these challenges,  developing a robust relational database is needed. This database design adheres to normalization principles to reduce redundancy and improve data integrity. Therefore, each piece of information is stored only once, minimizing duplication and potential inconsistencies.
Detail of Entities and Attributes of the Database
Entities (tables) serve as the building blocks of the database, representing the fundamental objects or concepts that need to be stored and managed. This database contains 5 entities (supplier, material, production_unit, contract, and unit_inventory) where each entity has it owns attributes (columns) that define the characteristics or properties the entity.
supplier
The purpose of this table is to store and manage data related to all suppliers the company deals with, facilitating contact and transaction management.
Attributes
Description
supplier_id (INT, Primary Key)
A unique identifier for each supplier.
company_name (VARCHAR(150))
The name of the supplier company.
address (VARCHAR(255))
The street address of the supplier.
city (VARCHAR(150))
The city where supplier is located.
province (VARCHAR(100))
The province where supplier is located.
postal_code (VARCHAR(20))
The postal code for the supplier's location.
country (VARCHAR(100))
The country where the supplier is based.
material
This table holds detailed descriptions of each material that the company uses or provides, crucial for inventory management and procurement.
Attributes
Description
material_id (INT, Primary Key)
A unique identifier for each material.
name (VARCHAR(150))
The name of the material.
description (VARCHAR(255))
A detailed description of the material.
unit (VARCHAR(50))
The unit of measure used for the material (e.g., kilograms, cubic meters).
production_unit
This table stores information about each production or storage location, which is essential for logistics, inventory management, and fulfilling orders specific to locations.
Attributes
Description
unit_id (INT, Primary Key)
A unique identifier for each production unit.
unit_name (VARCHAR(150))
The name of the production unit.
address (VARCHAR(255))
The street address of the production unit.
city (VARCHAR(100))
The city where the production unit is located.
province (VARCHAR(100))
The province where the production unit is located.
postal_code (VARCHAR(20))
The postal code for the production unit's location.
country (VARCHAR(100))
The country where the production unit is based.
contract
A table of combination of elements that can be found in a supply agreement with contractual obligations. It tracks the supply of materials under specific contracts, including quantities, prices, and total contract value, which is calculated from the price per unit and quantity.
Attributes
Description
contract_id (INT, Primary Key)
A unique identifier for each contract.
supplier_id (INT, Foreign Key)
Links to supplier_id in the Supplier table.
material_id (INT, Foreign Key)
Links to material_id in the Material table.
unit_id (INT, Foreign Key)
Links to unit_id in the Production Unit table.
quantity (DECIMAL(18,2))
The amount of material supplied.
price_per_unit (DECIMAL(18,2))
The cost per unit of the material.
contract_value (DECIMAL(18,2))
quantity * price_per_unit
start_date (DATE)
The date on which the contract begins.
end_date (DATE)
The date on which the contract is set to expire.
supply_date (DATE)
The date on which the material was supplied.
status (VARCHAR(50))
The current status of the contract (e.g Active, Completed, Cancelled)
orders
This table records every order under a certain contract for every type of material across production unit.
Attributes
Description
order_id (INT, Primary Key)
A unique identifier for each order.
contract_id (INT, Foreign Key)
Links to contract_id in the Contract table.
unit_id (INT, Foreign Key)
Links to unit_id in the Production Unit table.
order_date (DATE)
The date the order was placed.
required_date (DATE)
The date by which the order is required.
shipped_date (DATE)
The date the order was shipped.
status (VARCHAR(50)
The current status of the order (e.g., Pending, Completed, Cancelled).
unit_inventory
This table is critical for real-time inventory management at each production unit, tracking how much of each material is available at any given time.
Attributes
Description
unit_id (INT, Foreign Key)
Links to unit_id in the Production Unit table.
material_id (INT, Foreign Key)
Links to material_id in the Material table.
quantity (DECIMAL (18,2)
The current quantity of the material in inventory.
last_updated (DATE)
The date on which the inventory was last updated.
SQL Script of Entities Structures
-- Creating the Supplier table
CREATE TABLE supplier (
    supplier_id INT PRIMARY KEY,
    company_name VARCHAR(150),
    address VARCHAR(255),
    city VARCHAR(100),
    province VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100),
)

-- Creating the Material table
CREATE TABLE material (
    material_id INT PRIMARY KEY,
    material_name VARCHAR(150),
    description VARCHAR(255),
    unit VARCHAR(50)
)

-- Creating the Production Unit table
CREATE TABLE production_unit (
    unit_id INT PRIMARY KEY,
    unit_name VARCHAR(150),
    address VARCHAR(255),
    city VARCHAR(100),
    province VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100)
)

-- Creating the Contract table
CREATE TABLE contract (
    contract_id INT PRIMARY KEY,
    supplier_id INT,
    material_id INT,
    unit_id INT,
    quantity DECIMAL(18, 2),
    price_per_unit DECIMAL(18, 2),
    contract_value AS (quantity * price_per_unit),
    start_date DATE,
    end_date DATE,
    supply_date DATE,
    status VARCHAR(50),
    FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id),
    FOREIGN KEY (material_id) REFERENCES material(material_id),
    FOREIGN KEY (unit_id) REFERENCES production_unit(unit_id)
)

-- Creating the Orders table
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    contract_id INT,
    unit_id INT,
    order_date DATE,
    required_date DATE,
    shipped_date DATE,
    status VARCHAR(50),
    FOREIGN KEY (contract_id) REFERENCES contract(contract_id),
    FOREIGN KEY (unit_id) REFERENCES production_unit(unit_id)
)

-- Creating the Unit Inventory table
CREATE TABLE unit_inventory (
    inventory_id INT PRIMARY KEY,
    unit_id INT,
    material_id INT,
    quantity DECIMAL(18, 2),
    last_updated DATE,
    FOREIGN KEY (unit_id) REFERENCES production_unit(unit_id),
    FOREIGN KEY (material_id) REFERENCES material(material_id)
);
Database Model
Inserting Data
To interact with the database and insert the data, I made an SQL query available on my GitHub repository. Please kindly check it out!
Conclusion
Overall, designing this relational database not only addresses the immediate needs of managing complex data in a manufacturing environment but also sets a standardization of its company's data. This database also anticipate future expansion, both in terms of scaling up operations and integrating with more advanced technologies such as ERP systems or other analytic tools.
Powered by Webflow
LINKEDIN
GITHUB