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
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:
Which exact record?
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.
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.
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.
An ID such as:
S001
does not tell you:
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.
A structured music library might include:
Song ID
Title
Artist ID
Album ID
Artist ID
Artist Name
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.
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.
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.
Use this distinction:
Identifier
Which exact record is this?
Descriptive fields
What information do we know about the record?
For example:
S003
identifies the song.
Northern Lights
describes its title.
Those jobs are related, but they are not the same.