1-to-1, 1-to-many, and many-to-many relationships

Overview

Relational databases organize data into tables, and relationships describe how rows in one table are connected to rows in another table. The three most common relationship types are:

• 1-to-1
• 1-to-many
• many-to-many

These relationships are implemented using primary keys, foreign keys, unique constraints, and sometimes junction tables.

Understanding relationships is essential for SQL interviews because most real database queries involve connected data. A developer rarely works with one isolated table. A typical application has users, orders, products, payments, roles, permissions, invoices, comments, categories, tags, and audit records. These tables must be connected correctly so the database can preserve data integrity and queries can retrieve accurate results.

Example:

Customer 1-to-many Orders
Order 1-to-many OrderItems
Product many-to-many Categories
User many-to-many Roles
Employee 1-to-1 EmployeeProfile

Relationships matter because they affect:

• Data integrity.
• Query design.
• Join logic.
• Normalization.
• Indexing.
• Delete behavior.
• Update behavior.
• Performance.
• Reporting.
• Application code.
• Entity Framework Core mapping.
• API response design.
• Database constraints.

A weak relationship design can cause serious problems:

• Duplicate data.
• Orphan rows.
• Inconsistent reports.
• Slow joins.
• Incorrect counts.
• Many-to-many data stored as comma-separated values.
• Accidental duplicate links.
• Deleting a parent row unexpectedly deleting too much data.
• Application code enforcing rules that the database should enforce.
• Queries that return duplicate rows because relationships are misunderstood.

In interviews, relationship questions are common because they test both database fundamentals and practical design judgment. A strong candidate should be able to explain not only the definitions, but also how to implement each relationship in SQL, how to query each relationship using joins, which constraints are needed, and what mistakes to avoid.

A good answer should connect relationship types to real examples:

1-to-1:
One employee has one employee profile.

1-to-many:
One customer can place many orders.

Many-to-many:
One student can enroll in many courses, and one course can have many students.

The most important practical rule is:

Relationships should be enforced by database constraints whenever possible, not only by application code.

Core Concepts

Primary Keys

A primary key uniquely identifies each row in a table. Every row should have a stable identifier that can be referenced by other tables.

Example:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,
    Email NVARCHAR(320) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)
);

In this table, CustomerId uniquely identifies each customer.

Primary key characteristics:

• Must be unique.
• Cannot be NULL.
• There is one primary key constraint per table.
• Can be a single column or multiple columns.
• Is commonly referenced by foreign keys.
• Usually has an index automatically created by the database.

Composite primary key example:

CREATE TABLE CourseEnrollments
(
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrolledAtUtc DATETIME2 NOT NULL,

    CONSTRAINT PK_CourseEnrollments PRIMARY KEY (StudentId, CourseId)
);

Here, the combination of StudentId and CourseId uniquely identifies one enrollment.

Foreign Keys

A foreign key is a column or set of columns in one table that references a primary key or unique key in another table. It creates and enforces a relationship between tables.

Example:

CREATE TABLE Orders
(
    OrderId INT IDENTITY(1,1) NOT NULL,
    CustomerId INT NOT NULL,
    OrderDateUtc DATETIME2 NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),

    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
);

This means every Orders.CustomerId must refer to an existing Customers.CustomerId.

Foreign keys help prevent:

• Orders assigned to non-existing customers.
• Child rows with invalid parent references.
• Orphan data.
• Inconsistent relationships.

Important terminology:

In the customer/order example:

Customers = parent table
Orders = child table
Orders.CustomerId = foreign key
Customers.CustomerId = referenced primary key

Unique Constraints

A unique constraint ensures that values in one column or a combination of columns are unique.

Unique constraints are especially important for 1-to-1 relationships and many-to-many junction tables.

Example:

CREATE TABLE EmployeeProfiles
(
    EmployeeProfileId INT IDENTITY(1,1) NOT NULL,
    EmployeeId INT NOT NULL,
    Biography NVARCHAR(1000) NULL,

    CONSTRAINT PK_EmployeeProfiles PRIMARY KEY (EmployeeProfileId),

    CONSTRAINT UQ_EmployeeProfiles_EmployeeId UNIQUE (EmployeeId),

    CONSTRAINT FK_EmployeeProfiles_Employees
        FOREIGN KEY (EmployeeId)
        REFERENCES Employees(EmployeeId)
);

