What is a practical way to normalize a SQLite table with repeated groups?

Imagine a music library where a track row holds several artist names in one field:

tracks(track_id, title, artist_names)
(10, 'First Light', 'Mira Vale, Jun Park')

The problem is not that SQLite cannot store that text. It is that the field represents multiple artist-to-track relationships at once. That can make tasks less clear: how would you find tracks by Jun Park, rename that artist everywhere, or avoid treating slightly different spellings as different artists?

Those are practical design concerns, not claims established by SQLite’s foreign-key documentation. A useful starting point is to put artist records in one table and refer to them by ID. SQLite’s example uses separate artist and track tables, with a track storing an artist key; a foreign key can require the referenced artist row to exist. [S1]

The original layout can be sketched like this:

tracks(track_id, title, artist_names)

For tracks with one artist each, split the data into related tables:

CREATE TABLE artists (
  artist_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE tracks (
  track_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  artist_id INTEGER NOT NULL,
  FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

Add Mira Vale and Jun Park as separate rows in artists. Then put the appropriate artist ID in each track’s artist_id column. Each track row now refers to one artist rather than storing a list of names.

This sketch assumes every track has exactly one artist. It does not model collaborations; using it for a track with multiple artists would bring back the repeated-group problem.

If a track can have several artists, represent each track–artist pairing as its own row in a linking table. For example:

CREATE TABLE track_artists (
  track_id INTEGER NOT NULL,
  artist_id INTEGER NOT NULL,
  PRIMARY KEY (track_id, artist_id),
  FOREIGN KEY (track_id) REFERENCES tracks(track_id),
  FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

A track featuring Mira Vale and Jun Park gets two rows in track_artists: both use the track’s ID, and each uses a different artist ID. The composite primary key is a design choice that prevents the same artist–track pair from appearing twice.

To read the artist names for a track, join the tables:

SELECT t.title, a.name
FROM tracks AS t
JOIN track_artists AS ta ON ta.track_id = t.track_id
JOIN artists AS a ON a.artist_id = ta.artist_id;

The tradeoff is more tables and joins in exchange for explicit, separately stored relationships. SQLite’s documentation supports the separate-table and foreign-key pattern, but does not prescribe which schema best fits your application. [S1]

Not by itself. SQLite’s documentation says foreign-key enforcement is disabled by default and must be enabled separately for each database connection. [S1] Enable it when opening each connection, before relying on those constraints:

PRAGMA foreign_keys = ON;

For the single-artist schema, a foreign key can require a track’s artist_id to match an artist row. For the linking-table version, the two foreign keys express that each pairing must refer to existing track and artist rows. The documentation also establishes the per-connection setting; it does not establish that this design solves every data-quality concern or choose between one artist per track and multiple artists per track. [S1]

Before changing an existing table, decide what each stored value means and how you will map existing text values to artist rows. That mapping is application-specific, so check it against your data.