BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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)
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!
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.