The foreign key links the profile to an employee. The unique constraint prevents two profiles from referencing the same employee. Together, they enforce 1-to-1.

Without the unique constraint, the design becomes 1-to-many.

Cardinality

Cardinality describes how many rows in one table can be related to rows in another table.

Common relationship cardinalities:

Examples:

1-to-1:
Employee -> EmployeeProfile

1-to-many:
Customer -> Orders

Many-to-many:
Students <-> Courses

Cardinality should come from business rules, not convenience.

Optional vs Required Relationships

A relationship can be required or optional.

Required relationship:

CustomerId INT NOT NULL

This means each order must have a customer.

Optional relationship:

SalesRepresentativeId INT NULL

This means a customer may or may not have a sales representative.

Example:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,
    SalesRepresentativeId INT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId),

    CONSTRAINT FK_Customers_Employees
        FOREIGN KEY (SalesRepresentativeId)
        REFERENCES Employees(EmployeeId)
);

A nullable foreign key represents an optional relationship.

Interview point:

NOT NULL foreign key = required relationship.
NULL foreign key = optional relationship.

1-to-1 Relationships

A 1-to-1 relationship means one row in table A is related to at most one row in table B, and one row in table B is related to at most one row in table A.

Examples:

• One employee has one employee profile.
• One user has one user security setting row.
• One person has one passport record in a simplified country-specific system.
• One customer has one billing preference record.
• One product has one product detail row.

1-to-1 relationships are less common than 1-to-many. They are usually used when:

• Some data is optional and should be stored separately.
• Sensitive data should be isolated.
• Large rarely-used columns should be separated.
• Different parts of the system own different data.
• You want to split a wide table.
• You want separate permissions or auditing.
• You want to model an extension table.

Example:

Employees
- EmployeeId
- FullName
- DepartmentId

EmployeeProfiles
- EmployeeProfileId
- EmployeeId
- Biography
- LinkedInUrl

Each employee can have one profile, and each profile belongs to one employee.

Implementing 1-to-1 with a Unique Foreign Key

One common way to implement 1-to-1 is to place a foreign key in one table and add a unique constraint on that foreign key.

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId)
);

CREATE TABLE EmployeeProfiles
(
    EmployeeProfileId INT IDENTITY(1,1) NOT NULL,
    EmployeeId INT NOT NULL,
    Biography NVARCHAR(1000) NULL,
    LinkedInUrl NVARCHAR(500) NULL,

    CONSTRAINT PK_EmployeeProfiles PRIMARY KEY (EmployeeProfileId),

    CONSTRAINT UQ_EmployeeProfiles_EmployeeId UNIQUE (EmployeeId),

    CONSTRAINT FK_EmployeeProfiles_Employees
        FOREIGN KEY (EmployeeId)
        REFERENCES Employees(EmployeeId)
);

Important parts:

EmployeeProfiles.EmployeeId is a foreign key to Employees.EmployeeId.
EmployeeProfiles.EmployeeId is also unique.

The foreign key ensures the employee exists. The unique constraint ensures one employee cannot have multiple profile rows.

Query:

SELECT
    e.EmployeeId,
    e.FullName,
    p.Biography,
    p.LinkedInUrl
FROM Employees e
LEFT JOIN EmployeeProfiles p
    ON p.EmployeeId = e.EmployeeId;

Use LEFT JOIN because an employee may not have a profile yet.

Implementing 1-to-1 with a Shared Primary Key

Another strong 1-to-1 pattern is a shared primary key. The child table's primary key is also a foreign key to the parent table.

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId)
);

CREATE TABLE EmployeeProfiles
(
    EmployeeId INT NOT NULL,
    Biography NVARCHAR(1000) NULL,
    LinkedInUrl NVARCHAR(500) NULL,

    CONSTRAINT PK_EmployeeProfiles PRIMARY KEY (EmployeeId),

    CONSTRAINT FK_EmployeeProfiles_Employees
        FOREIGN KEY (EmployeeId)
        REFERENCES Employees(EmployeeId)
);

Here, EmployeeProfiles.EmployeeId is both:

• The primary key of EmployeeProfiles.
• The foreign key to Employees.

This naturally enforces one profile per employee.

Benefits:

