MySQL Data Types, Primary Keys, Foreign Keys & Database Relationships

MySQL Data Types

Choosing the Right Container for Your Data

Imagine AQAD opens a massive warehouse.

Products start arriving every minute.

Some products are:

Rice Bags
Milk Bottles
Juice Cartons

Some items are documents:

Trade Licenses
VAT Certificates
Vendor Agreements

Some information is numbers:

Product Price
Quantity
Order Count

Some information is dates:

Order Date
Delivery Date
Registration Date

Now imagine warehouse employees throw everything into random containers.

Rice goes into water tanks.

Milk goes into cardboard boxes.

Documents are mixed with vegetables.

Very quickly the warehouse becomes chaos.

Databases face the exact same challenge.

Before storing information, the database must know:

What type of data is this?

That is where Data Types come in.

What Is a Data Type?

A data type tells MySQL:

MySQL Data Types, Primary Keys, Foreign Keys & Database Relationships

"What kind of information will be stored in this column?"

Think of it as a label on a storage container.

For example:

A container labeled:

"Rice"

Should contain rice.

Not water.

Similarly:

A column labeled:

Price

Should contain numbers.

Not paragraphs of text.

Data types help databases:

Store information correctly
Save storage space
Improve performance
Prevent mistakes

Why Data Types Matter

Imagine AQAD stores product prices.

Correct:

10
25
100

Incorrect:

Ten
Twenty Five
One Hundred

If prices are stored as text, calculations become difficult.

The database needs to know:

"This column contains numbers."

Only then can it:

Calculate totals
Calculate averages
Sort properly
Generate reports

This is why choosing the correct data type is important.

Major Categories of Data Types

In MySQL, data types are generally grouped into:

Numeric Types
String Types
Date and Time Types
Boolean Types
Special Types

Let's understand each one.

Numeric Data Types

Numeric types store numbers.

Examples:

Product Price
Inventory Quantity
Order Count
Customer Age

INT (Integer)

The most commonly used numeric type.

Stores whole numbers.

Examples:

AQAD Examples:

Vendor ID

101
102
103

Product Quantity

500
1000
2500

Table Example:

Product Name	Quantity
Milk	500
Rice	2000

Quantity should use INT.

BIGINT

Used for very large numbers.

Suppose AQAD grows globally.

Orders become:

1000000000

Regular INT may eventually become insufficient.

BIGINT handles extremely large values.

Usually used for:

Large identifiers
Huge transaction counts

DECIMAL

One of the most important types for business applications.

Used for:

Prices
Payments
Revenue
Tax Calculations

Example:

10.50
99.99
1500.75

AQAD Product Price:

12.99 AED

Should use:

DECIMAL(10,2)

Meaning:

10 total digits

2 digits after decimal

Examples:

99999999.99

Why Not Use FLOAT for Money?

Many beginners make this mistake.

Example:

0.1 + 0.2

Sometimes computers produce unexpected precision errors.

For money calculations:

Always prefer DECIMAL.

Banks, e-commerce platforms, and payment systems use DECIMAL.

AQAD payment calculations should also use DECIMAL.

String Data Types

Strings store text.

Examples:

Names
Emails
Addresses
Product Titles

CHAR

Stores fixed-length text.

Example:

Country Code

UAE
IND
USA

Every value has similar length.

CHAR works well.

Example:

CHAR(3)

VARCHAR

The most commonly used text type.

Stores variable-length text.

AQAD Examples:

Vendor Name

Fresh Farm LLC

Product Name

Organic Milk 1L

Retailer Name

ABC Supermarket

Example:

VARCHAR(255)

Maximum:

255 characters.

Why VARCHAR Is Popular

Imagine:

Vendor Name:

Ali

Only 3 characters.

VARCHAR stores only required space.

This saves storage.

Unlike CHAR which reserves fixed space.

TEXT

Used for large text content.

AQAD Product Description:

Premium organic milk sourced from grass-fed cows...

Descriptions can become long.

TEXT is perfect for:

Product descriptions
Vendor notes
Admin remarks
Blog content

Example:

description TEXT

Date and Time Data Types

Applications constantly track time.

AQAD records:

Registration Date
Order Date
Delivery Date
Payment Date

Date types handle this information.

DATE

Stores only the date.

Example:

2026-06-14

Useful for:

Birth Dates
Joining Dates
Expiry Dates

AQAD Example:

Vendor Registration Date

TIME

Stores only time.

Example:

14:30:00

Useful for:

Delivery Schedules
Meeting Times

DATETIME

Stores both date and time.

Example:

2026-06-14 14:30:00

AQAD Example:

Order Created At

Payment Completed At

Delivery Completed At

This is one of the most frequently used types.

TIMESTAMP

Very similar to DATETIME.

Used heavily for tracking events.

Example:

created_at
updated_at

Most production systems use timestamps extensively.

AQAD Examples:

Product Created

Vendor Updated

Order Status Changed

Payment Processed

Boolean Data Type

Sometimes information is simply:

Yes or No

True or False

1 or 0

BOOLEAN

Examples:

Vendor Verified?

TRUE

Order Paid?

FALSE

Retailer Active?

TRUE

Very useful for status flags.

AQAD User Table Example

Imagine:

User	Verified
Vendor A	TRUE
Vendor B	FALSE

Boolean handles this perfectly.

Common Data Types Used in AQAD

Let's design part of a users table.

user_id INT

name VARCHAR(255)

email VARCHAR(255)

phone VARCHAR(20)

is_verified BOOLEAN

created_at DATETIME

Notice how every field uses a different type depending on its purpose.

Product Table Example

product_id INT

title VARCHAR(255)

description TEXT

price DECIMAL(10,2)

quantity INT

created_at DATETIME

Each column uses the most suitable data type.

Order Table Example

order_id INT

retailer_id INT

total_amount DECIMAL(10,2)

is_paid BOOLEAN

order_date DATETIME

This is how real-world database design works.

Data Type Selection Analogy

Think of moving house.

You have:

Books

Clothes

Furniture

Electronics

Would you place everything inside the same box?

No.

You choose containers based on the item.

Databases work exactly the same way.

Different data requires different storage types.

How Data Types Improve Performance

Imagine AQAD stores 1 million products.

Correct data types help:

Faster searching
Faster sorting
Better indexing
Lower storage usage

Poor data types cause:

Wasted storage
Slower queries
Difficult maintenance

Good database design starts with choosing correct data types.

Common Beginner Mistakes

Mistake 1

Using VARCHAR for everything.

Many beginners create:

name VARCHAR(255)

price VARCHAR(255)

quantity VARCHAR(255)

Wrong.

Prices and quantities should be numeric.

Mistake 2

Using FLOAT for Money

Use DECIMAL instead.

Mistake 3

Using Huge VARCHAR Values Everywhere

Example:

VARCHAR(5000)

when only:

VARCHAR(100)

is needed.

Mistake 4

Storing Dates as Text

Bad:

June 14 2026

Good:

DATE

DATETIME

Mistake 5

Ignoring Future Growth

Think about scale.

AQAD may grow from:

100 products

100,000 products

Data type choices should support future growth.

Mini Exercise

Choose the correct data type.

Product Price

Answer:

DECIMAL(10,2)

Product Name

Answer:

VARCHAR(255)

Quantity

Answer:

INT

Product Description

Answer:

TEXT

Order Date

Answer:

DATETIME

Try It Yourself

Imagine you are designing AQAD's database.

Choose data types for:

Vendor Name
Vendor Email
Product Price
Inventory Quantity
Order Date
Payment Status

Write your answers before checking future chapters.

This helps develop database design thinking.

Real Developer Insight

One of the easiest ways to identify an experienced database developer is by looking at their data type choices.

Beginners often think:

"Everything can be VARCHAR."

Experienced developers think:

"What is the most appropriate type for this data?"

That mindset creates better databases.

Primary Keys

How Databases Identify Every Record Uniquely

Imagine AQAD becomes one of the largest B2B marketplaces in the Middle East.

The platform now has:

