Skip to content

Room

Relationships

Entities can be related to other entities in the same or other tables. These relationships ca be

  • One-to-one: One instance of an entity relates to exactly one instance other entity
  • One-to-many: One instance of entity relates to multiple instances of another entity
  • Many-to-Many: Multiple instances of an entity can be related to multiple instances of another entity

These relationships are easiest to understand through examples

One to One

In our database, each Person can have exactly one Birth Certificate. Each Birth Certificate is owned by exactly one person.

erDiagram
    PERSON ||--|| BIRTH-CERTIFICATE : born

This relationship is often used for extra/optional data. While we could add the birth certificate details directly to the person, putting it in a separate entity allows that data to be optional, or easily moved to a different entity.

Using one-to-one relationships, we can gradually attach more data to an entity as our system grows with new function.

There are several ways to represent this, but we typically define an attribute in one of the entities that holds the id of the other entity. This attribute is called a Foreign Key, as it's a key in another entity. Usually you'll put this attribute in the entity that feels like it's "owned" by the other entity (if the relationship feels like that). In this example, the Person owns the Birth Certificate, so we put a Foreign Key called personId in the Birth Certificate.

One to Many

Often we'll need to have more than one entity attached/owned by an entity. In our example, a Person has organs, and those organs are only associated with that specific person. 

erDiagram
    PERSON ||--|{ ORGAN : has-organ

By making Organ a separate entity, we can attach any number of Organs to a Person by defining a personId foreign key in the Organ entity. We can even donate organs to another Person entity by just changing the value of that foreign key. The donate organ can only belong to one Person at a time.

Many to Many

The real fun begins when entities can be shared. Think about an address as representing a home, office, hotel room, and so on.

Each Person entity could be associated with multiple Address entities. Each Address entity could be associated with multiple Person entities.

erDiagram
    PERSON }o--o{ ADDRESS : has-address

This relationship is trickier to represent - we cannot simply add a foreign key to either entity; we need to create a table to represent the linkage.

We can create an Person-Address table to relate each person/address pair

Attribute Type Key Comment
personId String FK The ID of the related person
addressId String FK The ID of the related address

In this case, the combination of the personId and addressId create a unique key for a row in the Person-Address table.

Associative Entities

Sometimes, a many-to-many relationship requires additional information describing the relationship itself. When we defined that Person-Address table above to represent the many-to-many relationship, we can add more columns to it for that extra information.

Because we now have data we care about attached to the relationship, we now think of that Person-Address as an entity, and can link the three entities:

erDiagram
    PERSON ||--o{ PERSON-ADDRESS : has-address
    PERSON-ADDRESS }o--|| ADDRESS: has-address

The many-to-many relationship now looks like two one-to-many relationships:

  • One Person can have zero or more Person-Addresses
  • One Address can have zero or more Person-Addresses
  • One Person-Address can have exactly one Person and one Address

The Person-Address Entity now looks like

Attribute Type Key Comment
personId String FK The ID of the related person
addressId String FK The ID of the related address
type String The type of address (home, work...)

Full Example

We can put them all together in a single diagram

erDiagram
    PERSON ||--|| BIRTH-CERTIFICATE : born
    PERSON ||--|{ ORGAN : has-organ
    PERSON }o--|| PERSON-ADDRESS : has-address
    PERSON-ADDRESS ||--|{ ADDRESS: has-address