• Simple constraint model.
• No separate surrogate key needed in the child table.
• Strong 1-to-1 enforcement.
• Good for extension tables.

Trade-offs:

• Child row identity is fully dependent on parent row.
• Less flexible if the child table later needs independent identity.
• Insert order matters: parent must exist first.

When to Use 1-to-1

Use 1-to-1 when the split has a clear purpose.

Good reasons:

• Security separation.
• Optional data.
• Rarely accessed large data.
• Different ownership/lifecycle.
• Reducing row width.
• Isolating sensitive information.
• Extension table pattern.
• Legacy migration.
• Table-per-type style modeling.

Example: security separation

Users
- UserId
- Email
- DisplayName

UserSecrets
- UserId
- PasswordHash
- PasswordSalt
- LastPasswordChangedAtUtc

This can help separate sensitive data and access permissions.

Bad reasons:

• Splitting tables without a clear need.
• Thinking every logical object must have many tables.
• Creating 1-to-1 tables because a table has "too many columns" without performance or ownership evidence.
• Avoiding nullable columns without understanding whether the split adds value.

1-to-many Relationships

A 1-to-many relationship means one row in a parent table can be related to many rows in a child table, but each child row belongs to one parent row.

This is the most common relationship type.

Examples:

• One customer can have many orders.
• One order can have many order items.
• One category can have many products.
• One blog post can have many comments.
• One department can have many employees.
• One invoice can have many invoice lines.

Implementation rule:

Put the foreign key on the many side.

Example:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)
);

CREATE TABLE Orders
(
    OrderId INT IDENTITY(1,1) NOT NULL,
    CustomerId INT NOT NULL,
    OrderDateUtc DATETIME2 NOT NULL,
    Status NVARCHAR(50) NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),

    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
);

One customer can have many rows in Orders.

Query:

SELECT
    c.CustomerId,
    c.FullName,
    o.OrderId,
    o.OrderDateUtc,
    o.Status
FROM Customers c
INNER JOIN Orders o
    ON o.CustomerId = c.CustomerId
WHERE c.CustomerId = 1
ORDER BY o.OrderDateUtc DESC;

Required 1-to-many

A required 1-to-many relationship means each child row must have a parent.

Example:

CustomerId INT NOT NULL

In this design, every order must belong to a customer.

This is common for:

• Orders belonging to customers.
• Order items belonging to orders.
• Comments belonging to posts.
• Invoice lines belonging to invoices.

Optional 1-to-many

An optional 1-to-many relationship means a child row may or may not have a parent.

Example:

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,
    ManagerId INT NULL,

    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId),

    CONSTRAINT FK_Employees_Manager
        FOREIGN KEY (ManagerId)
        REFERENCES Employees(EmployeeId)
);

This is a self-referencing relationship:

One manager can manage many employees.
An employee may have zero or one manager.

Query:

SELECT
    e.EmployeeId,
    e.FullName AS EmployeeName,
    m.FullName AS ManagerName
FROM Employees e
LEFT JOIN Employees m
    ON m.EmployeeId = e.ManagerId;

Use LEFT JOIN because some employees may not have a manager.

many-to-many Relationships

A many-to-many relationship means many rows in table A can relate to many rows in table B.

Examples:

• Students can enroll in many courses; courses can have many students.
• Users can have many roles; roles can belong to many users.
• Products can belong to many categories; categories can contain many products.
• Posts can have many tags; tags can apply to many posts.
• Doctors can have many patients; patients can have many doctors.
• Books can have many authors; authors can write many books.

Relational databases do not model many-to-many directly with only two tables. They use a third table called a junction table, join table, bridge table, association table, or link table.

Example:

Students
Courses
CourseEnrollments

CourseEnrollments connects students and courses.

Implementing many-to-many with a Junction Table

Example:

