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:

Each artist record has a unique identifier.

Now consider a Songs table:

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

The ID Acts as a Reference

For:

Song ID = S002
Title = Northern Lights
Artist ID = A002

the value:

A002

points to the artist record:

Artist ID = A002
Artist Name = Example Band

That lets the dataset express:

Northern Lights is related to Example Band.

The relationship is represented through a shared identifier.

This Reduces Repeated Descriptive Data

Without IDs, the song table might repeat:

Example Band

on many rows.

If the artist's name needs correction, many rows may need editing.

With a separate Artists table, the descriptive artist information can live in one artist record.

Songs can refer to that record by ID.

This can reduce duplication and improve consistency.

The Referenced ID Must Exist

Suppose a song contains:

Artist ID = A099

but the Artists table has no record with A099.

The reference does not connect to a known artist record.

The structure may still look valid, but the relationship is broken.

Related-record IDs are useful only when they refer to actual intended records.

IDs Must Match Exactly

If the artist identifier is:

A002

then:

A02

is a different text value.

So is:

a002

in systems where case matters.

A relationship depends on matching the identifier according to the rules of the dataset.

Consistency matters.

One Artist Can Be Referenced by Many Songs

Suppose Example Band has three songs:

All three song records refer to the same Artist ID.

The artist record itself does not need to be duplicated.

This is a common data relationship:

one artist → many songs

You will model relationships more formally when you work with entity-relationship diagrams.

A Song Can Also Refer to an Album

The Songs table might contain:

Now the song refers to:

• an artist record;
• an album record.

The record becomes connected to multiple kinds of data.

Related IDs Are Not the Same as Copying a Record

If the song contains:

Artist ID = A002

it does not contain the entire artist record.

It contains a value used to identify the related record.

The descriptive artist information remains in the Artists table.

That distinction is important.

A reference tells you which record to connect to.

It does not duplicate all information from that record.

Relationships Make Separated Data Useful

Separating songs, artists, and albums would be inconvenient if the records could never be reconnected.

Identifiers make the separation useful.

Conceptually:

Songs.Artist ID
        ↓
Artists.Artist ID

and:

Songs.Album ID
        ↓
Albums.Album ID

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.