50,000 Vendors
500,000 Retailers
100,000 Products
Millions of Orders

Everything seems fine.

Then one day customer support receives a call.

A retailer says:

"I want details about my order."

Support asks:

"Which order?"

The retailer replies:

"Order for Milk."

The problem?

Thousands of orders contain Milk.

Support asks:

"What's your company name?"

The retailer says:

"ABC Supermarket."

Problem again.

There are several branches of ABC Supermarket.

Now support is confused.

Which exact order belongs to this retailer?

This is the problem Primary Keys solve.

The Identity Card Analogy

Imagine a country with 100 million citizens.

Many people can have the same:

First Name
Last Name
City
Profession

For example:

There may be thousands of people named:

Mohammed Ahmed

If the government used names as identities, chaos would occur.

Instead, every citizen receives a unique ID number.

Example:

Citizen Name: Mohammed Ahmed

National ID: 784512369

Even if 10,000 people have the same name, the ID number remains unique.

Databases work exactly the same way.

Every row needs a unique identity.

That identity is called a Primary Key.

What Is a Primary Key?

A Primary Key is a column (or combination of columns) that uniquely identifies each row in a table.

In simple words:

A Primary Key is the identity card of a record.

No two rows can have the same Primary Key value.

Why Do We Need Primary Keys?

Imagine AQAD's products table.

Without a primary key:

Product Name	Price
Milk	10
Milk	12
Milk	15

Now imagine a vendor wants to update one Milk product.

Which row should the database update?

Nobody knows.

The records look identical.

This creates confusion.

Primary Keys eliminate this problem.

AQAD Product Table with Primary Key

Product ID	Product Name	Price
1	Milk	10
2	Milk	12
3	Milk	15

Now every product has a unique identity.

Even if names are identical, Product IDs are different.

The database can easily identify the correct record.

Characteristics of a Primary Key

A good Primary Key follows three rules.

Rule 1: Must Be Unique

Every value must be different.

Valid:

Product ID
1
2
3

Invalid:

Product ID
1
1
3

Duplicate values are not allowed.

Rule 2: Cannot Be NULL

Every record must have an identity.

Valid:

Product ID
1
2
3

Invalid:

Product ID
1
NULL
3

A record without identity creates confusion.

Rule 3: Should Remain Stable

Primary Keys should rarely change.

Example:

Vendor Name:

Fresh Farm LLC

could change to:

Fresh Farm International LLC

Therefore names are poor Primary Keys.

A numeric ID remains stable.

Creating a Primary Key

Suppose we create AQAD's products table.

CREATE TABLE products (

product_id INT PRIMARY KEY,

product_name VARCHAR(255),

price DECIMAL(10,2)

);

Notice:

PRIMARY KEY

This tells MySQL:

"This column uniquely identifies every record."

Inserting Data

INSERT INTO products

VALUES

(1,'Milk',10),

(2,'Rice',25),

(3,'Juice',15);

Everything works.

Duplicate Primary Key Example

Suppose we try:

INSERT INTO products

VALUES

(1,'Coffee',20);

Problem.

Product ID 1 already exists.

MySQL rejects the insert.

This protects data integrity.

AUTO_INCREMENT

Imagine AQAD receives thousands of products daily.

Manually assigning IDs becomes impossible.

Example:

Vendor A adds product.

ID = 1

Vendor B adds product.

ID = 2

Vendor C adds product.

ID = 3

Doing this manually would be risky.

MySQL provides:

AUTO_INCREMENT

Creating Auto Increment IDs

CREATE TABLE products (

product_id INT AUTO_INCREMENT PRIMARY KEY,

product_name VARCHAR(255),

price DECIMAL(10,2)

);

Now IDs are generated automatically.

Example

Insert:

INSERT INTO products
(product_name,price)

VALUES

('Milk',10);

Database creates:

Product ID	Product Name
1	Milk

Insert again:

INSERT INTO products
(product_name,price)

VALUES

('Rice',25);

Database creates:

Product ID	Product Name
2	Rice

Developers don't need to manage IDs manually.

AQAD User Example

Users Table