CREATE TABLE Students
(
    StudentId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE Courses
(
    CourseId INT IDENTITY(1,1) NOT NULL,
    CourseName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Courses PRIMARY KEY (CourseId)
);

CREATE TABLE CourseEnrollments
(
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrolledAtUtc DATETIME2 NOT NULL,
    Grade NVARCHAR(5) NULL,

    CONSTRAINT PK_CourseEnrollments PRIMARY KEY (StudentId, CourseId),

    CONSTRAINT FK_CourseEnrollments_Students
        FOREIGN KEY (StudentId)
        REFERENCES Students(StudentId),

    CONSTRAINT FK_CourseEnrollments_Courses
        FOREIGN KEY (CourseId)
        REFERENCES Courses(CourseId)
);

The junction table has:

• A foreign key to Students.
• A foreign key to Courses.
• A primary key or unique constraint to prevent duplicate relationships.
• Optional attributes about the relationship, such as EnrolledAtUtc or Grade.

Query students and their courses:

SELECT
    s.StudentId,
    s.FullName,
    c.CourseId,
    c.CourseName,
    e.EnrolledAtUtc,
    e.Grade
FROM Students s
INNER JOIN CourseEnrollments e
    ON e.StudentId = s.StudentId
INNER JOIN Courses c
    ON c.CourseId = e.CourseId
ORDER BY s.FullName, c.CourseName;

Query all courses for one student:

SELECT
    c.CourseId,
    c.CourseName,
    e.EnrolledAtUtc,
    e.Grade
FROM CourseEnrollments e
INNER JOIN Courses c
    ON c.CourseId = e.CourseId
WHERE e.StudentId = 1
ORDER BY c.CourseName;

Query all students in one course:

SELECT
    s.StudentId,
    s.FullName,
    e.EnrolledAtUtc,
    e.Grade
FROM CourseEnrollments e
INNER JOIN Students s
    ON s.StudentId = e.StudentId
WHERE e.CourseId = 10
ORDER BY s.FullName;

Junction Table Naming

Common naming styles:

StudentCourses
CourseStudents
CourseEnrollments
UserRoles
ProductCategories
PostTags
BookAuthors

Prefer a domain name when the relationship has meaning or attributes.

Examples:

CourseEnrollments instead of StudentCourses
OrderItems instead of OrderProducts
ProjectAssignments instead of EmployeeProjects
Memberships instead of UserGroups

If the relationship has its own data, it is often a real business entity.

Composite Key vs Surrogate Key in Junction Tables

A junction table can use either a composite primary key or a surrogate primary key.

Composite key:

CREATE TABLE UserRoles
(
    UserId INT NOT NULL,
    RoleId INT NOT NULL,

    CONSTRAINT PK_UserRoles PRIMARY KEY (UserId, RoleId)
);

Surrogate key plus unique constraint:

CREATE TABLE UserRoles
(
    UserRoleId INT IDENTITY(1,1) NOT NULL,
    UserId INT NOT NULL,
    RoleId INT NOT NULL,

    CONSTRAINT PK_UserRoles PRIMARY KEY (UserRoleId),

    CONSTRAINT UQ_UserRoles_UserId_RoleId UNIQUE (UserId, RoleId)
);

Composite key benefits:

• Natural uniqueness.
• No extra identity column.
• Prevents duplicates directly.
• Good for pure link tables.

Composite key trade-offs:

• Foreign keys to the junction table require multiple columns.
• Some ORMs or APIs may prefer a single-column key.
• If the relationship later needs independent identity, migration may be needed.

Surrogate key benefits:

• Simple single-column identity.
• Easier for APIs and ORMs in some cases.
• Useful when the relationship has its own lifecycle.
• Useful when other tables reference the relationship.

Surrogate key trade-offs:

• Must still add unique constraint on (UserId, RoleId) to prevent duplicates.
• Extra column.
• Can hide duplicate relationship bugs if unique constraint is missing.

Best practice:

For pure junction tables, a composite primary key is often sufficient.
For relationship entities with their own lifecycle or references, a surrogate key plus a unique constraint can be useful.

Relationship Attributes

Sometimes the relationship itself has data.

Example many-to-many:

Student <-> Course

Relationship data:

EnrolledAtUtc
Grade
Status
CompletedAtUtc

This data belongs in the junction table because it describes the relationship, not only the student or the course.

CREATE TABLE CourseEnrollments
(
    EnrollmentId INT IDENTITY(1,1) NOT NULL,
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrolledAtUtc DATETIME2 NOT NULL,
    CompletedAtUtc DATETIME2 NULL,
    Grade NVARCHAR(5) NULL,
    Status NVARCHAR(50) NOT NULL,

    CONSTRAINT PK_CourseEnrollments PRIMARY KEY (EnrollmentId),

    CONSTRAINT UQ_CourseEnrollments_StudentId_CourseId
        UNIQUE (StudentId, CourseId),

    CONSTRAINT FK_CourseEnrollments_Students
        FOREIGN KEY (StudentId)
        REFERENCES Students(StudentId),

    CONSTRAINT FK_CourseEnrollments_Courses
        FOREIGN KEY (CourseId)
        REFERENCES Courses(CourseId)
);

This is sometimes called an associative entity.

Inner Join vs Left Join for Relationships

When querying relationships, the join type affects which rows appear.

INNER JOIN returns only matching rows.

Example:

SELECT
    c.CustomerId,
    c.FullName,
    o.OrderId
FROM Customers c
INNER JOIN Orders o
    ON o.CustomerId = c.CustomerId;

This returns only customers who have orders.

LEFT JOIN returns all rows from the left table, even when there is no match.

SELECT
    c.CustomerId,
    c.FullName,
    o.OrderId
FROM Customers c
LEFT JOIN Orders o
    ON o.CustomerId = c.CustomerId;

This returns all customers, including customers with no orders.

Interview point:

Use INNER JOIN when the related row must exist.
Use LEFT JOIN when the parent row should appear even if related data does not exist.

Counting Related Rows

A common relationship query is counting child rows.

Count orders per customer:

SELECT
    c.CustomerId,
    c.FullName,
    COUNT(o.OrderId) AS OrderCount
FROM Customers c
LEFT JOIN Orders o
    ON o.CustomerId = c.CustomerId
GROUP BY
    c.CustomerId,
    c.FullName
ORDER BY OrderCount DESC;

Use LEFT JOIN when you want customers with zero orders included.

Important:

COUNT(o.OrderId)

counts matching child rows.

COUNT(*)

with LEFT JOIN can return 1 for a parent with no child because the parent row still exists in the result.

Filtering with Outer Joins

A common mistake is accidentally turning a LEFT JOIN into an INNER JOIN.

Bad:

SELECT
    c.CustomerId,
    c.FullName,
    o.OrderId
FROM Customers c
LEFT JOIN Orders o
    ON o.CustomerId = c.CustomerId
WHERE o.Status = 'Completed';

The WHERE clause removes rows where o.Status is NULL, so customers with no completed orders disappear.

Better if you want all customers and only completed matching orders:

SELECT
    c.CustomerId,
    c.FullName,
    o.OrderId
FROM Customers c
LEFT JOIN Orders o
    ON o.CustomerId = c.CustomerId
   AND o.Status = 'Completed';

This preserves customers with no completed orders.

Interview point:

Filters on the right table of a LEFT JOIN usually belong in the ON clause if you still want unmatched left-side rows.

Delete Behavior and Referential Actions

Foreign keys can define what happens when a referenced parent row is deleted or updated.

Common delete behaviors:

Example cascade delete:

CREATE TABLE OrderItems
(
    OrderItemId INT IDENTITY(1,1) NOT NULL,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity INT NOT NULL,

    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderItemId),

    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderId)
        REFERENCES Orders(OrderId)
        ON DELETE CASCADE
);

If an order is deleted, its order items are deleted too.

Use cascade delete carefully.

Good cascade examples:

• Order -> OrderItems.
• BlogPost -> Comments in some systems.
• Parent temporary record -> child temporary record.

Dangerous cascade examples:

• Customer -> Orders in systems where order history must be preserved.
• User -> AuditLogs if audit logs must remain.
• Product -> OrderItems if historical order lines must remain.

Best practice:

Choose delete behavior based on business rules and data retention requirements.

Indexing Foreign Keys

Foreign keys are often used in joins and filters, so indexing foreign key columns is usually important for performance.

Example:

CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);

For a junction table:

CREATE INDEX IX_CourseEnrollments_CourseId
ON CourseEnrollments(CourseId);

If primary key is (StudentId, CourseId), this index helps queries that start from CourseId.

Why indexes matter:

• Faster joins.
• Faster lookups of child rows.
• Faster cascade checks.
• Better query plans.
• Reduced table scans.

Common mistake:

Assuming the foreign key automatically creates an index.

In SQL Server, creating a foreign key does not automatically create an index on the child column. You often need to create the index yourself.

Normalization and Relationships

Normalization is the process of organizing relational data to reduce duplication and improve integrity.

Relationships are central to normalization.

Example bad design:

CREATE TABLE Customers
(
    CustomerId INT PRIMARY KEY,
    FullName NVARCHAR(200),
    Order1Id INT NULL,
    Order2Id INT NULL,
    Order3Id INT NULL
);

