Alternative to comma separated field with any possible value and amount of values in mysql-CodePudding

I have a table called tracks with the fields id and composer. The field composer might be just one name, or two names separated by commas, or a hundred names separated by commas. This seems like bad practice, and makes it very complicated to write queries where each individual value of composer has to be treated as one.

I could make more fields, like composer2, composer3 etc. up to a hundred, but the next day I might have a value with contains 101 names. What would be a better way to store this data in a mysql database?

CodePudding user response：

The better way: another table.

What you have is called a multi-valued attribute. Your example where you can have up to 100 composers suggests that you really mean there is no fixed limit.

You know what else has no fixed limit in SQL? Rows.

It is common practice to store each value of a multi-valued attribute on its own row, with a reference back to the primary table.

CREATE TABLE Tracks (
  id INT PRIMARY KEY
  ... other single-valued attributes of a track ...
);

CREATE TABLE Composers (
  id INT PRIMARY KEY
  ... composer name and other attributes of a composer ...
);

CREATE TABLE TrackComposers (
  trackId INT,
  composerId INT,
  PRIMARY KEY (trackId, composerId),
  FOREIGN KEY (trackId) REFERENCES Tracks (id),
  FOREIGN KEY (composerId) REFERENCES Composers (id)
);

The TrackComposers table in this example implements a many-to-many relationship between Tracks and Composers. For each track, there may be many rows in TrackComposers. Each row references one track and one composer.

This way you don't have any limit to the number of composers for a given track, and you also have other good features, like it's easy to find all the tracks composed by a certain person.