User ID	Name
101	Ahmed
102	Fatima
103	Ali

Even if two users have the same name:

User ID	Name
104	Ahmed
105	Ahmed

No problem.

User IDs remain unique.

AQAD Orders Example

Orders are one of the best examples of Primary Keys.

Imagine AQAD processes:

50,000 orders daily.

Each order receives:

Order ID
1001
1002
1003

Order IDs uniquely identify every transaction.

Without them, order management would become impossible.

Natural Keys vs Surrogate Keys

As developers gain experience, they encounter two concepts.

Natural Key

A value that already exists naturally.

Examples:

Email
Passport Number
National ID

Example:

ahmed@gmail.com

Could potentially be unique.

Problems with Natural Keys

What if:

Email changes?
Passport expires?
User updates information?

Primary Keys should remain stable.

Natural values often change.

Surrogate Key

An artificial ID created specifically for identification.

Example:

User ID = 101

This value has no business meaning.

Its only purpose is identification.

Most modern systems prefer surrogate keys.

Why AQAD Should Use Surrogate Keys

Consider:

Vendor Email:

freshfarm@gmail.com

Today.

Tomorrow:

support@freshfarm.com

Email changed.

If email was the Primary Key, updating relationships becomes difficult.

Instead:

Vendor ID = 5001

Never changes.

This makes database management easier.

Primary Keys and CRUD Operations

Let's connect this with CRUD.

Create

INSERT INTO products
(product_name)

VALUES
('Milk');

Database generates Product ID.

Read

SELECT *
FROM products

WHERE product_id = 1;

Specific record retrieved.

Update

UPDATE products

SET price = 12

WHERE product_id = 1;

Correct product updated.

Delete

DELETE FROM products

WHERE product_id = 1;

Correct product removed.

Notice something important.

Primary Keys make CRUD operations precise.

Primary Keys Improve Performance

Imagine AQAD stores:

100 million products.

Without Primary Keys:

Database searches become slower.

With Primary Keys:

Database can locate records quickly.

Think of a library.

Searching by:

Book Title

may return many results.

Searching by:

ISBN Number

returns exactly one book.

Primary Keys work similarly.

Real AQAD Tables and Their Primary Keys

Users Table

user_id

Products Table

product_id

Orders Table

order_id

Payments Table

payment_id

Deliveries Table

delivery_id

Every important table needs a Primary Key.

Common Beginner Mistakes

Mistake 1

Using Names as Primary Keys

Bad:

Ahmed
Fatima
Ali

Names can repeat.

Mistake 2

Using Email as Primary Key

Emails can change.

Mistake 3

Not Using AUTO_INCREMENT

Manual ID management creates errors.

Mistake 4

Updating Primary Keys Frequently

Primary Keys should remain stable.

Mistake 5

Creating Tables Without Primary Keys

Every major table should have one.

Mini Exercise

Choose the best Primary Key.

Users Table

Options:

Name
Email
User ID

Answer:

User ID

Products Table

Options:

Product Name
Product ID

Answer:

Product ID

Orders Table

Options:

Order Date
Order ID

Answer:

Order ID

Try It Yourself

Imagine AQAD launches a new module.

Create Primary Keys for:

Vendors
Retailers
Products
Orders
Payments
Deliveries

Possible answers:

vendor_id

retailer_id

product_id

order_id

payment_id

delivery_id

This exercise helps develop database design skills.

Real Developer Insight

One of the first things experienced database architects look for is:

"Where is the Primary Key?"

If a table lacks a proper Primary Key, future development becomes difficult.

Relationships become harder.

Queries become slower.

Data integrity becomes weaker.

A well-designed database almost always starts with strong Primary Keys.

Foreign Keys

How Databases Build Relationships Between Tables

we learned about Primary Keys. We discovered that every record needs a unique identity.

For example:

Products Table

Product ID	Product Name
1	Milk
2	Rice
3	Juice

The Product ID uniquely identifies each product.

That's great.

But now we face a new challenge.

Imagine AQAD has:

Vendors
Retailers
Products
Orders
Payments
Deliveries

