|
Getting your Trinity Audio player ready...
|
Imagine you just joined a growing FMCG company as a Data Analyst.
The company sells:
- Beverages
- Snacks
- Household products
They have:
- Employees
- Customers
- Products
- Sales
- Inventory
But there is a problem…
All data is currently managed in Excel files 😵
- Duplicate entries
- Wrong spellings
- No relationships
- Slow reporting
Your job?
Move everything into a proper SQL database.
Today, we will learn exactly how to do that.
Step 1: What is a Database in Practical Terms?
A database is simply a container that holds tables.
Think of it like:
📂 FMCG_Company (Database)
├── employees (Table)
├── customers (Table)
├── products (Table)
├── sales (Table)
└── inventory (Table)
Database = Collection of related tables.
Now let’s create one.
Also Read: Basics of SQL
Creating a Database
We will first create a database named:
fmcg_companyPostgreSQL
CREATE DATABASE fmcg_company;To start using it:
\c fmcg_company(In pgAdmin, simply select the database and open query tool.)
MySQL
CREATE DATABASE fmcg_company;To use it:
USE fmcg_company;That’s it.
Now we are inside the database and ready to create tables.
Step 2: Designing Our FMCG Database Structure
Before writing SQL, we must design structure.
Our FMCG company will have 6 main tables:
- departments
- employees
- customers
- products
- sales
- inventory
Let’s understand each one before creating them.
Departments Table
Stores department details like:
- Sales
- Finance
- HR
- Warehouse
PostgreSQL
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
department_name VARCHAR(100) NOT NULL,
location VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);MySQL
CREATE TABLE departments (
id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(100) NOT NULL,
location VARCHAR(100),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);Read More: Data Types in SQL
Employees Table
Employees belong to departments.
So this table will connect to departments using a Foreign Key.
Columns:
- id
- employee_code
- first_name
- last_name
- salary
- joining_date
- department_id (Foreign Key)
PostgreSQL
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
employee_code VARCHAR(20) UNIQUE NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
salary NUMERIC(10,2) NOT NULL,
joining_date DATE,
department_id INT,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (department_id) REFERENCES departments(id)
);MySQL
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
employee_code VARCHAR(20) UNIQUE NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
salary DECIMAL(10,2) NOT NULL,
joining_date DATE,
department_id INT,
is_active BOOLEAN DEFAULT TRUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (department_id) REFERENCES departments(id)
);Customers Table
Stores customer information.
Columns:
- id
- customer_name
- phone
- city
- state
- created_at
PostgreSQL
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
phone VARCHAR(15),
email VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);MySQL
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
phone VARCHAR(15),
email VARCHAR(100),
city VARCHAR(50),
state VARCHAR(50),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);Products Table
Stores all product information.
Columns:
- id
- product_name
- category
- price
- cost_price
- launch_date
- is_active
PostgreSQL
CREATE TABLE products (
id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price NUMERIC(10,2) NOT NULL,
cost_price NUMERIC(10,2),
launch_date DATE,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);MySQL
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10,2) NOT NULL,
cost_price DECIMAL(10,2),
launch_date DATE,
is_active BOOLEAN DEFAULT TRUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);Sales Table (Most Important Table)
This table connects everything.
It will include:
- customer_id
- product_id
- employee_id
- quantity
- total_amount
- sale_date
This table contains multiple Foreign Keys.
PostgreSQL
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
invoice_no VARCHAR(30) UNIQUE NOT NULL,
customer_id INT NOT NULL,
product_id INT NOT NULL,
employee_id INT NOT NULL,
quantity INT NOT NULL,
total_amount NUMERIC(10,2) NOT NULL,
sale_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id),
FOREIGN KEY (product_id) REFERENCES products(id),
FOREIGN KEY (employee_id) REFERENCES employees(id)
);MySQL
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
invoice_no VARCHAR(30) UNIQUE NOT NULL,
customer_id INT NOT NULL,
product_id INT NOT NULL,
employee_id INT NOT NULL,
quantity INT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
sale_date DATE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id),
FOREIGN KEY (product_id) REFERENCES products(id),
FOREIGN KEY (employee_id) REFERENCES employees(id)
);Inventory Table
Tracks stock levels.
Columns:
- product_id
- stock_in
- stock_out
- current_stock
PostgreSQL
CREATE TABLE inventory (
id SERIAL PRIMARY KEY,
product_id INT NOT NULL,
stock_in INT DEFAULT 0,
stock_out INT DEFAULT 0,
current_stock INT,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id)
);MySQL
CREATE TABLE inventory (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
stock_in INT DEFAULT 0,
stock_out INT DEFAULT 0,
current_stock INT,
last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id)
);What We Just Built
We created a complete relational database system:
- Departments → Employees
- Employees → Sales
- Customers → Sales
- Products → Sales
- Products → Inventory
This is exactly how real company databases are structured.
Important Differences Between PostgreSQL and MySQL
| Feature | PostgreSQL | MySQL |
|---|---|---|
| Auto Increment | SERIAL | AUTO_INCREMENT |
| Decimal Type | NUMERIC | DECIMAL |
| DateTime | TIMESTAMP | DATETIME |
| Boolean | TRUE/FALSE | 1/0 internally |
Your primary learning focus should be PostgreSQL.
But understanding MySQL makes you flexible.
Why This Article Is Very Important
Most beginners jump directly to SELECT queries.
But real-world Data Analysts must understand:
- How tables are structured
- How foreign keys work
- How constraints prevent errors
- How databases are designed
If structure is wrong, reports will be wrong.
What’s Next?
Now that we created database and tables, now: