This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Designing databases for SQL Server and Access Part three of a three-part series of blogs
Designing relational databases is easy once you learn the few basic rules, and the procedure is identical whether you're using SQL Server or Access. This blog illuminates the subject! Designing Databases - an Introduction Designing our Example Database Modelling Many-to-many Relationships (this blog) This blog is part of our tutorial on writing SQL. You can also learn SQL on one our introduction or advanced SQL courses.

Designing databases for SQL Server and Access

Part three of a three-part series of blogs

Designing relational databases is easy once you learn the few basic rules, and the procedure is identical whether you're using SQL Server or Access. This blog illuminates the subject!

Designing Databases - an Introduction
Designing our Example Database
Modelling Many-to-many Relationships (this blog)

This blog is part of our tutorial on writing SQL. You can also learn SQL on one our introduction or advanced SQL courses.

Posted by Andy Brown on 07 July 2023

Modelling Many-to-many Relationships

In real life, people move jobs: so Bob may work for Shell one year, but IBM the next. This inconsiderate behaviour on the part of human beings complicates our database!

In the real world, you have two tables: the people who could potentially work for companies, and the companies for whom the people could work.

The link between the two tables shown above is therefore many-to-many:

Each company can have many people working for it; but
Each person can over the course of their lifetime work for many companies.

Given that every relationship in a relational database is one-to-many, how can we get round this?

The Answer: Creating a Child Table

The solution to this problem is to create a table which contains the children of both the companies and the persons table. This will contain the following fields/columns:

Field/column	What it contains
CompanyId	The company for whom this person worked for this job
PersonId	The person who worked for this job
DateJoined	The date the person joined the company
DateLeft	The date the person left the company (may be blank)
JobId	The unique number of each record (see below)

Thus the final database diagram would look like this:

The final design contains 3 linked tables

Choosing a Primary Key for Child Tables

You can create two different primary keys for tables like the jobs one above. One possibility is to create a brand new field/column to number each record:

The JobId field/column numbers each record

In the example above, the selected row/record shows that person number 1 (Alan Arbib) started work for company number 3 (Microsoft) on 1st May 2010, having previously worked at Shell (company 1) and IBM (company 2).

The other possibility is to create a primary key based on the fact that the combination of CompanyId and PersonId must be unique:

Here the composite primary key contains the company and person numbers.

If you do go down this route, you must be certain that you can't repeat the same PersonId/CompanyId combination in the table. Thought: what happens if Alan Arbib decides at some point in the future to rejoin Shell or IBM?

And that's it! A typical relational database will contain tens - perhaps even hundreds - of tables joined together with relationships, but that's essentially all of the building blocks taken care of.

Parts of this blog
Designing Databases - an Introduction Designing our Example Database Modelling Many-to-many Relationships (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Modelling Many-to-many Relationships

The Answer: Creating a Child Table

Choosing a Primary Key for Child Tables

Head office

London

Manchester