How do we connect them together?

How does the database know:

Which vendor owns a product?
Which retailer placed an order?
Which payment belongs to which order?
Which delivery belongs to which order?

This is where Foreign Keys become important.

The Family Relationship Analogy

Let's start with a simple analogy.

Imagine a family.

Father:

Father ID = 1
Name = Ahmed

Child:

Child ID = 101
Name = Ali
Father ID = 1

Notice something interesting.

The child record stores:

Father ID = 1

This creates a relationship.

The child is connected to the father.

Without that connection, we wouldn't know who belongs to whom.

Databases use Foreign Keys in exactly the same way.

What Is a Foreign Key?

A Foreign Key is a column in one table that references the Primary Key of another table.

In simple English:

A Foreign Key creates a relationship between tables.

Think of it as a bridge.

Primary Key

⬇

Foreign Key

The bridge allows tables to communicate.

AQAD Without Foreign Keys

Imagine AQAD products table.

Product ID	Product Name
1	Milk
2	Rice

And vendors table.

Vendor ID	Vendor Name
101	Fresh Farm LLC
102	Gulf Foods

Question:

Which vendor owns Milk?

We don't know.

The tables are disconnected.

The database has no relationship information.

AQAD With Foreign Keys

Products Table

Product ID	Product Name	Vendor ID
1	Milk	101
2	Rice	102

Vendors Table

Vendor ID	Vendor Name
101	Fresh Farm LLC
102	Gulf Foods

Now everything makes sense.

Milk belongs to Vendor 101.

Vendor 101 is Fresh Farm LLC.

The relationship is established.

Understanding the Connection

Let's visualize it.

Vendors Table

Vendor ID = 101
Vendor Name = Fresh Farm LLC

Products Table

Product ID = 1
Product Name = Milk
Vendor ID = 101

Notice:

The Product table stores Vendor ID.

Vendor ID already exists as the Primary Key inside Vendors table.

Therefore:

products.vendor_id

becomes a Foreign Key.

Real AQAD Example

Imagine a vendor uploads 500 products.

Would it make sense to store:

Fresh Farm LLC

inside every product record?

No.

That creates duplication.

Instead we store:

Vendor ID = 101

This is:

Smaller
Faster
More efficient

Databases love identifiers.

Creating a Foreign Key

Let's create tables.

Vendor Table

CREATE TABLE vendors (

vendor_id INT PRIMARY KEY,

vendor_name VARCHAR(255)

);

Now Products Table.

CREATE TABLE products (

product_id INT PRIMARY KEY,

product_name VARCHAR(255),

vendor_id INT,

FOREIGN KEY (vendor_id)

REFERENCES vendors(vendor_id)

);

Notice:

FOREIGN KEY (vendor_id)

REFERENCES vendors(vendor_id)

This creates the relationship.

What Happens Behind the Scenes?

Imagine Vendors Table contains:

Vendor ID
101
102

Now we insert:

INSERT INTO products

VALUES

(1,'Milk',101);

Success.

Vendor 101 exists.

Now try:

INSERT INTO products

VALUES

(2,'Rice',999);

Problem.

Vendor 999 doesn't exist.

MySQL rejects the insert.

Why?

Because the Foreign Key protects data integrity.

Referential Integrity

This sounds like a scary database term.

But the idea is simple.

Referential Integrity means:

Relationships must remain valid.

If a product claims:

Vendor ID = 101

Then Vendor 101 must exist.

Otherwise the relationship becomes broken.

Foreign Keys enforce this rule automatically.

AQAD Order Example

Let's build something more realistic.

Retailers Table

Retailer ID	Name
201	ABC Supermarket
202	Smart Mart

Orders Table

Order ID	Retailer ID
5001	201
5002	202

Question:

Who placed Order 5001?

Answer:

Retailer ID = 201

↓

ABC Supermarket

Foreign Keys make this possible.

AQAD Payment Example

Orders Table

Order ID
5001
5002

Payments Table

Payment ID	Order ID
9001	5001
9002	5002

Now every payment belongs to a specific order.

Relationship established.

AQAD Delivery Example

