• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Learn basic types of associations

Log in or subscribe for free to enjoy all this course has to offer!

Let's return to the condominium's database with its  Apartment and  Dog  tables.

One-to-many & Complete / Incomplete

Do you remember what this diagram means?

Between 0-4 dogs can live in an apartment and evey dog must be associated with an apartment.
Between 0-4 dogs can live in an apartment and every dog must be associated with an apartment.

Because many dogs can be associated with a single apartment, this is called a one-to-many relationship (a one apartment to many dog relationship).

And because every dog must have an apartment associated with it (note the 1..1, which means a minimum of 1 and a max of 1) this is called a complete relationship

On the other side, objects of the  Apartment  class don't need to have an object of the  Dog class associated with them (note the zero part of 0..4 ), this is an incomplete relationship.

Examples of one to many relationships in the world:
  • a class of students

  • flights at an airport

  • items in a shopping cart

  • platforms in a train station

One-to-one & complete/incomplete

‌ Do you know what the rules for multiplicity in this diagram mean?

An apartment can house 1 dog one or no dogs.  And every dog must be associated with an apartment - so no strays.
An apartment can house 1 dog or no dogs. And every dog must be associated with an apartment, so no strays.

This is an example of a one-to-one relationship (1 dog per apartment and at most 1 apartment per dog). And just like the previous example, there are not allowed to be any stray dogs, so this is a complete one-to-one relationship (in the direction of dogs to apartments). And because apartments don't have to have dogs associated with them, the relationship of the  Apartment  class to the  Dog  class it's an incomplete one-to-one relationship.

An example of a one-to-one relationship in the world is a person to their passport. This is an incomplete relationship because not everyone has a passport. 

Another example of a one-to-one relationship would be a country to its flag.

Many-to-Many relationship

Do you know what the rules for multiplicity in this diagram mean?

An apartment can house up to 8 dogs but doesn't need to house a dog.  A dog can be housed in a maximum of  3 apartments and a minimum of 1 apartment.
An apartment can house up to 8 dogs but doesn't need to house a dog. A dog can be housed in a maximum of 3 apartments and a minimum of 1 apartment.

This is an example of a many-to-many relationship that is complete on one side and incomplete on the other side. In other words, an apartment can house many dogs and a dog can live in many apartments.

Other examples of many to many in the world are:

  • students to classes: there are many students in a class and a student can take many classes.

  • passengers to flights: there are many passenger on a flight and passengers can take many flights.

  • customers to products: a customer can buy many products and a product can be bought by many customers.

  • employees to projects: assuming employees can work on more than one project.

The Foreign Key & one-to-many relationship

The Foreign Key is the link between the classes

Here's the one-to-many association that we started with above:

A One to Many relationship
A one-to-many relationship

We need to update the diagram to show each class's Primary Key so we can get a specific line from a single table.

Because the apartment numbers are sure to be unique, we can use them as the Primary Key for the  Apartment  class. The PK of the  Dog  class,  dogID , will be an auto incremented number.

Adding the primary unique identifier in both classes
Adding the primary unique identifier in both classes

Now, we need to link specific rows together.

From the rules of multiplicity we can see that a single row of the  Apartment  table will be linked to a maximum of 4 rows of the  Dog  table.  We connect objects of the  Dog  class to objects of the  Apartment  class by adding a column to the  Dog  table that contains the PK of the  Apartment  table. This column in the  Dog  table is called the Foreign Key (FK).

The class diagrams will now look like this:

Adding the Foreign key to the Dog class
Adding the Foreign key to the Dog class

Here are a few rows of data from the  Apartment  table :

aptNumber

ownerFirstName

ownerLastName

phone

17F

Fred

Red

2233445566

17G

Reddy

Freddy

3344556677

And here are a few rows of data from the  Dog  table:

dogID

aptNumber

name

breed

1

17F

Mister Peppers

Terrier

2

17F

Enamel

Mutt

3

17F

Slobby

Sheepdog

4

17G

Curlers

Poodle

We can ask the database to give us an owner's first name ( ownerFirstName ) and the names of the dogs ( name ) who live in  aptNumber  17F.

And it will return to us a table that looks like this:

ownerFirstName

name

Fred

Mister Peppers

Fred

Enamel

Fred

Slobby

One-to-one & the Foreign Key

Let's update the diagram to show a one-to-one relationship and let's also include the Foreign Key so that we specify how we are linking the tables.

A complete One to One relationship linked with a foreign key
A complete one-to-one relationship linked with a foreign key

Here are a few rows of each table:

 Apartment  table

aptNumber

ownerFirstName

ownerLastName

phone

17F

Fred

Red

2223334444

17G

Reddy

Freddy

3334445555

 Dog  table

dogID

aptNumber

name

breed

1

17F

Dingus

Dalmatian

2

17G

Moose

Chiwawa

To get all the information about an apartment and its dog, you would ask the database in the same way that you would have asked if this was a one-to-many relationship.

In this case, you could ask the database for all the columns of the  Apartment  table where the  aptNumber  is 17F and all the columns  of the  Dog  table where the  aptNumber  is 17F.

Because there is only 1 dog per apartment, we could combine the tables like this:

aptNumber

ownerFirstName

ownerLastName

phone

dogName

breed

17F

Fred

Red

2223334444

Dingus

Dalmatian

17G

Reddy

Freddy

3334445555

Moose

Chiwawa

Then you wouldn't need to bother creating the other table. For this reason, one-to-one tables are rare.

It does make sense to separate the information when:

  • the entities have very different relationships to other classes

  • the entities are coming from different sources

  • the combined row's length becomes unwieldy

Organizing many-to-many

Given a many-to-many relationship, putting in a Foreign Key won't work because FK can only be filled with a single value.

When an apartment can house many dogs and a dog can live in many apartments neither side "owns" the other.

For example, Linguini, the Miniature Schnauzer lives in apt 17G, 18A and 12B and Geraldine Feraldine who lives in apt 18A looks after Linguini and 4 other dogs, and those other dogs live in quite a few other apartments.

Keeping track of this is scary!

We need to tame this complexity!
We need to tame this complexity!

To the rescue comes association classes!

An association class is a class that comes off the association line. Its purpose is to organize the many connections between the two classes.

In the domain diagram, the  Association  class looks like this:

An association class records every instance of the association.
An association class records every instance of the association.

Here are a few rows of the  HousesInfo  Table:

aptNumber

dogID

bedType

3c

2

mat

5d

2

kennel

16f

7

cushion

3c

1

couch

Now, if we have an  aptNumber, we can get a list of which dogs stay there and what kind of bed they sleep on. Similarly, with a  dogID, we can get the list of which apartments it can stay in.

An association class often only contains the primary keys of the classes that it is associated with. In the  HousesInfo class, we are also recording what type of bed the dog sleeps on.

Other examples of what's in an association class

For an airline, association class that connects  Airplane  and  Passenger  classes could contain the following attributes:

  • flightNumber

  • passangerID

  • date

  • seat

In the context of a school, the  Student class and the  Class  class could be connected with an association class with the following attributes:

  • classID

  • studentID

  • semester

  • grade

Association tables are amazing at making a tangle of information clear!

Example of certificate of achievement
Example of certificate of achievement