Database Design Project: Building A Business Database from Scratch

9 min readAug 1, 2023

Project Successfully Completed ✅

Before I dissect this project, if you have no idea on what Database Design is all about, I would recommend you read up HERE so you can follow me along.

So Why is DATABASE DESIGN really important

Picture this: databases are like the rock-solid backbone of any business. Think of them as the superheroes keeping everything together! When these database superheroes are designed and maintained with care, businesses flourish like never before. It’s like having a magic wand that saves time and effort in the long run. So, nailing that perfect database design is like laying the groundwork for a smooth and successful business journey. It’s all about starting strong and saving the day!

So let’s dive into the project

Project Overview

Recently, a business owner approached me seeking assistance in creating a brand-new database for their company.

They needed a database built from scratch to efficiently manage their business operations. I gladly accepted the challenge and embarked on the journey of designing a robust and tailored database solution to meet their unique needs.

The business data is currently on an excel file. You can download the Excel file here. A snapshot of the data is seen below. The data has 31 columns and 1000 rows. The Database Management System used for this project is MySQL (MySQL WorkBench).

After reviewing the data structure and the steps needed to help the business, I have broken down these steps into the following steps

Database Creation & Data Loading
Normalization and Denormalization
Database Diagram Design
Table Alterations
Views, Triggers, and Stored Procedures
User Management and Privileges
Database Backup

Database Creation & Data Loading

The first step I took was to create a database and load the data into my database. The database name of choice is db_northwind

The SQL script below was used to create the database

-- Dropping the database if it exists 
DROP DATABASE IF EXISTS db_northwind;

-- creating the database using character encoding of utf-8
CREATE DATABASE db_northwind DEFAULT CHARACTER SET utf8mb4;

-- to check if the database was created 
SHOW DATABASES;

-- to make the db_northwind database the active database 
USE db_northwind;

At this stage, the database to hold the business data has been created and the next step is to get the dataset into the database.

This can be achieved in two different ways and they are

Using the Table Import wizard on MySQL Workbench to import the data
Using LOAD DATA INFILE on MySQL Shell to load the data into a table

To use the second method, first step would be to clean the data in the excel file and save it as a CSV file.

Cleaning steps involves

Ensuring Date types are in “yyyy-mm-dd”
Ensuring the right number format is used. Presence of currency symbols or comma separators or any symbols are not allowed

After this had been done, the following code was used

-- To drop the table if it exists
DROP TABLE IF EXISTS TblNorthwind;

-- TO create the table for the northwind data
CREATE TABLE TblNorthwind (
    orderID INT,
    customerID INT,
    employeeID INT,
    orderDate DATE,
    requiredDate DATE,
    shippedDate DATE,
    shipVia INT,
    Freight DECIMAL(10,2),
    productID INT,
    unitPrice DECIMAL(10,2),
    quantity INT,
    discount DECIMAL(4,2),
    companyName VARCHAR(255),
    contactName VARCHAR(255),
    contactTitle VARCHAR(255),
    lastName VARCHAR(255),
    firstName VARCHAR(255),
    title VARCHAR(255),
    productName VARCHAR(255),
    supplierID INT,
    categoryID INT,
    quantityPerUnit VARCHAR(255),
    product_unitPrice DECIMAL(10,2),
    unitsInStock INT,
    unitsOnOrder INT,
    reorderLevel INT,
    discontinued TINYINT,
    categoryName VARCHAR(255),
    supplier_CompanyName VARCHAR(255),
    supplier_ContactName VARCHAR(255),
    supplier_ContactTitle VARCHAR(255)
);

-- INSERTING DATA INTO THE CREATED TABLE
LOAD DATA LOCAL INFILE 'C:/Users/Data Analytics/MySQL DBA/Northwind.csv' 
          -- table path
INTO TABLE TblNorthwind
FIELDS TERMINATED BY ',' -- for a csv file
ENCLOSED BY '"' -- for the strings
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- ignore the headers