Orders Table

Order ID
5001

Deliveries Table

Delivery ID	Order ID
7001	5001

Now we know:

Delivery 7001 belongs to Order 5001.

Again, Foreign Keys create the connection.

Why Foreign Keys Matter

Imagine AQAD without Foreign Keys.

Products may reference vendors that don't exist.

Orders may reference retailers that don't exist.

Payments may reference orders that don't exist.

The database becomes unreliable.

Foreign Keys prevent these problems.

Database Relationship Visualization

Think of AQAD like a city.

Vendors

↓

Products

↓

Orders

↓

Payments

↓

Deliveries

Every connection is built using Foreign Keys.

Without them, everything becomes isolated.

What Happens When Data Is Deleted?

Now let's discuss a common problem.

Vendor Table

Vendor ID
101

Products Table

Product ID	Vendor ID
1	101

Question:

What happens if Vendor 101 is deleted?

The product still references Vendor 101.

Now the relationship is broken.

Databases handle this using delete rules.

ON DELETE RESTRICT

Most strict option.

If products exist:

Vendor cannot be deleted.

Example:

ON DELETE RESTRICT

Database says:

"No."

Delete operation fails.

AQAD Example

Fresh Farm LLC has:

500 products.

Someone tries:

DELETE FROM vendors
WHERE vendor_id = 101;

Database blocks the action.

This prevents accidental data loss.

ON DELETE CASCADE

This option automatically deletes related records.

Example:

Vendor deleted.

↓

Products deleted.

↓

Relationships remain valid.

Configuration:

ON DELETE CASCADE

AQAD Example

Delete Vendor:

Fresh Farm LLC

Database automatically removes:

Products
Related records

Everything connected disappears.

This is powerful but dangerous.

Use carefully.

ON DELETE SET NULL

Another option.

Vendor deleted.

Instead of deleting products:

Database sets:

vendor_id = NULL

The relationship is removed.

Products remain.

Useful in some business cases.

Cascade Update

Suppose:

Vendor ID changes.

Rare, but possible.

Example:

101 → 5001

Instead of manually updating products:

ON UPDATE CASCADE

Database automatically updates all related rows.

Very useful in large systems.

Real AQAD Relationships

Let's map the marketplace.

Users

↓

Vendors

↓

Products

↓

Orders

↓

Order Items

↓

Payments

↓

Deliveries

Each connection uses Foreign Keys.

This creates a complete business network.

Primary Key vs Foreign Key

Beginners often confuse these.

Let's simplify.

Primary Key:

Identity card.

Example:

vendor_id

Uniquely identifies vendor.

Foreign Key:

Relationship bridge.

Example:

products.vendor_id

Connects product to vendor.

Think:

Primary Key = Identity

Foreign Key = Relationship

Common Beginner Mistakes

Mistake 1

Not Creating Foreign Keys

Tables become disconnected.

Mistake 2

Using Names Instead of IDs

Bad:

Vendor Name = Fresh Farm LLC

Good:

Vendor ID = 101

IDs are faster and more reliable.

Mistake 3

Ignoring Referential Integrity

Broken relationships create bad data.

Mistake 4

Using CASCADE Everywhere

Can accidentally delete large amounts of data.

Mistake 5

Deleting Parent Records Carelessly

Always understand dependencies first.

Mini Exercise

Identify the Foreign Key.

Vendors Table

vendor_id

Products Table

product_id

vendor_id

Answer:

products.vendor_id

Because it references:

vendors.vendor_id

Try It Yourself

Design Foreign Keys for AQAD.

Tables:

Vendors
Products
Orders
Payments
Deliveries

Possible Answer:

products.vendor_id

orders.retailer_id

payments.order_id

deliveries.order_id

This exercise helps build relationship-design skills.

Real Developer Insight

When junior developers first learn databases, they focus on tables.

Experienced developers focus on relationships.

Because businesses are built on relationships.

AQAD isn't just:

Products.

It's:

Vendors → Products

Retailers → Orders

Orders → Payments

Orders → Deliveries