Problems:

• Fixed number of orders.
• Many nullable columns.
• Hard to query.
• Hard to add more orders.
• Violates relational design principles.

Better 1-to-many design:

CREATE TABLE Customers
(
    CustomerId INT PRIMARY KEY,
    FullName NVARCHAR(200) NOT NULL
);

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT NOT NULL,
    OrderDateUtc DATETIME2 NOT NULL,

    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
);

Another bad design for many-to-many:

CREATE TABLE Students
(
    StudentId INT PRIMARY KEY,
    FullName NVARCHAR(200),
    CourseIds NVARCHAR(500)
);

Example value:

"1,2,5,9"

Problems:

• Hard to enforce foreign keys.
• Hard to query.
• Hard to index correctly.
• Hard to prevent duplicates.
• Hard to update.
• Breaks normalization.

Better:

CREATE TABLE CourseEnrollments
(
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,

    CONSTRAINT PK_CourseEnrollments PRIMARY KEY (StudentId, CourseId)
);

Denormalization and Relationships

Sometimes systems intentionally denormalize data for performance or reporting, but this should be done carefully.

Example:

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT NOT NULL,
    CustomerNameSnapshot NVARCHAR(200) NOT NULL,
    OrderDateUtc DATETIME2 NOT NULL
);

CustomerNameSnapshot duplicates data, but it may be intentional because the order should preserve the customer name as it appeared at purchase time.

Good denormalization reasons:

• Historical snapshots.
• Reporting performance.
• Read-heavy query optimization.
• Avoiding expensive joins in read models.
• Event-sourced projections.
• Search indexes.

Bad denormalization reasons:

• Avoiding joins because they are misunderstood.
• Storing comma-separated IDs.
• Duplicating business data with no synchronization plan.
• Premature optimization.

Best practice:

Normalize first for correctness. Denormalize intentionally for specific performance or historical requirements.

Relationship Modeling Examples

E-commerce

Customer 1-to-many Orders
Order 1-to-many OrderItems
Product 1-to-many OrderItems
Product many-to-many Categories
User many-to-many Roles

Tables:

Customers
Orders
OrderItems
Products
Categories
ProductCategories
Users
Roles
UserRoles

Blog System

Author 1-to-many Posts
Post 1-to-many Comments
Post many-to-many Tags
User 1-to-1 UserProfile

Tables:

Users
UserProfiles
Posts
Comments
Tags
PostTags

Course Management

Instructor 1-to-many Courses
Student many-to-many Courses through Enrollments
Course 1-to-many Assignments
Assignment 1-to-many Submissions

Tables:

Instructors
Courses
Students
CourseEnrollments
Assignments
Submissions

Modeling Direction

Relationships are often described in one direction, but SQL queries can navigate either direction using joins.

Example:

One customer has many orders.

This also means:

Each order belongs to one customer.

The foreign key is stored on the child table:

Orders.CustomerId

To get orders for a customer:

SELECT *
FROM Orders
WHERE CustomerId = 1;

To get customer for an order:

SELECT
    o.OrderId,
    c.FullName
FROM Orders o
INNER JOIN Customers c
    ON c.CustomerId = o.CustomerId
WHERE o.OrderId = 100;

The physical foreign key placement matters.

Self-Referencing Relationships

A self-referencing relationship is a relationship where a table references itself.

Example: employee-manager hierarchy

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,
    ManagerId INT NULL,

    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId),

    CONSTRAINT FK_Employees_Manager
        FOREIGN KEY (ManagerId)
        REFERENCES Employees(EmployeeId)
);

This represents:

One manager can have many employees.
One employee can have zero or one manager.

Query:

SELECT
    e.FullName AS EmployeeName,
    m.FullName AS ManagerName
FROM Employees e
LEFT JOIN Employees m
    ON m.EmployeeId = e.ManagerId;

Self-referencing many-to-many example:

Users can follow many users.
Users can be followed by many users.