-- to check the table for the loaded data
SELECT *
FROM TblNorthwind;

NB: This would only work if you have access is enabled on the User and clients side.

I ran into some issues with getting access so I used the Table Import Wizard.

The table was successfully imported into the db_northwind database created on MySQL Workbench.

Data Normalization

After observing the imported data, I noticed the data is denormalized. This brings me to my next step, Data Normalization

Normalization involves organizing data based on assigned attributes as a part of a larger data model. The main objective of database normalization is to eliminate redundant data, minimize data modification errors, and simplify the query process.

Database Diagram Design

The Original table has been split into 6 tables which are the Customers , Categories , Suppliers , Products , Orders and Employees .

The next step is to design an ER (Entity Relationship) Diagram. ER diagrams use symbols to represent entities, attributes, and relationships, which help to illustrate the relationships between the entities in the database.

Table Alterations

The ER Diagram for the entities have been created and the table constraints have been specified.

The next step is to alter the tables to fit the constraints as specified in the ER Diagram. This section focuses on Table Alterations.

To start with Altering the features of the Customers table

-- FOR THE CUSTOMERS TABLE

ALTER TABLE customers 
CHANGE COLUMN customerID customerID CHAR(5) NOT NULL ,
CHANGE COLUMN companyName companyName VARCHAR(255) NOT NULL ,
CHANGE COLUMN contactName contactName VARCHAR(255) NOT NULL ,
CHANGE COLUMN contactTitle contactTitle VARCHAR(255) NOT NULL ,
ADD PRIMARY KEY (customerID);
;

-- TO VERIFY IF THE CHANGES WERE MADE
DESCRIBE customers;

To Alter the features of the Categories table

-- FOR THE CATEGORIES TABLE

ALTER TABLE categories
MODIFY categoryID INT AUTO_INCREMENT PRIMARY KEY, -- For Category ID
MODIFY categoryName VARCHAR(255) NOT NULL UNIQUE -- For Category Name
;

-- TO VERIFY IF THE CHANGES WERE MADE
DESCRIBE categories;

To view the full documentation of this step, check HERE

Creating Views, Triggers, and Stored Procedures

At this point, the database for the business has been setup.

The business owner made three requests.

Create a view that shows the number of Quantity sold and The Revenue made by Each Employee
Create a Trigger on the products table that automatically removes the number of Units of product in stock, after an order has been made
Write a Procedure to check if a certain products needs to be restocked and also list the products that needs to be restocked

If you have no idea on what Triggers, Views and Procedures mean, Check HERE

For Task 1: Creating a View

To create a view that shows the Quantity of products sold and the revenue for each Employee

-- Creating the View for the task
CREATE OR REPLACE VIEW employee_record AS
SELECT e.employeeID,
    e.full_name AS "Full Name",
       e.employees_title AS "Title",
    SUM(o.quantity) AS "Quantity Sold",
       CONCAT( '$',
    ROUND(SUM(o.unitPrice * o.quantity), 2)
          ) AS Revenue
FROM employees e JOIN
  orders o 
     ON e.employeeID = o.employeeID
GROUP BY e.employeeID,
   e.full_name,
         e.employees_title
ORDER BY SUM(o.unitPrice * o.quantity) DESC
;

-- To test the view if it works
SELECT *
FROM employee_record;

The output of the view is seen below

For Task 2: Creating a Trigger

To Create a Trigger on the products table that automatically removes the number of Units of product in stock, after an order has been made

-- To fist drop the Trigger if it exists
DROP TRIGGER update_products;

-- To create the trigger
DELIMITER //
CREATE TRIGGER update_products
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE products
SET unitsInStock = products.unitsInStock - NEW.quantity
WHERE productID = NEW.productID;
END //
DELIMITER ;

For Task 3: Writing a Stored Procedure

Write a Procedure to check if a certain products needs to be restocked and also list the products that needs to be restocked

-- Creating a stored procedure

DELIMITER $$
CREATE PROCEDURE getRestock_products ( IN product_name VARCHAR(255))
BEGIN
-- TAKING THE LOWER CASE OF THE INPUT PARAMETER
SET product_name = LOWER(product_name);
-- To check if a certain product needs to be restocked
SELECT productName,
    CASE WHEN unitsInStock < reorderLevel THEN "Restock Level reached"
       WHEN unitsInStock = reorderLevel THEN "On Restock Level"
       WHEN unitsInStock - reorderLevel <= 5 THEN "Close to Restock Level"
       ELSE "Above Restocked Level"
       END AS "Restock Status"
FROM products
WHERE LOWER(productName) LIKE CONCAT('%', product_name, '%');

-- Products that needs to be restocked
SELECT productID,
    productName,
       unitsInStock,
       reorderLevel
FROM products
WHERE unitsInStock < reorderLevel;
END $$
DELIMITER ;

-- To test if the procedures worked, to check if the product that contains
-- "che" has reached restocked level
CALL getRestock_products("che");

The Output of the Procedure is seen below

The products that contain “che” and their Restock Status

From the Image above, we can see that all the listed products that contain “che”. Of all the products, Chef Anton’s Gumbo Mix is the only product on the restock level. This means that the suppliers should be getting ready to supply more of the product.

The procedure also lists all the products that urgently needs to be restocked as seen in the image below

User Management and Privileges

Task: Create two Users and give them access to the database. The first user, “TuckerReilly”, will be a DBA, and should get full database administrator privileges. The second user, “EllaBrody” is an Analyst and only need read access.

Designing a database also entails User Management, Granting and Revoking User privileges. This would be done using SQL commands GRANT and REVOKE.

To read more about the Revoke and Grant command, Check HERE

The User Creation and User Privileges were done using the following commands

-- To check for the existing privileges granted
SHOW GRANTS FOR 
root@localhost -- username@hostname
;

-- USER CREATION

-- FOR THE FIRST USER NAMED "TuckerRelly"
CREATE USER IF NOT EXISTS
'TuckerRelly'@'localhost'
IDENTIFIED BY 'user_password';

-- FOR THE SECOND USER NAMED "EllaBrody"
CREATE USER IF NOT EXISTS
'EllaBrody'@'localhost'
IDENTIFIED BY 'user_password';

-- ASSIGNING USER PRIVILEGES

-- FOR THE FIRST USER, PRIVILEGES ARE FULL DATABASE ADMINISTRATOR
GRANT ALL PRIVILEGES
ON db_northwind.*
TO 'TuckerRelly'@'localhost';

-- FOR THE SECOND USER, PRIVILEGE IS ONLY THE SELECT STATEMENT
GRANT 
 SELECT
ON db_northwind.*
TO 'EllaBrody'@'localhost';

Database Backup

The primary purpose of backing up a database is to create a duplicate copy of its data and structure at a specific point in time. This process involves making a snapshot of the entire database or selected portions of it, and storing this copy in a secure location.

Some of the locations you can use to backup your database are

On-premises Servers
External Hard Drives or USB Drives
Network Attached Storage (NAS)
Cloud Storage Services: Storing backups in cloud-based storage solutions like Amazon S3, Google Cloud Storage, Microsoft Azure Blob Storage, or other similar services. Cloud storage offers scalability, accessibility, and disaster recovery benefits.

and so many other locations.

This database was backed up on a Hard Drive using MySQL local instance Data Export feature.

The full documentation of the project can be found Here

Generally, A database is very essential to every business. It serves as a centralized repository, organizing and storing vast amounts of valuable data related to customers, products, orders, and more.

With a database in place, businesses can easily track and manage their inventory, ensuring products are readily available when customers need them. Moreover, a database ensures data security and integrity, safeguarding sensitive information from unauthorized access or loss. It allows for reliable backups, protecting against potential disasters or system failures.

Thank you for reading and feel free to comment, share and correct me in any aspect of the work. I would also love feedbacks.