Relationship Types
There are 3 different types of relationships objects can have with one another. These are:
- One to One
- One to Many
- Many to Many
To describe these relationships, we will use the following Database tables as an example to work with:
tools
| id | name | description | workplace_id |
|---|---|---|---|
| 7 | Small Red Hammer | Red hammer to hit things with, preferably nails. | 42 |
| 8 | Crowbar | Heavy crowbar, for busting open crates or zombie heads. | 43 |
| 9 | Axe | Nice shiny axe, for chopping down trees or inflated egos. | 42 |
| 10 | Drill | Large drill for breaking up large stones or doing delicate dentistry work (on giants). | 43 |
| 11 | Box of Nails | Yep, one box of nails is all we've got. | 42 |
| 12 | Big Hammer | Blacksmith's hammer for shaping metal into who knows what. | 44 |
workplaces
| id | location |
|---|---|
| 42 | Timber Mill |
| 43 | Old Stone Quarry |
| 44 | Blacksmith Hut |
workers
| id | firstname | lastname | workplace_id |
|---|---|---|---|
| 1 | Fred | Smith | 44 |
| 2 | Jayne | Doe | 42 |
| 3 | Joe | Public | 43 |
skills_workers
| id | skill_id | worker_id |
|---|---|---|
| 1 | 22 | 1 |
| 2 | 27 | 1 |
| 3 | 23 | 2 |
| 4 | 25 | 2 |
| 5 | 25 | 3 |
| 6 | 26 | 3 |
| 7 | 27 | 3 |
skills
| id | name |
|---|---|
| 22 | Blacksmith |
| 23 | Carpentry |
| 24 | Cabinet Maker |
| 25 | Builder |
| 26 | Stonemason |
| 27 | Zombie Slayer |
If you've read Database Tables, you will recognise that tools, workplaces, workers and skills are all normal tables. These are the tables that you would create a DataMapper model for, in order to tranform them into objects.
The other tables, being tools_workplaces, workers_workplaces and skills_workers, are the joining tables which hold the relationship records between the normal tables.
One to One
One to One relationships occur when there is exactly one record in the first table that corresponds to exactly one record in the related table.
For example, a worker can be assigned to only one workplace and a workplace can have only one worker. From the tables above we can see that:
Fred Smith works at the Blacksmith Hut.
Jayne Doe works at the Timber Mill.
Joe Public works at the Old Stone Quarry.
Only Fred is allowed to work at the Blacksmith Hut. Similarly for the others, they can work only at their own workplace and no one else can work there. Sure, it's not very efficient to have one worker per workplace but it suits for this example.
Because this is a One to One relationship, the relationship could have been stored in three ways:
- As shown, on the workers table.
- On the workplaces table, as worker_id
- On a dedicated workers_workplaces join table, with the columns id, worker_id, and workplace_id
One to Many
One to Many relationships occur when each record in the first table has many linked records in the related table but each record in the related table has only one corresponding record in the first table.
For example, a workplace can be equipped with many tools but a tool can be in only one workplace. From the tables above we can see that:
The Blacksmith Hut has the Big Hammer.
The Timber Mill has the Small Red Hammer, Axe and Box of Nails.
The Old Stone Quarry has the Crowbar and Drill.
This relationship could also have been stored on its own table, as tools_workplaces, with the columns id, tool_id, and workplace_id
Many to Many
Many to Many relationships occur when each record in the first table has many linked records in the related table and vice-versa. These are always stored using dedicated join tables.
For example, a worker can have many skills and a skill can have many workers listed with it. From the tables above we can see that:
Fred Smith has the Blacksmith and Zombie Slayer skills.
Jayne Doe has the Carpentry and Builder skills.
Joe Public has the Builder, Stonemason and Zombie Slayer skills.
So we can see that each of the workers have multiple skills, and in the case of the Builder and Zombie Slayer skills, we see there are multiple workers who have them.
The Builder skill is listed against Jayne Doe and Joe Public.
The Zombie Slayer skill is listed against Fred Smith and Joe Public.
Self Referencing Relationships and Multiple Relationships to the Same Model
These more advanced relationships are discussed after the next section, in Advanced Relationships.
Now that you have a better understanding of the relationship types, you can continue on to read Setting up Relationships.