Database Design
Why Good Databases Are Planned Before They Are Built
Imagine AQAD decides to expand across multiple countries.
The business team is excited.
Developers start building new features.
Everyone is moving quickly.
A junior developer says:
Let's create tables as we go.
At first, this sounds reasonable.
After all, creating tables is easy.
But six months later, problems start appearing:
Duplicate vendor information
Confusing product records
Slow reports
Difficult maintenance
Complex queries
Unexpected bugs
Management asks:
Why is everything becoming difficult?
The answer is often:
Poor Database Design.
Building a House Analogy
Imagine building a house.
Would you start by randomly placing bricks?
Of course not.
First:
Blueprint
Foundation
Structure
Rooms
Plumbing
Electrical Planning
Only then construction begins.
Database design follows the same principle.
Experienced developers spend significant time planning before creating tables.
What Is Database Design?
Database Design is the process of organizing data efficiently before building the database.
Think of it as:
Creating the blueprint for your data.
Good database design makes systems:
Easier to build
Easier to maintain
Easier to scale
Faster to query
Poor design creates problems for years.
Why Database Design Matters
Imagine AQAD stores products.
Bad design:
| Product | Vendor Name | Vendor Phone |
|---|---|---|
| Milk | Fresh Farm | 123 |
| Rice | Fresh Farm | 123 |
| Juice | Fresh Farm | 123 |
Notice something?
Vendor information repeats.
Again.
And again.
And again.
This may seem harmless.
It is not.
The Duplicate Data Problem
Suppose Fresh Farm changes phone number.
Old: 123
New: 999
Now every row must be updated.
Miss one row.
Data becomes inconsistent.
This is one of the biggest database problems.
Good design avoids it.
Thinking Like a Database Architect
When experienced developers design databases, they ask:
What information exists?
How is it related?
What changes frequently?
What remains stable?
How will this scale?
These questions guide the design.
AQAD Information Analysis
Let's analyze AQAD.
Main entities:
Vendors
Retailers
Products
Orders
Payments
Deliveries
Categories
Notice:
These are separate business concepts.
Therefore they should usually have separate tables.
Bad AQAD Design Example
Imagine a single table.
| Vendor | Product | Retailer | Order |
|---|
Everything stored together.
Problems:
Massive duplication
Difficult updates
Poor scalability
Confusing structure
This approach does not scale.
Better AQAD Design
Separate tables.
Vendors
Products
Retailers
Orders
Payments
Deliveries
Each table focuses on one responsibility.
This principle is extremely important.
Single Responsibility Principle for Tables
Just like software classes should have one responsibility, tables should too.
Example:
Vendors Table
Stores:
Vendor Name
Vendor Email
Vendor Phone
Nothing else.
Products Table
Stores:
Product Name
Price
Quantity
Nothing else.
This creates clean architecture.
What Is Normalization?
Normalization sounds complicated.
But the idea is simple.
Normalization means:
Organizing data to reduce duplication.
That's it.
Library Analogy
Imagine a library.
Bad organization:
Book Title
Author Name
Author Phone
Author Address
Repeated inside every book record.
Huge duplication.
Better organization:
Books Table
Authors Table
Relationship between them.
Less duplication.
Cleaner structure.
Normalization works the same way.
First Normal Form (1NF)
Don't worry about memorizing names.
Focus on the concept.
Rule:
Each column should contain a single value.
Bad Example
| Product |
|---|
| Milk,Rice,Juice |
Multiple values in one column.
Bad design.
Better
| Product |
|---|
| Milk |
| Rice |
| Juice |
One value per row.
Cleaner.
AQAD Example
Bad:
| Vendor Products |
|---|
| Milk,Rice,Juice |
Good:
Products stored in separate rows.
This makes searching and reporting easier.
Second Normal Form (2NF)
Simplified idea:
Store information in the correct table.
Example:
Product Table should not contain:
Vendor Phone
Vendor Phone belongs in Vendors table.
Third Normal Form (3NF)
Another simple rule:
Store information only once whenever possible.
Example:
Vendor Address should exist in:
Vendors Table
Not:
Products Table
Orders Table
Payments Table
Repeated hundreds of times.
Why Normalization Matters
Benefits:
Less duplication
Easier updates
Better consistency
Smaller storage
Cleaner architecture
Most production databases use normalized structures.
AQAD Vendor Example
Bad Design
Products Table
| Product | Vendor Name |
|---|---|
| Milk | Fresh Farm |
| Rice | Fresh Farm |
| Juice | Fresh Farm |
Vendor name repeats.
Good Design
Vendors Table
| Vendor ID | Vendor Name |
|---|---|
| 101 | Fresh Farm |
Products Table
| Product | Vendor ID |
|---|---|
| Milk | 101 |
| Rice | 101 |
| Juice | 101 |
Cleaner.
More scalable.
Designing for Future Growth
A common beginner mistake:
Designing for today's data.
Example:
AQAD currently has:
100 products.
Database seems fine.
But what about:
100,000 products?
10 million products?
Database design should support growth.
Questions Architects Ask
Imagine AQAD plans expansion.
Architect asks:
How many vendors?
How many retailers?
How many products?
How many orders?
How many countries?
Design decisions depend on future scale.
Naming Conventions
Good naming matters.
Bad:
tbl1
tbl2
data123
Nobody understands these names.
Better:
vendors
products
orders
payments
Clear and readable.
Future developers will thank you.
Consistent Primary Keys
AQAD Example:
vendor_id
product_id
retailer_id
order_id
payment_id
Consistent naming improves maintainability.
Consistent Foreign Keys
Products Table
vendor_id
Orders Table
retailer_id
Payments Table
order_id
This makes relationships obvious.
Avoid Storing Derived Data
Suppose AQAD stores:
total_order_value
and
individual order items.
Potential problem:
Order items change.
Total remains outdated.
Now data becomes inconsistent.
Sometimes storing derived data is useful for performance.
But beginners should be careful.
Real AQAD Schema Thinking
Let's design part of AQAD.
Users
user_id
name
email
phone
Vendors
vendor_id
user_id
company_name
Products
product_id
vendor_id
title
price
quantity
Orders
order_id
retailer_id
order_date
Order Items
order_item_id
order_id
product_id
quantity
price
Notice:
Everything has a clear responsibility.
Why Experienced Developers Draw First
Before writing SQL, architects often create:
ER Diagrams
(Entity Relationship Diagrams)
These diagrams visualize:
Tables
Relationships
Foreign Keys
Planning reduces future mistakes.
AQAD Relationship View
Users
↓
Vendors
↓
Products
Retailers
↓
Orders
↓
Order Items
↑
Products
Orders
↓
Payments
Orders
↓
Deliveries
This picture helps developers understand the business.
Scalability Thinking
Imagine AQAD reaches:
50 countries
Millions of users
Hundreds of millions of orders
Good design continues working.
Bad design becomes painful.
This is why architecture matters.
Common Beginner Mistakes
Mistake 1
Creating giant tables.
Mistake 2
Duplicating information.
Mistake 3
Ignoring relationships.
Mistake 4
Poor naming conventions.
Mistake 5
Designing only for current requirements.
Mistake 6
Skipping planning.
Mini Exercise
Question: Should vendor phone number exist inside every product row?
Answer: No.
Store it once in Vendors table.
Question: Should products and orders be separate tables?
Answer: Yes.
They represent different business entities.
Question: Should AQAD have one giant table for everything?
Answer: No.
Separate responsibilities create better design.
Try It Yourself
Imagine you are designing a new AQAD module.
Tables:
Coupons
Campaigns
Reviews
Ask yourself:
What information belongs in each table?
What relationships exist?
This exercise helps develop architecture thinking.
Real Developer Insight
Many database problems don't appear immediately.
Poor designs often work for months.
Sometimes years.
Then growth happens.
Suddenly:
Queries become difficult
Features become harder
Reports become unreliable
Good database design is like a strong foundation.
Most users never notice it.
But every successful application depends on it.
Real E-Commerce Database Schema
Why Every Serious Application Needs a Database Schema
Imagine AQAD launches tomorrow.
The mobile app is live.
Vendors are uploading products.
Retailers are placing orders.
Payments are being processed.
Drivers are delivering products.
Everything looks exciting.
But then a developer asks:
"Where should we store the products?"
Another asks:
"Where do we save orders?"
Another asks:
"How do we know which products belong to which vendor?"
And another asks:
"How can we track inventory?"
Suddenly everyone realizes something important.
Before building features, we need a proper database structure.
This structure is called a Database Schema.
A database schema is simply the blueprint of how data is organized.
Just like a house blueprint shows:
- Bedrooms
- Kitchen
- Bathroom
- Hallway
A database schema shows:
- Tables
- Columns
- Relationships
- Constraints
Without a blueprint, builders create a mess.
Without a schema, developers create a mess.
What Is a Database Schema?
A database schema is the overall design of a database.
It defines:
- What tables exist
- What data each table stores
- How tables connect
- Which columns are required
- Which columns are unique
- Which relationships exist
Think about AQAD.
The platform needs:
- Users
- Vendors
- Retailers
- Products
- Categories
- Orders
- Payments
- Deliveries
Each of these becomes one or more tables.
Together they form the schema.
Real E-Commerce Flow
Before designing tables, let's understand the business.
A retailer opens AQAD.
The retailer searches products.
The retailer adds products to cart.
The retailer places an order.
The vendor receives the order.
The vendor prepares inventory.
AQAD processes payment.
A logistics partner delivers products.
The retailer receives products.
Every step generates data.
That data must be stored somewhere.
Now let's design the database.
Step 1: Customers Table
In a traditional e-commerce system, customers purchase products.
Table:
customers
Columns:
customer_id
name
phone
address
city
country
created_at
Example:
| customer_id | name |
|---|---|
| 1 | Ahmed |
| 2 | Sara |
| 3 | Ali |
This table stores customer information only.
Not products.
Not orders.
Only customer information.
One table.
One responsibility.
This is an important database design principle.
Why Separate Tables Matter
Many beginners try this:
customer_name
customer_email
product_name
product_price
order_total
inside one giant table.
Initially it seems easier.
Later it becomes a disaster.
Why?
Because:
- Data duplicates
- Updates become difficult
- Reports become slow
- Storage increases
Good database design separates responsibilities.
Step 2: Categories Table
AQAD sells thousands of products.
We need categories.
Examples:
- Grocery
- Beverages
- Electronics
- Household Goods
- Health & Beauty
Table:
categories
Columns:
category_id
category_name
description
created_at
Example:
| category_id | category_name |
|---|---|
| 1 | Grocery |
| 2 | Beverages |
| 3 | Electronics |
Now products can belong to categories.
Why Categories Exist
Imagine 100,000 products.
Without categories users would scroll forever.
Categories help:
- Search
- Filtering
- Reporting
- Inventory management
- Analytics
For example:
AQAD management can ask:
Which category generated the most sales this month?
Without categories this becomes difficult.
Step 3: Products Table
Products are the heart of an e-commerce business.
Table:
products
Columns:
product_id
category_id
product_name
description
sku
price
stock_quantity
status
created_at
Example:
| product_id | product_name |
|---|---|
| 101 | Coca-Cola 330ml |
| 102 | Rice 5kg |
| 103 | Sunflower Oil |
Notice:
category_id
connects products to categories.
This is a foreign key relationship.
Product Relationship Diagram
Categories
|
|
v
Products
One category can have many products.
Example:
Beverages
↓
- Coca-Cola
- Pepsi
- Water
- Orange Juice
Relationship:
One Category
→
Many Products
This is a classic One-to-Many relationship.
Step 4: Inventory Table
Many beginners store inventory directly inside products.
Small systems can survive this.
Large systems usually separate inventory.
Table:
inventory
Columns:
inventory_id
product_id
available_quantity
reserved_quantity
warehouse_location
updated_at
Example:
| product_id | available_quantity |
|---|---|
| 101 | 500 |
| 102 | 200 |
| 103 | 900 |
Why separate inventory?
Because inventory changes constantly.
Products rarely change.
Separating them improves performance.
Real AQAD Scenario
Vendor uploads:
Rice 5kg
Inventory:
1000 units
Retailer orders:
50 units
Inventory becomes:
950 units
This update affects inventory only.
Not the product details.
That is why inventory deserves its own table.
Step 5: Orders Table
Orders represent purchases.
Table:
orders
Columns:
order_id
customer_id
order_date
total_amount
status
payment_status
created_at
Example:
| order_id | customer_id |
|---|---|
| 5001 | 1 |
| 5002 | 2 |
This table stores order-level information.
Not product details.
Just the order itself.
Common Beginner Mistake
Many beginners ask:
Where do we store ordered products?
Not inside the orders table.
Because one order can contain:
- Rice
- Oil
- Juice
- Water
Multiple products.
We need another table.
Step 6: Order Items Table
This is one of the most important tables in e-commerce.
Table:
order_items
Columns:
order_item_id
order_id
product_id
quantity
price
subtotal
Example:
| order_id | product_id | quantity |
|---|---|---|
| 5001 | 101 | 5 |
| 5001 | 102 | 2 |
| 5001 | 103 | 1 |
Now a single order can contain many products.
Perfect.
Understanding the Relationship
Order:
Order #5001
Contains:
Rice
Oil
Water
Relationship:
Orders
|
|
Many
Order Items
One order
↓
Many order items
This design powers almost every major e-commerce platform.
Visual Schema So Far
Customers
|
|
Orders
|
|
Order Items
|
|
Products
|
|
Categories
Now the system is becoming powerful.
But we're not finished.
Step 7: Payments Table
Businesses must track payments.
Table:
payments
Columns:
payment_id
order_id
payment_method
amount
transaction_reference
payment_status
payment_date
Examples:
Payment methods:
- Credit Card
- Debit Card
- Bank Transfer
- Wallet
- Cash
Why Separate Payments?
Because orders and payments are different things.
Example:
Order:
Created
Payment:
Pending
or
Failed
or
Completed
Separating them creates flexibility.
Step 8: Shipping and Deliveries
Products must reach customers.
Table:
deliveries
Columns:
delivery_id
order_id
driver_name
tracking_number
delivery_status
estimated_delivery
delivered_at
Statuses:
Pending
Assigned
Picked Up
In Transit
Delivered
Failed
Now AQAD can track every shipment.
Complete Relationship Overview
Customers
|
|
Orders
|
+----------------+
| |
| |
Order Items Payments
|
|
Products
|
|
Categories
|
|
Inventory
Orders
|
Deliveries
This is the foundation of most e-commerce systems.
Real AQAD Walkthrough
Let's follow one order.
Retailer:
Ahmed Supermarket
places:
100 Coca-Cola
50 Rice Bags
20 Cooking Oils
Flow:
Step 1
Customer record exists.
customers
Step 2
Order created.
orders
Step 3
Products added.
order_items
Step 4
Inventory reduced.
inventory
Step 5
Payment recorded.
payments
Step 6
Delivery assigned.
deliveries
Step 7
Order completed.
Everything is stored cleanly.
Everything is connected.
Everything is traceable.
That is the power of a well-designed schema.
Common Database Design Mistakes
Mistake 1
Storing everything in one table.
Bad:
Orders + Customers + Products
mixed together.
Mistake 2
No foreign keys.
This creates orphan records.
Mistake 3
Duplicating customer data.
Mistake 4
Duplicating product information.
Mistake 5
No inventory tracking.
Mistake 6
No payment table.
Try It Yourself
Design tables for:
Food Delivery App
Think about:
- Restaurants
- Menu Items
- Orders
- Customers
- Drivers
What relationships would you create?
Mini Exercise
AQAD sells:
Beverages
|
+ Coca-Cola
+ Pepsi
+ Water
Question:
What relationship exists between:
Categories
and
Products
Answer:
One-to-Many
One category can contain many products.
AQAD Marketplace Database Design (Part 1)
From Simple E-Commerce to a Real Marketplace
we designed a traditional e-commerce database.
That model works well when:
- One company owns all products
- One company manages inventory
- One company receives payments
- One company delivers products
Examples:
- Small online stores
- Brand websites
- Single-vendor shops
But AQAD is different.
AQAD is a marketplace.
A marketplace is much more complicated.
Why?
Because multiple businesses participate.
For example:
Vendor A → Coca-Cola Distributor
Vendor B → Rice Supplier
Vendor C → Electronics Supplier
↓
AQAD Platform
↓
Retailers
↓
Customers
Now the database must handle:
- Thousands of vendors
- Thousands of retailers
- Millions of products
- Millions of orders
- Multiple warehouses
- Multiple delivery partners
- Notifications
- Payments
- Inventory updates
- User permissions
This is where database design becomes interesting.
Understanding AQAD Business Flow
Before creating tables, let's understand how AQAD actually works.
Imagine this situation.
A new vendor wants to join AQAD.
The vendor uploads:
- Company information
- Trade license
- VAT certificate
- Bank details
- Warehouse information
AQAD verifies the vendor.
The vendor uploads products.
Retailers browse products.
Retailers place orders.
Vendors receive orders.
Logistics partners deliver products.
Payments are settled.
Notifications are sent.
Reports are generated.
Every single step creates data.
Every piece of data must be stored correctly.
Let's design the database.
Step 1: Users Table
Almost every platform starts with users.
But AQAD has multiple user types.
Examples:
- Vendor
- Retailer
- Logistics Partner
- AQAD Employee
Instead of creating separate login tables, we create one master table.
Table:
users
Columns:
id
name
phone
password
user_type
account_status
is_verified
created_at
updated_at
Example:
| id | name | user_type |
|---|---|---|
| 1 | Ahmed | vendor |
| 2 | Sara | retailer |
| 3 | Ali | logistics |
| 4 | Admin User | employee |
Why One Users Table?
Many beginners ask:
Why not create separate tables?
Because authentication becomes difficult.
Imagine creating:
vendor_login
retailer_login
employee_login
driver_login
Now login becomes messy.
Instead:
users
handles authentication.
User type determines behavior.
Simple.
Scalable.
Maintainable.
User Type Analogy
Think of an office building.
Every person enters through the same gate.
But inside:
- Employees go to offices
- Visitors go to reception
- Security goes to monitoring room
- Managers go to executive floor
Same entrance.
Different permissions.
AQAD users work exactly the same way.
Step 2: Vendor Profile Table
Vendors need additional information.
A normal user table should not contain:
- Trade License
- VAT Certificate
- Company Name
- Warehouse Details
Those belong in a separate table.
Table:
vendor_profiles
Columns:
vendor_id
user_id
company_name
trade_license_number
vat_number
iban
company_address
country
status
created_at
Example:
| vendor_id | company_name |
|---|---|
| 101 | Dubai Beverage Trading |
| 102 | Fresh Foods LLC |
| 103 | Gulf Electronics |
Why Separate Vendor Data?
Not every user is a vendor.
A retailer doesn't need:
Trade License
VAT Number
Warehouse Address
Storing those columns in users table would create many empty fields.
This violates good database design.
Step 3: Retailer Profile Table
Retailers also need additional information.
Table:
retailer_profiles
Columns:
retailer_id
user_id
store_name
business_license
address
country
credit_limit
created_at
Example:
| retailer_id | store_name |
|---|---|
| 201 | Ahmed Supermarket |
| 202 | Star Grocery |
| 203 | Fresh Mart |
Real AQAD Example
Retailer:
Ahmed Supermarket
might place:
500 orders per month
The retailer profile stores business-related information.
The users table stores login-related information.
Responsibilities remain separate.
Step 4: Vendor Documents Table
During onboarding vendors upload documents.
Examples:
- Trade License
- VAT Certificate
- Emirates ID
- Residence Visa
- Cheque Scan
We should never store file paths directly inside vendor_profiles.
Instead:
vendor_documents
Columns:
document_id
vendor_id
document_type
document_url
verification_status
uploaded_at
Example:
| document_type |
|---|
| Trade License |
| VAT Certificate |
| Cheque Scan |
Why Separate Documents?
Because vendors may upload:
5 documents
10 documents
20 documents
Tomorrow AQAD may require additional documents.
The schema remains flexible.
Step 5: Categories Table
AQAD categories already exist.
Examples:
Fresh Food
Grocery
Beverages
Frozen Foods
Health & Beauty
Baby Products
Household Goods
Electronics
Fashion
Sports
Table:
categories
Columns:
category_id
title
description
image
status
created_at
Step 6: Sub Categories
Large marketplaces need hierarchy.
Example:
Beverages
|
+ Soft Drinks
+ Water
+ Juice
+ Tea
+ Coffee
Table:
sub_categories
Columns:
sub_category_id
category_id
title
created_at
Relationship:
Category
|
|
Many
Sub Categories
Why Hierarchies Matter
Imagine AQAD has:
100,000 products
Without category hierarchy:
Search becomes difficult.
Reports become difficult.
Navigation becomes difficult.
Proper category structure solves all of these problems.
Step 7: Brands Table
Products belong to brands.
Examples:
Coca-Cola
Pepsi
Nestle
Samsung
Apple
LG
Table:
brands
Columns:
brand_id
brand_name
country
status
created_at
Why Brands Deserve Their Own Table
Bad design:
product_brand
stored as text repeatedly.
Result:
Coca Cola
Coca-Cola
COCA COLA
cocacola
Now reporting breaks.
Separate brands table prevents inconsistency.
Step 8: Products Table
This becomes one of the most important tables.
Table:
products
Columns:
product_id
vendor_id
category_id
sub_category_id
brand_id
title
description
summary
universal_standard_code
status
created_at
updated_at
Notice:
vendor_id
This is the biggest difference between normal e-commerce and AQAD.
Every product belongs to a vendor.
Product Ownership
Example:
Vendor A
|
+ Coca-Cola
+ Pepsi
Vendor B
|
+ Rice
+ Flour
Vendor C
|
+ Samsung TV
Relationship:
Vendor
|
Many Products
This is fundamental to marketplace architecture.
Why Product Ownership Matters
Suppose a retailer buys:
10 Rice Bags
AQAD must know:
- Which vendor owns the product
- Which warehouse ships it
- Who receives payment
- Who updates inventory
Without vendor ownership, none of this works.
Product Table Responsibility
The product table should store:
What the product is
Not:
Inventory
Pricing history
Images
Orders
Those belong elsewhere.
A professional database keeps responsibilities separated.
AQAD Product Lifecycle
Vendor uploads:
Rice 5kg
AQAD stores:
products
Retailer searches.
Retailer views product.
Retailer orders product.
Inventory decreases.
Reports update.
Notifications trigger.
Everything starts from the product table.
Database Design Principle Learned So Far
A common mistake among beginners is:
One Table = Everything
Professional systems use:
One Table = One Responsibility
This principle alone can save years of future problems.
What We Have Built So Far
Current AQAD schema:
Users
|
+ Vendor Profiles
|
+ Retailer Profiles
Vendor Profiles
|
Vendor Documents
Categories
|
Sub Categories
Brands
Vendor Profiles
|
Products
|
Brands
|
Categories
|
Sub Categories
This is already significantly larger than a traditional e-commerce schema.
But we are only getting started.
AQAD Marketplace Database Design (Part 2)
The Challenge of Real Products
So far we have created:
- Users
- Vendors
- Retailers
- Categories
- Sub Categories
- Brands
- Products
Many beginners think:
"Great, the database is finished."
Not even close.
In real marketplaces, products are rarely simple.
Let's look at an AQAD example.
Vendor uploads:
Coca-Cola
Now AQAD asks:
Which one?
250ml Can
330ml Can
500ml Bottle
1 Liter Bottle
2 Liter Bottle
Same product.
Different variations.
Different prices.
Different quantities.
Different inventory.
This changes everything.
Why Product Variations Exist
Imagine we store this:
| Product |
|---|
| Coca-Cola |
That's not enough information.
Retailers need to purchase specific versions.
For example:
Coca-Cola 330ml
and
Coca-Cola 2 Liter
are not the same product.
Price differs.
Weight differs.
Inventory differs.
SKU differs.
Therefore we need a separate table.
Step 9: Product Variations Table
Table:
product_variations
Columns:
variation_id
product_id
sku
size
color
country
price
quantity
status
created_at
Example:
| variation_id | product_id | size |
|---|---|---|
| 1 | 101 | 330ml |
| 2 | 101 | 500ml |
| 3 | 101 | 2L |
Now one product can have many variations.
Relationship
Product
|
|
Many
Variations
Example:
Coca-Cola
|
+ 330ml
+ 500ml
+ 2 Liter
This is a classic One-to-Many relationship.
AQAD Category Examples
Different categories use different variation types.
Fashion
M
L
XL
XXL
Shoes
UK 7
UK 8
UK 9
UK 10
Electronics
128GB
256GB
512GB
Grocery
500g
1kg
5kg
10kg
The variation table allows all of this.
Why Variations Should Not Be Stored in Products
Bad design:
products
contains:
size
color
price
quantity
Problem:
One product can have many sizes.
One product can have many colors.
One product can have many prices.
The design breaks immediately.
Step 10: Product Images Table
Images are critical in marketplaces.
Retailers often decide based on product images.
One product may have:
Front View
Back View
Side View
Package Image
Marketing Banner
Therefore:
product_images
Columns:
image_id
variation_id
image_url
display_order
created_at
Example:
| image_id | variation_id |
|---|---|
| 1 | 101 |
| 2 | 101 |
| 3 | 101 |
Why Separate Images?
Imagine storing:
image1
image2
image3
image4
image5
inside products.
What happens when a vendor uploads 20 images?
The design fails.
Separate image tables are infinitely scalable.
Product Image Relationship
Variation
|
Many Images
Example:
Rice 5kg
|
+ Front
+ Back
+ Nutrition
+ Package
Clean.
Flexible.
Scalable.
Step 11: Warehouses
AQAD vendors may have multiple warehouses.
Example:
Vendor:
Dubai Beverage Trading
Warehouses:
Dubai
Abu Dhabi
Sharjah
One vendor.
Many warehouses.
Table:
warehouses
Columns:
warehouse_id
vendor_id
warehouse_name
address
country
city
status
created_at
Warehouse Relationship
Vendor
|
Many Warehouses
Example:
Vendor A
|
+ Dubai Warehouse
+ Sharjah Warehouse
+ Abu Dhabi Warehouse
Why Warehouses Matter
Without warehouse tracking:
AQAD cannot determine:
- Stock location
- Shipping origin
- Delivery speed
- Regional inventory
Warehouses become extremely important as businesses grow.
Step 12: Inventory Table
Inventory is one of the most frequently updated parts of a database.
Table:
inventory
Columns:
inventory_id
warehouse_id
variation_id
available_quantity
reserved_quantity
updated_at
Example:
| variation_id | quantity |
|---|---|
| 101 | 500 |
| 102 | 1200 |
| 103 | 900 |
Available vs Reserved Quantity
This is an important concept.
Suppose:
100 units available
Retailer places order:
20 units
Payment not completed yet.
Should AQAD sell those 20 units again?
No.
Therefore:
Available = 80
Reserved = 20
This prevents overselling.
Large marketplaces rely heavily on this mechanism.
Real Inventory Flow
Inventory:
Rice 5kg
Available = 1000
Reserved = 0
Retailer places order:
50 units
Inventory becomes:
Available = 950
Reserved = 50
Payment success:
Reserved = 0
Shipment begins.
Everything remains accurate.
Step 13: Shopping Cart
Before placing an order, retailers add products to a cart.
Table:
cart
Columns:
cart_id
retailer_id
created_at
updated_at
Cart Items Table
Table:
cart_items
Columns:
cart_item_id
cart_id
variation_id
quantity
price
created_at
Relationship
Cart
|
Many Cart Items
Example:
Ahmed Supermarket cart:
Coca-Cola
Rice
Oil
Water
Nothing is purchased yet.
Items are simply stored temporarily.
Why Cart and Orders Must Be Separate
A cart means:
Maybe I will buy
An order means:
I bought it
Huge difference.
Many beginners accidentally mix both concepts.
Professional systems never do this.
Step 14: Orders Table
Now comes the heart of the marketplace.
Table:
orders
Columns:
order_id
retailer_id
order_number
total_amount
payment_status
order_status
created_at
updated_at
Order Status Examples
Pending
Confirmed
Packed
Ready For Pickup
In Transit
Delivered
Cancelled
Returned
Real AQAD Scenario
Retailer:
Ahmed Supermarket
places:
100 Coca-Cola
50 Rice
20 Oil
AQAD creates:
Order #AQD-10001
inside:
orders
Step 15: Order Items Table
Products inside an order are stored separately.
Table:
order_items
Columns:
order_item_id
order_id
vendor_id
variation_id
quantity
price
subtotal
created_at
Why Vendor ID Exists Here
This is where marketplace logic becomes powerful.
Example:
Order contains:
Rice → Vendor A
Oil → Vendor B
Water → Vendor C
One order.
Three vendors.
AQAD must know who fulfills each item.
That is why:
vendor_id
exists in order_items.
Multi-Vendor Order Example
Retailer places:
Rice
Oil
Television
Products belong to:
Vendor A
Vendor B
Vendor C
Order:
AQD-10001
Order Items:
Rice → Vendor A
Oil → Vendor B
Television → Vendor C
One retailer order.
Three vendor fulfillments.
This is a true marketplace architecture.
Marketplace Relationship Diagram
Retailer
|
Orders
|
Order Items
|
Products
|
Vendors
This relationship powers Amazon-style marketplaces.
It also powers AQAD.
AQAD Purchase Journey
Let's follow a real order.
Step 1
Retailer logs in.
Step 2
Searches products.
Step 3
Adds products to cart.
Step 4
Creates order.
Step 5
Inventory reserved.
Step 6
Vendor notified.
Step 7
Warehouse prepares shipment.
Step 8
Payment processed.
Step 9
Logistics assigned.
Step 10
Delivery completed.
Every step updates database records.
Every step leaves a data trail.
That trail becomes reports, analytics, notifications, invoices, and tracking information.
Schema Built So Far
Users
|
+ Vendor Profiles
+ Retailer Profiles
Vendor Profiles
|
+ Vendor Documents
+ Warehouses
Categories
|
Sub Categories
Brands
Products
|
Product Variations
|
Product Images
Warehouses
|
Inventory
Retailers
|
Cart
|
Cart Items
Retailers
|
Orders
|
Order Items
At this point AQAD is no longer a simple e-commerce store.
It is becoming a real enterprise marketplace.
AQAD Marketplace Database Design (Part 3)
The Database Behind the Real Business
Now AQAD has the core marketplace tables:
- Users
- Vendors
- Retailers
- Products
- Variations
- Inventory
- Cart
- Orders
- Order Items
But a real marketplace does not stop after order creation.
After an order is placed, many business activities happen behind the scenes.
For example:
- Payment must be confirmed
- Vendor must prepare products
- Logistics partner must pick up order
- Retailer must receive delivery
- Vendor must get settlement
- Notification must be sent
- Admin must track everything
This is where the database becomes the memory of the business.
Step 16: Payments Table
An order and a payment are not the same thing.
Order means:
Retailer wants to buy products
Payment means:
Money movement for that order
So we create a separate table.
payments
Columns:
payment_id
order_id
retailer_id
payment_method
amount
transaction_reference
payment_status
paid_at
created_at
Payment statuses:
Pending
Success
Failed
Refunded
Partially Refunded
This table helps AQAD answer:
Which orders are paid?
Which payments failed?
Which retailer has pending payment?
Which transaction belongs to which order?
Step 17: Vendor Settlements Table
In a marketplace, AQAD may collect payment first.
Later AQAD pays vendors.
This is called settlement.
Example:
Retailer pays:
AED 10,000
Order includes products from:
Vendor A = AED 6,000
Vendor B = AED 4,000
AQAD must settle money correctly.
Table:
vendor_settlements
Columns:
settlement_id
vendor_id
order_id
gross_amount
commission_amount
net_amount
settlement_status
settled_at
created_at
Settlement statuses:
Pending
Processing
Settled
Failed
On Hold
This table is very important for finance teams.
Why Settlements Matter
Without a settlement table, AQAD cannot clearly answer:
How much should each vendor receive?
How much commission did AQAD earn?
Which payments are pending?
Which vendors are already paid?
For a B2B marketplace, payment clarity is not optional.
It is survival.
Step 18: Logistics Partners Table
AQAD also needs delivery management.
A logistics partner may be a company or an individual delivery provider.
Table:
logistics_partners
Columns:
logistics_partner_id
user_id
company_name
phone
vehicle_type
status
created_at
Examples:
FastMove Logistics
Dubai Express Delivery
Al Noor Transport
Step 19: Deliveries Table
Delivery connects an order with a logistics partner.
Table:
deliveries
Columns:
delivery_id
order_id
logistics_partner_id
pickup_warehouse_id
delivery_address
tracking_number
delivery_status
estimated_delivery_time
delivered_at
created_at
Delivery statuses:
Pending
Assigned
Picked Up
In Transit
Delivered
Failed
Returned
Real AQAD Delivery Flow
Order placed.
Inventory reserved.
Vendor packs products.
AQAD assigns logistics partner.
Driver picks product from warehouse.
Retailer receives delivery.
Delivery status becomes:
Delivered
This whole journey is tracked in the deliveries table.
Step 20: Notifications Table
Marketplaces send many notifications.
Examples:
- Vendor approved
- Product approved
- New order received
- Payment successful
- Delivery assigned
- Order delivered
- Inventory low
Table:
notifications
Columns:
notification_id
user_id
title
message
notification_type
is_read
created_at
Notification types:
Order
Payment
Delivery
Inventory
Account
Promotion
This table helps AQAD show notification history inside the app.
Step 21: Activity Logs Table
In a business application, tracking actions is important.
Example:
Vendor updated product price
Admin approved vendor document
Retailer cancelled order
Employee changed order status
Table:
activity_logs
Columns:
log_id
user_id
action
entity_type
entity_id
ip_address
created_at
This table answers:
Who changed what?
When was it changed?
Which record was affected?
This is useful for debugging, security, and business auditing.
Step 22: Roles Table
AQAD employees may have different permissions.
Examples:
- Super Admin
- Support Agent
- Finance Team
- Product Approver
- Operations Manager
Table:
roles
Columns:
role_id
role_name
description
created_at
Step 23: Permissions Table
Permissions define what users can do.
Table:
permissions
Columns:
permission_id
permission_name
module_name
created_at
Examples:
create_product
approve_vendor
view_payment
assign_delivery
refund_payment
Step 24: Role Permissions Table
One role can have many permissions.
One permission can belong to many roles.
So we need a bridge table.
role_permissions
Columns:
role_id
permission_id
Example:
Finance role may have:
view_payment
process_settlement
download_invoice
Support role may have:
view_order
update_ticket
contact_retailer
This is a many-to-many relationship.
Step 25: Analytics Summary Tables
For large platforms, analytics queries can become expensive.
Instead of calculating everything live every time, AQAD can store summary data.
Example table:
daily_sales_summary
Columns:
summary_id
summary_date
vendor_id
total_orders
total_sales
total_commission
created_at
This helps dashboards load faster.
Instead of scanning millions of orders, AQAD reads a smaller summary table.
Complete AQAD Database Flow
Now see the full journey.
User registers
|
Vendor / Retailer profile created
|
Vendor uploads documents
|
Admin verifies account
|
Vendor uploads products
|
Product variations and images added
|
Inventory added to warehouse
|
Retailer adds products to cart
|
Retailer places order
|
Order items created
|
Inventory reserved
|
Payment recorded
|
Vendor settlement prepared
|
Delivery assigned
|
Notifications sent
|
Activity logs stored
|
Analytics updated
This is not just database design.
This is business design.
Complete Schema Overview
users
├── vendor_profiles
│ ├── vendor_documents
│ ├── warehouses
│ └── products
│ ├── product_variations
│ │ ├── product_images
│ │ └── inventory
│ └── order_items
│
├── retailer_profiles
│ ├── carts
│ │ └── cart_items
│ └── orders
│ ├── order_items
│ ├── payments
│ ├── deliveries
│ └── vendor_settlements
│
├── logistics_partners
│ └── deliveries
│
└── notifications
roles
├── role_permissions
└── permissions
activity_logs
daily_sales_summary
Important Design Lesson
A beginner sees AQAD and thinks:
We need product table and order table.
An experienced developer sees AQAD and thinks:
We need identity, onboarding, documents, catalog, inventory, cart,
order lifecycle, payment tracking, settlement, logistics, notifications,
roles, audit logs, and analytics.
That is the difference between small project thinking and real business application thinking.
Common Mistakes in Marketplace Database Design
Mistake 1: No Vendor Ownership
If products are not connected to vendors, settlement becomes impossible.
Mistake 2: No Order Items Table
Without order items, one order cannot properly contain multiple products.
Mistake 3: No Inventory Reservation
Without reserved quantity, the same stock can be sold twice.
Mistake 4: Mixing Payments with Orders
Payment failure, refunds, partial payments, and settlement become difficult.
Mistake 5: No Activity Logs
When something goes wrong, nobody knows who changed what.
Mistake 6: No Role Permission Design
Admin systems become unsafe and messy.
Mini Exercise
Design this situation:
Retailer places one order containing:
Rice from Vendor A
Oil from Vendor B
Water from Vendor C
Question:
Where should vendor information be stored?
Answer:
Inside:
order_items
because each product in the same order may belong to a different vendor.
Try It Yourself
Create a simple schema for:
AQAD Vendor Settlement System
Think about these tables:
vendors
orders
order_items
payments
vendor_settlements
Ask yourself:
- Which vendor sold which item?
- How much was paid?
- What commission did AQAD take?
- Has the vendor been paid?
.png)
.png)
0 Comments