Design an e-commerce Database in MySQL — schema

If you understand core concepts of SQL like create, update, alter, drop, and different search queries, it is going to be easy for you to design a database schema.

Almost every company stores data about employees, clients, sales, suppliers, and branches, right? To build such a database, we need to create separate tables and establish connections between them.

An example is an actual schema of an e-commerce store that provides services to compare prices for insurance contracts, cars, etc.

We will create five tables in our database: ‘employee’, ‘branch’, ‘branch supplier’, ‘client’, and ‘works with’. I will tell you how to do it.

1. Create Employee Table

Such organizations have employees with their unique employee ID that will be PRIMARY KEY. This key allows us to store and find a particular person in the table. Your employees have their first name, last name, date of birth, sex, and salary. These are all the dimensions that will be stored in the employee table.

Every employee belongs to a department or a branch (branch_id) and has their own manager (super_id). This kind of information is identified in SQL as FOREIGN KEYS.

Later, when you search for the information in your table, primary and foreign keys will have considerable meaning.

CREATE TABLE employee (
emp_id INT PRIMARY KEY,
first_name VARCHAR(40),
last_name VARCHAR(40),
birth_day DATE,
sex VARCHAR(1),
salary INT,
super_id INT,
branch_id INT);

If you forgot SQL Syntax, here’s a little hint below.

INT — Whole Numbers

DECIMAL(M,N) — Decimal Numbers — Exact Value

VARCHAR(l) — String of text of length l

BLOB — Binary Large Object, Stores large data

DATE — ‘YYYY-MM-DD’

TIMESTAMP — ‘YYYY-MM-DD HH:MM:SS’ — used for recording events

2. Create Branch Table

So, this company has three departments: insurance, account, and energy. Each department has its own manager (mgr_id) and manager start date. Manager ID will be our way to connect employee ID from our main employee table. It is the only dimension that cannot be deleted because it connects the tables.

It is crucial to add ON DELETE SET NULL in the foreign key to ensure that if a manager resigns, the connection will remain. I hope it makes sense.

CREATE TABLE branch (
branch_id INT PRIMARY KEY,
branch_name VARCHAR(40),
mgr_id INT,
mgr_start_date DATE,
FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

3. Create Client Table

Since the clients of this store are private individuals, we add their first and last names. Every client should have a unique identification number (client_id) and belong to a department. For example, Daniel Maier, with an identification number 10, would like to request construction financing from a bank and compare suppliers on the website. As long as it is a financial operation, the transaction will be conducted within Account & Finance branch, which is number 2 in our table Branch.

CREATE TABLE client (
client_id INT PRIMARY KEY,
first_name VARCHAR(40),
last_name VARCHAR(40),
branch_id INT,
FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL);

Let’s establish foreign keys in the table

ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;understand logic

4. Create Work With Table

The task of the company’s employees is to serve customers. In the table Works_with, we will set the clients belonging to the employee and the number of sales.

CREATE TABLE works_with (
emp_id INT,
client_id INT,
total_sales INT,
product_id INT,
PRIMARY KEY(emp_id, client_id),
FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);

We establish ON DELETE CASCADE in our table to delete a row entirely in both parent and child tables if, for instance, we need to delete a client or an employee.

5. Create Branch Supplier Table

For example, if a customer wants to compare the prices of potential suppliers for an insurance contract. The branch table stores data about suppliers, their type, and branch_id.

CREATE TABLE branch_supplier (
branch_id INT,
supplier_name VARCHAR(40),
supply_type VARCHAR(40),
PRIMARY KEY(branch_id, supplier_name),
FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);

5. Insert data

Since our tables have been created, the next step is to insert the information. The queries are below. Make sure that we add the data about employees according to the branches.

-- Insurance Branch
INSERT INTO employee VALUES(20, 'Finn', 'Müller', '1967-05-12', 'M', 85000, NULL, NULL);
INSERT INTO branch VALUES(1, 'Insurance', 20, '2022-02-06');
UPDATE employee
SET branch_id = 1
WHERE emp_id = 20;

INSERT INTO employee VALUES(21, 'Lucas', 'Schmidt', '1972-08-30', 'M', 200000, 20, 1);
-- Account and Credit Branch
INSERT INTO employee VALUES(22, 'Jonas', 'Fischer', '1983-01-01', 'M', 65000, 22, NULL);
INSERT INTO branch VALUES(2, 'Account', 22, '2022-04-01');
UPDATE employee
SET branch_id = 2
WHERE emp_id = 22;
INSERT INTO employee VALUES(23, 'Ella', 'Weber', '1964-05-22', 'F', 170000, 22, 2);
INSERT INTO employee VALUES(24, 'Ida', 'Wagner', '1970-03-07', 'F', 90000, 22, 2);
INSERT INTO employee VALUES(25, 'Ben', 'Schneider', '1973-08-25', 'M', 72000, 22, 2);
-- Energy and Gas Branch
INSERT INTO employee VALUES(26, 'Oliver', 'Brown', '1989-03-02', 'M', 199000, 26, NULL);
INSERT INTO branch VALUES(3, 'Energy', 26, '2019-02-24');
UPDATE employee
SET branch_id = 3
WHERE emp_id = 26;
INSERT INTO employee VALUES(27, 'Leonie', 'Williams', '1977-07-07', 'F', 80000, 26, 3);
INSERT INTO employee VALUES(28, 'Lea', 'Zimmerman', '1983-12-05', 'F', 62000, 26, 3);
-- BRANCH SUPPLIER
INSERT INTO branch_supplier VALUES(1, 'S_Direkt', 'Insurance');
INSERT INTO branch_supplier VALUES(1, 'Inshared', 'Insurance');
INSERT INTO branch_supplier VALUES(2, 'Deutsche_Bank', 'Credit');
INSERT INTO branch_supplier VALUES(2, 'Commerzbank', 'Credit');
INSERT INTO branch_supplier VALUES(3, 'Ostrom', 'Renewable');
INSERT INTO branch_supplier VALUES(3, 'E.On_Energy', 'Electricity');
-- CLIENT
INSERT INTO client VALUES(10, 'Daniel', 'Maier', 2);
INSERT INTO client VALUES(11, 'Ella', 'Walter', 2);
INSERT INTO client VALUES(12, 'Zhang', 'Wei', 1);
INSERT INTO client VALUES(13, 'Ivan', 'Kirillov', 3);
INSERT INTO client VALUES(14, 'John', 'Smith', 3);
INSERT INTO client VALUES(15, 'Dirk', 'Pelletr', 1);
INSERT INTO client VALUES(16, 'Tobias', 'Roth', 1);
-- WORKS_WITH
INSERT INTO works_with VALUES(20, 10, 23000);
INSERT INTO works_with VALUES(21, 11, 500000);
INSERT INTO works_with VALUES(22, 12, 119000);
INSERT INTO works_with VALUES(22, 13, 3000);
INSERT INTO works_with VALUES(28, 14, 120000);
INSERT INTO works_with VALUES(22, 15, 6000);
INSERT INTO works_with VALUES(20, 15, 6000);
INSERT INTO works_with VALUES(25, 16, 55000);
INSERT INTO works_with VALUES(21, 11, 40000);

Your database is established, and the tables are created.

Thanks for reading.

See you later!