CREATE TABLE UserFollows
(
    FollowerUserId INT NOT NULL,
    FollowedUserId INT NOT NULL,
    CreatedAtUtc DATETIME2 NOT NULL,

    CONSTRAINT PK_UserFollows PRIMARY KEY (FollowerUserId, FollowedUserId),

    CONSTRAINT FK_UserFollows_Follower
        FOREIGN KEY (FollowerUserId)
        REFERENCES Users(UserId),

    CONSTRAINT FK_UserFollows_Followed
        FOREIGN KEY (FollowedUserId)
        REFERENCES Users(UserId),

    CONSTRAINT CK_UserFollows_NoSelfFollow
        CHECK (FollowerUserId <> FollowedUserId)
);

Composite Foreign Keys

A foreign key can reference multiple columns.

Example:

CREATE TABLE Tenants
(
    TenantId INT NOT NULL,
    RegionCode NVARCHAR(10) NOT NULL,

    CONSTRAINT PK_Tenants PRIMARY KEY (TenantId, RegionCode)
);

CREATE TABLE TenantUsers
(
    TenantUserId INT IDENTITY(1,1) NOT NULL,
    TenantId INT NOT NULL,
    RegionCode NVARCHAR(10) NOT NULL,
    Email NVARCHAR(320) NOT NULL,

    CONSTRAINT PK_TenantUsers PRIMARY KEY (TenantUserId),

    CONSTRAINT FK_TenantUsers_Tenants
        FOREIGN KEY (TenantId, RegionCode)
        REFERENCES Tenants(TenantId, RegionCode)
);

Composite keys can be useful, but they make joins and foreign keys more verbose. Many systems use surrogate keys for simplicity and unique constraints for business rules.

Business Keys vs Surrogate Keys

A surrogate key is an artificial identifier, such as CustomerId INT IDENTITY or uniqueidentifier.

A business key is a real-world unique value, such as Email, NationalId, Sku, or PolicyNumber.

Example:

CREATE TABLE Products
(
    ProductId INT IDENTITY(1,1) NOT NULL,
    Sku NVARCHAR(50) NOT NULL,
    ProductName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId),
    CONSTRAINT UQ_Products_Sku UNIQUE (Sku)
);

Relationships often use surrogate keys because they are stable and compact. Business keys should still be protected with unique constraints when required.

Best practice:

Use a surrogate primary key for technical relationships when useful.
Use unique constraints to enforce natural business uniqueness.

Relationship Constraints vs Application Logic

Application code can validate relationships, but the database should enforce critical integrity rules.

Bad:

Application checks whether CustomerId exists before inserting Order.
Database has no foreign key.

This is fragile because:

• Another application can insert invalid data.
• A background job can bypass the check.
• Race conditions can occur.
• Manual scripts can corrupt data.
• Reports can become unreliable.

Better:

CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerId)
    REFERENCES Customers(CustomerId)

Application validation is still useful for user-friendly errors, but database constraints are the final protection.

Querying Across Multiple Relationships

Real queries often cross multiple relationships.

Example: order details with customer and products

SELECT
    o.OrderId,
    o.OrderDateUtc,
    c.FullName AS CustomerName,
    p.ProductName,
    oi.Quantity,
    oi.UnitPrice,
    oi.Quantity * oi.UnitPrice AS LineTotal
FROM Orders o
INNER JOIN Customers c
    ON c.CustomerId = o.CustomerId
INNER JOIN OrderItems oi
    ON oi.OrderId = o.OrderId
INNER JOIN Products p
    ON p.ProductId = oi.ProductId
WHERE o.OrderId = 100;

Relationships:

Customers 1-to-many Orders
Orders 1-to-many OrderItems
Products 1-to-many OrderItems

The query joins from parent to child and child to related parent.

Avoiding Duplicate Rows in Relationship Queries

Joining 1-to-many or many-to-many relationships naturally produces multiple rows.

Example:

SELECT
    c.CustomerId,
    c.FullName,
    o.OrderId
FROM Customers c
INNER JOIN Orders o
    ON o.CustomerId = c.CustomerId;

If one customer has 5 orders, that customer appears 5 times.

This is not necessarily wrong. It reflects the result grain.

Important concept:

The result grain is the level represented by one row in the result.

In the query above, the result grain is:

One row per customer-order pair.

