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!

  1. Designing Databases - an Introduction
  2. Designing our Example Database
  3. 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 23 April 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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!

Two separate tables

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 3 tables

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 Jobs table

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:

Composite primary key

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.


  1. Designing Databases - an Introduction
  2. Designing our Example Database
  3. Modelling Many-to-many Relationships (this blog)
This blog has 0 threads Add post