0

This is (I think) a generic question, but here's a specific example. Suppose that:

  1. Albums each have 1-n Tracks.
  2. Each Track is in exactly one Album. (Even if "Logging with the Bloggs" appears in both Bloggs' First Album and Bloggs' Greatest Hits, these are 2 entities for my purposes.)
  3. Each Track has exactly one Artist.
  4. Artists have 1-n Tracks.
  5. Each Album has 1 Album_Artist. (Where all the Tracks are by Bloggs, the Album_Artist is Bloggs. Where the Tracks are by different Artists, as in A Collection of Great Logging Songs, the Album_Artist is Various.

I want to be able to answer questions like:

  • List all the Bloggs albums
  • Find all the Bloggs Tracks with 'logging' in the title
  • List all the Album_Artists.

So what's the best table model? Currently I have an Artists table with a boolean column for IsAlbumArtist? Then Tracks has a foreign key Artist_id and a foreign key Album_id . and Albums has a foreign key Artist_id

But would it be preferable to have a separate AlbumArtists table, or is this a moment to think about table inheritance?

Thanks for any design info and tips!

NickR
  • 298
  • 4
  • 11
  • Album_Artist flag and/or AlbumArtists kind table do not need to be present at all :) You can always deduce Album_Artists from other tables and relations. – Arvo May 05 '21 at 11:15
  • Thanks Arvo! That's true: but isn't there some benefit in calculating whether an Artist is AlbumArtist once and storing it, rather than iterating over the whole db each time I ask the question? Or, for a few thousand rows, is the work so trivial as not to be worth worrying about? – NickR May 05 '21 at 21:49
  • And - leaving aside for the moment the question of whether I need it at all - would you be in a position to comment on which design structure would be preferable? – NickR May 05 '21 at 21:50
  • Highly opinion based (and I have no theoretical base), but IMO boolean flag is not correct here. Currently it indicates that 'this artist has issued at least one solo album', but doesn't answer to other related questions. If you foresee this question asked in majority of cases - then yes, it can be usable; otherwise not. You also need specific procedure to update it while adding and/or updating (correcting) album info - like for all cases, when information is duplicated; same for albumartists table of course. – Arvo May 06 '21 at 07:07

1 Answers1

0
-- Artist ART exists.
--
artist {ART}
    PK {ART}

-- Make sure to add "Various" to the list of artists.

-- Album ALB by album-artist ART exists.
--
album {ALB, ART}
   PK {ALB}

FK1 {ART} REFERENCES artist {ART}

-- Track number TR_NO of album ALB 
-- performed by artist ART is named TR_TITLE.
--
track {ALB, TR_NO, ART, TR_TITLE}
   PK {ALB, TR_NO}
   AK {ALB, TR_TITLE}

FK1 {ALB} REFERENCES album  {ALB}
FK2 {ART} REFERENCES artist {ART}

-- List all the Bloggs albums
--
SELECT ALB
FROM album
WHERE ART = 'Bloggs' ;

-- Find all the Bloggs tracks
-- with 'logging' in the title.
--
SELECT ALB, TR_NO, TR_TITLE
FROM track
WHERE ART = 'Bloggs'
AND TR_TITLE like '%logging%' ;

-- List all the album-artists
--
SELECT ART
FROM album ;

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key

This is a simplified logical design, use it as a proof of concept. To use (add)integers or codes for PKs take a look at this example.

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71