Understanding Database Relationships with SQL and PostgreSQL Syntax

Table of Contents

1. One-to-One Relationship
2. One-to-Many Relationship
3. Many-to-Many Relationship
4. Importance of Relationships

In relational databases, relationships are crucial for organizing and linking data across different tables. They define how records in one table correspond to records in another, ensuring data consistency, integrity, and efficient querying. Relationships are typically established using foreign keys that reference primary keys in related tables.

1. One-to-One Relationship

In a one-to-one relationship, each record in one table is linked to only one record in another table. This type of relationship is often used when storing optional or sensitive information in a separate table.

Example:

Imagine two tables: Person and Passport. Each person has only one passport, and each passport is issued to only one person.

Data Example:

Person Table

    | PersonID | Name   | Age  |
    |----------|--------|------|
    | 1        | John   | 30   |
    | 2        | Alice  | 25   |
    

Passport Table

    | PassportID | PersonID | PassportNumber |
    |------------|----------|----------------|
    | 101        | 1        | P123456        |
    | 102        | 2        | P654321        |
    

SQL Server Syntax:

    CREATE TABLE Person (
        PersonID INT PRIMARY KEY,
        Name NVARCHAR(50),
        Age INT
    );

    CREATE TABLE Passport (
        PassportID INT PRIMARY KEY,
        PersonID INT UNIQUE,
        PassportNumber NVARCHAR(20),
        FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
    );
    

PostgreSQL Syntax:

    CREATE TABLE Person (
        PersonID SERIAL PRIMARY KEY,
        Name VARCHAR(50),
        Age INT
    );

    CREATE TABLE Passport (
        PassportID SERIAL PRIMARY KEY,
        PersonID INT UNIQUE,
        PassportNumber VARCHAR(20),
        FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
    );
    

2. One-to-Many (or Many-to-One) Relationship

A one-to-many relationship allows a single record in one table (the parent) to be related to multiple records in another table (the child). This is the most common type of relationship in relational databases.

Example:

A Customer can place many Orders, but each Order is placed by only one Customer.

Data Example:

Customer Table

    | CustomerID | Name    | City     |
    |------------|---------|----------|
    | 1          | John    | New York |
    | 2          | Alice   | London   |
    

Order Table

    | OrderID | CustomerID | OrderDate  |
    |---------|------------|------------|
    | 1001    | 1          | 2024-10-10 |
    | 1002    | 1          | 2024-10-15 |
    | 1003    | 2          | 2024-10-12 |
    

SQL Server Syntax:

    CREATE TABLE Customer (
        CustomerID INT PRIMARY KEY,
        Name NVARCHAR(50),
        City NVARCHAR(50)
    );

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

PostgreSQL Syntax:

    CREATE TABLE Customer (
        CustomerID SERIAL PRIMARY KEY,
        Name VARCHAR(50),
        City VARCHAR(50)
    );

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

3. Many-to-Many Relationship

A many-to-many relationship occurs when records in both tables can relate to multiple records in the other table. This type of relationship is usually represented through a junction table that holds foreign keys from both tables.

Example:

A Student can enroll in multiple Courses, and each Course can have multiple Students. A junction table Enrollment links students to courses.

Data Example:

Student Table

    | StudentID | Name    |
    |-----------|---------|
    | 1         | John    |
    | 2         | Alice   |
    

Course Table

    | CourseID | CourseName |
    |----------|------------|
    | 101      | Math       |
    | 102      | Science    |
    

Enrollment Table (Junction Table)

    | StudentID | CourseID |
    |-----------|----------|
    | 1         | 101      |
    | 1         | 102      |
    | 2         | 101      |
    

SQL Server Syntax:

    CREATE TABLE Student (
        StudentID INT PRIMARY KEY,
        Name NVARCHAR(50)
    );

    CREATE TABLE Course (
        CourseID INT PRIMARY KEY,
        CourseName NVARCHAR(50)
    );

    CREATE TABLE Enrollment (
        StudentID INT,
        CourseID INT,
        PRIMARY KEY (StudentID, CourseID),
        FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
        FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
    );
    

PostgreSQL Syntax:

    CREATE TABLE Student (
        StudentID SERIAL PRIMARY KEY,
        Name VARCHAR(50)
    );

    CREATE TABLE Course (
        CourseID SERIAL PRIMARY KEY,
        CourseName VARCHAR(50)
    );

    CREATE TABLE Enrollment (
        StudentID INT,
        CourseID INT,
        PRIMARY KEY (StudentID, CourseID),
        FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
        FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
    );
    

Importance of Relationships

• Data Integrity: Relationships ensure referential integrity between tables, preventing orphaned records and ensuring data consistency.
• Efficient Queries: Relationships enable the use of SQL JOIN operations to retrieve related data from multiple tables in a single query.
• Data Normalization: Relationships support proper database normalization, which reduces data redundancy and enhances efficiency.