Foreign Keys allow databases to represent these real-world relationships accurately.

Database Relationships

How Real Businesses Connect Information

we learned about Foreign Keys.

We discovered that Foreign Keys allow tables to connect with each other.

But now another question appears.

If AQAD has:

Vendors
Retailers
Products
Orders
Payments
Deliveries

How exactly should these tables be connected?

Should one vendor have one product?

Should one retailer have one order?

Can one product belong to many orders?

Can one order contain many products?

To answer these questions, database designers use relationship patterns.

Think of them as blueprints.

These blueprints help us model real-world businesses correctly.

If relationships are designed badly, the database becomes confusing.

If relationships are designed correctly, the entire system becomes easier to build, maintain, and scale.

A City Analogy

Imagine a city.

Inside the city there are:

People
Houses
Schools
Hospitals
Companies

Nothing exists in isolation.

Everything is connected.

A person lives in a house.

A student attends a school.

An employee works for a company.

A patient visits a hospital.

Databases work exactly the same way.

Tables are connected because real-world entities are connected.

This is why relationships are one of the most important topics in database design.

The Three Main Relationship Types

Almost every relational database uses three relationship patterns.

One-to-One (1:1)
One-to-Many (1:N)
Many-to-Many (M:N)

Understanding these three patterns will allow you to design most applications.

Let's explore each one.

One-to-One Relationship (1:1)

A One-to-One relationship means:

One record is connected to exactly one other record.

Think:

One Person

↓

One Passport

A passport belongs to only one person.

A person has only one passport.

Relationship:

Person ↔ Passport

One on both sides.

AQAD One-to-One Example

Imagine AQAD stores vendor information.

Vendor Table

Vendor ID	Vendor Name
101	Fresh Farm LLC

Vendor Verification Table

Verification ID	Vendor ID	Status
1	101	Approved

Notice:

Vendor 101 has one verification record.

Verification record belongs to one vendor.

Relationship:

Vendor ↔ Verification

One-to-One.

Why Use One-to-One?

Beginners often ask:

Why not store everything in one table?

Good question.

Sometimes extra information:

Is optional
Is sensitive
Changes independently

Separating data improves organization.

Example:

AQAD Vendor Table

Stores:

Name
Email
Phone

Verification Table

Stores:

Trade License
VAT Certificate
Approval Status

Cleaner design.

One-to-Many Relationship (1:N)

This is the most common relationship in databases.

One record can have many related records.

Think:

One Teacher

↓

Many Students

One teacher teaches many students.

Each student belongs to one teacher.

AQAD Vendor and Products

This relationship is everywhere.

Vendor:

Fresh Farm LLC

can sell:

Milk
Cheese
Butter
Yogurt
Juice

One Vendor

↓

Many Products

Relationship:

Vendor → Products

This is One-to-Many.

Database Representation

Vendors Table

Vendor ID	Vendor Name
101	Fresh Farm LLC

Products Table

Product ID	Product Name	Vendor ID
1	Milk	101
2	Cheese	101
3	Butter	101

Notice:

Vendor ID repeats.

This is normal.

The Vendor table stores one record.

The Product table stores many records referencing that vendor.

Another AQAD Example

Retailers and Orders.

ABC Supermarket places:

Order 1

Order 2

Order 3

Order 4

Relationship:

One Retailer

↓

Many Orders

Database:

Orders Table contains:

retailer_id

Foreign Key.

Why One-to-Many Is Everywhere

Most business relationships follow this pattern.

Examples:

Customer → Orders

Vendor → Products

Category → Products

Order → Payments

Order → Notifications

Because one entity usually owns or creates many related entities.

Many-to-Many Relationship (M:N)

Now let's look at the most interesting relationship type.

Imagine AQAD orders.

Question:

Can one order contain many products?

Yes.

Example:

Order 5001

Contains:

Milk
Rice
Juice

Now another question.

Can Milk appear in many orders?

Also yes.

Milk might appear in:

Order 5001
Order 5002
Order 5003
Order 5004

Now we have:

Many Orders

↔

Many Products

This becomes a Many-to-Many relationship.

