CODEWITHSUNDEEP
mysqldatabase
JMVanPelt
JMVanPelt

Reputation: 113

Should a boolean field or a separate table be used?

In order to learn MySQL I'm building a music CD database, which is pretty complex but so far I'm doing rather well. I have set up, among others, a table with albums, another with artists and an album_artists one which links album_id's with artist_id's. But in an album with various artists, usually one, or some, of them are the main artists, so when making a query I shouldn't order them by alphabetical or id order, but by primary or secondary. Question is:
Should I make a separate table of secondary_artists, identical to the original album_artists one, or make a boolean isPrimary field in the album_artists table? Are both ways acceptable?

Upvotes: 1

Views: 113

Answers (1)

O. Jones
O. Jones

Reputation: 108686

Many bibliographic / discographic schemes for recording multiple creators assign an ordinal number to each contributor. So, instead of a flag indicating "primary", your album_artist table could contain

      album_id artist_id artist_order

So if

  • "Daylight Again" had album_id 314,
  • David Crosby had artist_id 87,
  • Steven Stills had artist id 33,
  • Graham Nash had artist id 50,

your album_artist table would have these rows.

album_id  artist_id artist_order
  312        87         1
  312        33         2
  312        50         3

This would give you sufficient information to get the artists in the order mentioned in the work (which is the right order for most catalogs).

Don't put "secondary" artists in a different table.

Upvotes: 2

Related Questions