0.6.10 Giving Each Record a Unique ID

A Name Is Not Always Enough to Identify a Record

Suppose your music library contains two songs named:

Home

The titles match.

The songs may be completely different.

If another table needs to refer to one particular song, the title alone may be ambiguous.

A unique identifier, or ID, gives each record a value that distinguishes it from every other record in the same collection.

For example:

S001
S002
S003
Each Record Gets Its Own ID

A song table might look like:

Song ID Title Artist
S001 Home Dana Lee
S002 Home Example Band
S003 Northern Lights Example Band

The two Home titles are not a problem because the records have different IDs.

The ID answers:

A Unique ID Should Not Be Reused

If:

S001

identifies one song record, another song should not also use S001.

The ID's usefulness depends on uniqueness.

If the same identifier appears on two different records, another table cannot safely use it to distinguish between them.

IDs Should Be Stable

Suppose:

S001

identifies Northern Lights.

If the song's title is later corrected or the genre changes, the record is still the same song record.

The ID should normally remain:

S001

A stable identifier lets other data continue referring to the record even when descriptive fields change.

Descriptive Values Can Change

Consider an artist whose display name changes.

If other records refer to the artist using only the current name, every related row may need to be updated.

A stable Artist ID can preserve identity independently from the current display text.

For example:

Artist ID = A004
Name = Example Band

If the displayed name is later corrected, A004 can still identify the same artist record.

IDs Do Not Need to Describe Everything

An ID such as:

S001

does not tell you:

  • the song title;
  • the artist;
  • the genre.

That is acceptable.

Its primary job is identity.

Descriptive information belongs in other fields.

Trying to encode too much meaning into an ID can make it harder to keep stable.

Different Tables Can Have Different ID Fields

A structured music library might include:

Songs
Song ID
Title
Artist ID
Album ID
Artists
Artist ID
Artist Name
Albums
Album ID
Album Name

Each table identifies its own records.

A Song ID identifies a song.

An Artist ID identifies an artist.

An Album ID identifies an album.

The ID Is Part of the Record Structure

A simple song record could be:

Song ID: S003
Title: Northern Lights
Artist: Example Band
Genre: Rock

The ID is not decorative.

It gives the record a precise identity that can be used by people, spreadsheet formulas, programs, or other tables.

Unique IDs Prepare Data for Relationships

Once an artist has:

Artist ID = A004

a song record can refer to:

Artist ID = A004

instead of repeating all artist information.

That makes it possible to connect records across tables.

The next Learning Activity focuses on that relationship.

Keep Identity Separate From Description

Use this distinction:

Identifier

For example:

S003

identifies the song.

Northern Lights

describes its title.

Those jobs are related, but they are not the same.