0.6.11 Connecting Related Records With IDs

Structured Data Often Contains Related Records

A music library contains several kinds of information.

For example:

  • songs;
  • artists;
  • albums.

You could place every detail about the artist and album directly into every song row.

That creates repetition.

Another approach is to keep separate records and connect them using IDs.

Start With an Artist Table

Suppose the Artists table contains:

Artist ID Artist Name
A001 Dana Lee
A002 Example Band

Each artist record has a unique identifier.

Now consider a Songs table:

Song ID Title Artist ID
S001 Side Street A001
S002 Northern Lights A002

The Artist ID in each song record connects the song to one artist record.

The ID Acts as a Reference

For:

Activity diagram showing Songs.Artist ID, then Artists.Artist ID.
SongsArtistID.
  • New
  • Changed
  • Removed

and:

Activity diagram showing Songs.Album ID, then Albums.Album ID.
SongsAlbumID.
  • New
  • Changed
  • Removed

The matching values create the connection.

This Is the Intuition Behind Data Relationships

At this stage, focus on the practical idea:

  • give records stable unique IDs;
  • place related records in appropriate tables;
  • store the needed ID where one record must refer to another;
  • make sure the referenced ID exists.

The next Learning Activity introduces the broader vocabulary of entities, attributes, and relationships.