If you want one row per customer, aggregate:

SELECT
    c.CustomerId,
    c.FullName,
    COUNT(o.OrderId) AS OrderCount
FROM Customers c
LEFT JOIN Orders o
    ON o.CustomerId = c.CustomerId
GROUP BY c.CustomerId, c.FullName;

Common mistake:

Using DISTINCT to hide duplicate rows without understanding why the join produced them.

DISTINCT can hide modeling or query mistakes and may add unnecessary cost.

Handling Duplicate Links in Many-to-Many

A many-to-many junction table should prevent duplicate links.

Bad:

CREATE TABLE UserRoles
(
    UserRoleId INT IDENTITY(1,1) PRIMARY KEY,
    UserId INT NOT NULL,
    RoleId INT NOT NULL
);

This allows duplicate rows:

UserId = 1, RoleId = 2
UserId = 1, RoleId = 2

Better:

CREATE TABLE UserRoles
(
    UserId INT NOT NULL,
    RoleId INT NOT NULL,

    CONSTRAINT PK_UserRoles PRIMARY KEY (UserId, RoleId),

    CONSTRAINT FK_UserRoles_Users
        FOREIGN KEY (UserId)
        REFERENCES Users(UserId),

    CONSTRAINT FK_UserRoles_Roles
        FOREIGN KEY (RoleId)
        REFERENCES Roles(RoleId)
);

Or:

CREATE TABLE UserRoles
(
    UserRoleId INT IDENTITY(1,1) NOT NULL,
    UserId INT NOT NULL,
    RoleId INT NOT NULL,

    CONSTRAINT PK_UserRoles PRIMARY KEY (UserRoleId),

    CONSTRAINT UQ_UserRoles_UserId_RoleId UNIQUE (UserId, RoleId)
);

Always enforce uniqueness on the pair.

Relationship Design and EF Core

Even though this topic is SQL, .NET developers often map these relationships in EF Core.

Common mapping concepts:

1-to-many:
Customer has many Orders.
Order has one Customer.

1-to-1:
User has one UserProfile.
UserProfile has one User.

Many-to-many:
Student has many Courses.
Course has many Students.

EF Core can create many-to-many relationships with skip navigations, but the relational database still uses a junction table.

Interview point:

ORM navigation properties do not replace relational constraints. The database still needs keys, foreign keys, and unique constraints.

Common Mistakes

Common mistakes include:

• Not defining foreign keys.
• Enforcing relationships only in application code.
• Putting the foreign key on the wrong side of a 1-to-many relationship.
• Forgetting the unique constraint in a 1-to-1 relationship.
• Modeling many-to-many with comma-separated IDs.
• Modeling many-to-many with repeated columns such as Course1Id, Course2Id, Course3Id.
• Forgetting to prevent duplicate rows in a junction table.
• Not indexing foreign keys.
• Using INNER JOIN when LEFT JOIN is required.
• Filtering the right table of a LEFT JOIN in the WHERE clause incorrectly.
• Using DISTINCT to hide duplicate rows from misunderstood joins.
• Choosing cascade delete without understanding data retention rules.
• Sharing one table for unrelated concepts.
• Over-normalizing without a practical reason.
• Denormalizing without a synchronization strategy.
• Using business keys as primary keys when they can change.
• Not naming constraints clearly.
• Not understanding optional vs required relationships.
• Not considering delete behavior.
• Ignoring relationship attributes in many-to-many designs.

Best Practices

Use primary keys to uniquely identify rows.

Use foreign keys to enforce relationships.

Use NOT NULL foreign keys for required relationships.

Use nullable foreign keys for optional relationships.

Put the foreign key on the many side of a 1-to-many relationship.

Use a unique foreign key or shared primary key for 1-to-1 relationships.

Use a junction table for many-to-many relationships.

Add a primary key or unique constraint on junction table key pairs.

Store relationship attributes in the junction table.

Index foreign key columns used in joins and filters.

Use clear table and constraint names.

Choose cascade delete only when it matches business rules.

Use LEFT JOIN when parent rows without children should be returned.

Understand the result grain before using DISTINCT.

Normalize data for correctness first.

Denormalize only when there is a clear performance, historical, or reporting reason.

Let the database enforce critical integrity rules.

Keep business rules and database constraints aligned.