Thursday, August 14, 2025
EN | عربي
قواعد البيانات

type of relationship in sql

Monday, July 28, 2025 10 دقائق قراءة 10 مشاهدة

Types of relationships in databases are important for understanding how tables are related.


When designing a database, it's important to understand how different pieces of data relate to each other. These relationships help keep the data organized, reduce duplication, and make it easier to retrieve meaningful information. Before diving into complex structures, let’s start by exploring the basic types of relationships between tables.


Basic SQL Relationships


 One-to-One (1:1)

Each record in Table A relates to exactly one record in Table B.


table Users and table User Profiles

Every user has their own profile, and every profile is related to one user.

-- Users table
CREATE TABLE users (id, name, email);

-- User profiles table  
CREATE TABLE profiles (id, user_id UNIQUE, bio, avatar);

One-to-Many (1:N)

One record in Table A can relate to multiple records in Table B,But every record in Table b relate to one record in Table A

Categories table and Posts table

every Categorie has multi Post but every Post belong to one Categorie

-- Categories table
CREATE TABLE categories (id, name);
-- Posts table
CREATE TABLE posts (id, title, category_id);

Many-to-Many (M:N)

Each record in one table can be linked to multiple records in another table, and vice versa. This relationship is usually managed through a pivot table.

Students table and Courses table and Junction table

student can take many Courses and Course have many Students

-- Students table
CREATE TABLE students (id, name);
-- Courses table
CREATE TABLE courses (id, name);
-- Junction table
CREATE TABLE student_courses (student_id, course_id);

Complex Relationships

 Self-Referencing Relationship

A Self-Referencing Relationship occurs when a record in a table is related to another record within the same table.



Employees table with manager relationship

An Employees table where each employee may have a manager, and that manager is also listed in the same Employees table.

-- Employees table with manager relationship
CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

Polymorphic Relationship

A Polymorphic Relationship allows a model (or table) to belong to more than one other model on a single association.


A Comment can belong to either a Post, a Video, or an Image.

Instead of having separate comment tables for each type, we use:

  • commentable_id: the ID of the related item
  • commentable_type: the name of the related model/table


Polymorphic Relationship Table for Comments

SQL code defines a comments table that implements a polymorphic relationship, allowing each comment to be associated with either a post or a video. It uses two fields:

-- Comments can belong to posts OR videos
CREATE TABLE comments (
    id INT,
    content TEXT,
    commentable_id INT,
    commentable_type VARCHAR(50) -- 'post' or 'video'
);

Many-to-Many with Pivot Data

This is a Many-to-Many relationship where the junction table also stores additional information about the relationship itself.

Pivot Table with Additional Data for Student-Course Relationship

This SQL statement creates a pivot table named course_student to represent a many-to-many relationship between students and courses. In addition to storing foreign keys (student_id, course_id), the table also contains extra fields: grade: to store the student’s grade in the course. enrolled_at: to track the date the student enrolled.

CREATE TABLE course_student (
    student_id INT,
    course_id INT,
    grade FLOAT,
    enrolled_at DATE
);

Conditional Relationships

nditional Relationships are relationships between tables that only apply when a specific condition is met.

These relationships are not always enforced or available for all records — they depend on certain values or states.

Selecting Active Employees with Assigned Managers

This SQL query retrieves all records from the employees table where the employee's status is 'active' and they have a manager assigned (manager_id is not null).

SELECT * FROM employees
WHERE status = 'active' AND manager_id IS NOT NULL;

Temporal Relationships – Time-based Data Associations

Temporal relationships refer to associations between data that depend on time.

  • which employee held a specific position in March, or
  • What was the price of a product on a certain date,
  • we need to store time ranges or timestamps along with the relationships.



Title: Employee Job History Table

This table records the history of job assignments for each employee, including the department they worked in and the time period of the assignment. It allows tracking of when an employee started and ended a position, making it useful for temporal queries such as "Which department was the employee in during a specific month?" or "What roles has the employee held over time?"

-- Employee job history
CREATE TABLE job_assignments (
    employee_id INT,
    department_id INT,
    start_date DATE,
    end_date DATE NULL
);


Tags:

#tag3
← Back to Articles More from Databases
Keyboard