The Classroom Analogy

Imagine a school.

Students:

Ahmed
Fatima
Ali

Courses:

Mathematics
Science
English

One student can enroll in many courses.

One course can have many students.

Relationship:

Students

↔

Courses

Many-to-Many.

Why Many-to-Many Is Special

Databases cannot directly store Many-to-Many relationships efficiently.

Instead we create a special table.

This table is called:

Junction Table
Bridge Table
Mapping Table

All mean the same thing.

AQAD Order Example

Orders

Order ID
5001
5002

Products

Product ID
1
2
3

Now create:

Order Items Table

Order ID	Product ID
5001	1
5001	2
5001	3
5002	1

Notice what happened.

Order 5001 contains:

Milk

Rice

Juice

Order 5002 contains:

Milk

The relationship is now properly modeled.

Why Order Items Is Important

Beginners often create:

Orders Table

Order ID

Product 1

Product 2

Product 3

This becomes messy.

What if an order has:

10 products?

50 products?

100 products?

The design breaks.

Order Items solves this problem elegantly.

Visualizing Many-to-Many

Without Bridge Table

Orders ↔ Products

Complicated.

With Bridge Table

Orders
   ↓
Order_Items
   ↑
Products

Simple and scalable.

AQAD Marketplace Relationship Map

Let's visualize AQAD.

Vendor
  ↓
Products

Retailer
  ↓
Orders
  ↓
Order Items
  ↑
Products

Orders
  ↓
Payments

Orders
  ↓
Deliveries

This resembles real production systems.

Understanding Cardinality

A fancy database word you'll hear often is:

Cardinality

It simply means:

"How many records can be related?"

Examples:

One Vendor

↓

Many Products

Cardinality:

1:N

One Order

↓

Many Order Items

Cardinality:

1:N

Many Orders

↓

Many Products

Cardinality:

M:N

Don't let the word scare you.

It's just relationship counting.

How Relationship Design Affects Performance

Imagine AQAD reaches:

100,000 Vendors
1 Million Products
10 Million Orders

Poor relationship design creates:

Slow queries
Duplicate data
Difficult maintenance

Good relationship design creates:

Faster queries
Better organization
Easier scaling

Database architects spend significant time designing relationships correctly.

Common Beginner Mistakes

Mistake 1

Ignoring Relationships

Creating disconnected tables.

Mistake 2

Using Text Instead of IDs

Bad:

Vendor Name

Good:

Vendor ID

IDs are smaller and faster.

Mistake 3

Trying to Store Arrays in Columns

Bad:

Order:

Milk,Rice,Juice

Hard to manage.

Use Order Items table instead.

Mistake 4

Creating Huge Tables

Separating related data improves design.

Mistake 5

Not Thinking About Growth

Design should support:

100 records

and

100 million records.

Mini Exercise

Identify the relationship.

One Vendor

Many Products

Answer:

One-to-Many

One Retailer

Many Orders

Answer:

One-to-Many

Many Orders

Many Products

Answer:

Many-to-Many

Vendor

Verification Record

Answer:

One-to-One

Try It Yourself

Design relationships for AQAD.

Tables:

Vendors
Products
Orders
Retailers

Questions:

Vendor → Products?

Answer:

One-to-Many

Retailer → Orders?

Answer:

One-to-Many

Orders → Products?

Answer:

Many-to-Many

(using Order Items)

This exercise builds real database modeling skills.

Real Developer Insight

Junior developers often focus on tables.

Senior developers focus on relationships.

Why?

Because businesses are relationships.

AQAD isn't simply:

Products.

It's:

Vendor owns Product.

Retailer places Order.

Order contains Products.

Order generates Payment.

Order generates Delivery.

The better you understand relationships, the better database designer you become.

Preparing for Joins

Now that we understand relationships, we face a new challenge.

Suppose AQAD asks:

"Show all products with vendor names."

Problem:

Vendor Name is in Vendors table.

Product Name is in Products table.

The information is split across tables.

How do we combine it?

That is exactly what Joins do.

And Joins are one of the most important SQL skills every developer must learn.