SQL

Entity Relationships in SQL

🔗 Entity Relationships describe how tables in a relational database are connected to one another. These relationships are established using Primary Keys and Foreign Keys, allowing data to be organized efficiently while maintaining integrity and minimizing redundancy.

📖 What are Entity Relationships?

In a relational database, each table represents an entity—such as a customer, employee, product, or order. Relationships define how records in one table are associated with records in another table. These relationships form the foundation of relational database design.

Information

Entity relationships are typically planned during database design using an Entity-Relationship Diagram (ERD) before creating SQL tables.

🎯 Why are Entity Relationships Important?

Relationships enable databases to store related information across multiple tables while preserving consistency and reducing duplicate data.

📌 Reduce data redundancy.
📌 Maintain referential integrity.
📌 Support efficient normalization.
📌 Simplify complex data retrieval using joins.
📌 Improve database scalability and maintainability.

🧩 Core Components

Component	Description
Entity	A real-world object represented as a table.
Attribute	A property of an entity represented as a column.
Primary Key	Uniquely identifies each row.
Foreign Key	References the primary key of another table.
Relationship	Association between two entities.

📋 Example Tables

Consider two entities: Customers and Orders.

Customers
CustomerID (PK)
CustomerName
Email

Orders
OrderID (PK)
CustomerID (FK)
OrderDate
TotalAmount

The CustomerID column in the Orders table references the CustomerID in the Customers table.

🔢 Types of Entity Relationships

1️⃣ One-to-One (1:1)

Each record in one table is related to exactly one record in another table.

Employees	EmployeeDetails
EmployeeID	EmployeeID
Name	PassportNumber

Every employee has one detail record, and each detail record belongs to one employee.

One-to-One Relationship

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE EmployeeDetails (
    EmployeeID INT PRIMARY KEY,
    PassportNumber VARCHAR(50),
    FOREIGN KEY (EmployeeID)
        REFERENCES Employees(EmployeeID)
);

2️⃣ One-to-Many (1:N)

One record in the parent table can have multiple related records in the child table.

Customers	Orders
CustomerID	CustomerID
Name	OrderDate
Email	TotalAmount

One customer can place many orders, but each order belongs to only one customer.

One-to-Many Relationship

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID)
        REFERENCES Customers(CustomerID)
);

3️⃣ Many-to-Many (M:N)

Multiple records in one table can relate to multiple records in another table. This relationship is implemented using a junction (bridge) table.

Students	Enrollments	Courses
StudentID	StudentID (FK)	CourseID
Name	CourseID (FK)	CourseName

Many-to-Many Relationship

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID)
        REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID)
        REFERENCES Courses(CourseID)
);

Important

A many-to-many relationship is always implemented through an intermediate table containing foreign keys from both related tables.

📊 Relationship Summary

Relationship	Description	Example
One-to-One	One record relates to one record.	Person ↔ Passport
One-to-Many	One parent has many children.	Customer → Orders
Many-to-Many	Many records relate to many records.	Students ↔ Courses

🔍 Querying Related Data

Relationships allow data to be retrieved efficiently using SQL joins.

Retrieve Customer Orders

SELECT
    c.CustomerName,
    o.OrderID,
    o.OrderDate
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID;

💼 Real-World Applications

🛒 Customers and orders in e-commerce.
🏦 Customers and bank accounts.
🎓 Students and courses.
🏥 Patients and appointments.
🏢 Employees and departments.
📦 Products and suppliers.

🗄️ Database Compatibility

Entity relationships are supported by all relational database management systems through primary keys, foreign keys, and SQL constraints.

Database System	Relationship Support
MySQL	✅ Yes
PostgreSQL	✅ Yes
SQL Server	✅ Yes
Oracle	✅ Yes
SQLite	✅ Yes (foreign key enforcement must be enabled).

⚠️ Common Mistakes

❌ Missing foreign key constraints.
❌ Using duplicate data instead of relationships.
❌ Creating unnecessary many-to-many relationships.
❌ Forgetting indexes on frequently joined foreign keys.
❌ Confusing entity relationships with SQL joins.

Warning

Defining relationships in the database schema is different from writing JOIN queries. Relationships enforce data integrity, while joins retrieve related data.

⚠️ Best Practices

Best Practice

Design relationships during the database modeling phase, use primary and foreign keys to enforce integrity, normalize data before considering denormalization, create indexes on frequently queried foreign keys, and use meaningful table and column names to make relationships easy to understand.

🚀 Key Points to Remember

📌 Tables represent entities.
📌 Relationships connect entities using keys.
📌 Primary keys uniquely identify records.
📌 Foreign keys establish relationships.
📌 The three primary relationship types are One-to-One, One-to-Many, and Many-to-Many.
📌 Well-designed relationships improve consistency, scalability, and maintainability.

>>"A relational database is only as strong as the relationships that connect its data."

Summary

✅ Entity relationships are the foundation of relational database design. By connecting tables through primary and foreign keys, databases can organize related information efficiently, maintain referential integrity, reduce data duplication, and support powerful SQL queries using joins. Understanding one-to-one, one-to-many, and many-to-many relationships is essential for designing scalable and reliable database systems.