Many-to-many relationship where one entity has multiple properties of the same type

Question

I'm not sure how to phrase this, which may be why I couldn't find any information, but I have a many-to-many relationship where one of the entities has multiple properties of many of the other.

For instance, take the Artist and Song relationship. One artist can have many songs, and one song can have many artists. BUT, a song can have (many) main artists, (many) featured artists and (many) album artists. So these all come from the same table, but I'm not sure how to model this.

In code, I am using GRDB in Swift, so I follow the docs with:

import GRDB
    
struct Artist: TableRecord {
    let id: Int
    let name: String
    ...
    
    static let artistSong = hasMany(ArtistSong.self)
    static let songs = hasMany(Song.self, through: artistSong, using: ArtistSong.song)
}

struct ArtistSong: TableRecord {
    static let artist = belongsTo(Artist.self)
    static let song = belongsTo(Song.self)
}

struct Song: TableRecord {
    let id: Int
    let name: String
    ...
    
    static let artistSong = hasMany(ArtistSong.self)
    static let artists = hasMany(Artist.self, through: artistSong, using: ArtistSong.artist)
}

I imagine this would be fine if there were simply only "artists" on a song. But I have 3 different types of artists for a song (main, featured, album artist) but they all come from the same Artist table.

What would be the best way to tackle this?

Answer 1

For Many to Many relationship you need additional tables to define the relationship. Like with addition to Song and Artist tables you need additional table to define main, featured, album artist relationships.

These are join table which contains common fields from two or more other tables. In this way, it creates a many-to-many relationship between data.

You can go ahead and have only one extra table with a column defining relationship between song and artist. But this will create redundancy and several anomalies. For which you need Normalization. And need to have additional tables.

Normalization is the process of minimizing redundancy from a relation or set of relations.

Note: Minimum of three tables are required in the Many to Many relationships. If we try to merge it will create redundant data.

Answer 2

But I have 3 different types of artists for a song (main, featured, album artist) but they all come from the same Artist table.

What would be the best way to tackle this?

You can filter the Song.artists relationship. And you can define filtered associations as well:

extension Song {
  static let artists = hasMany(...)
  static let mainArtists = artists
    .filter(...) // Column("kind") == "main", maybe
    .forKey("mainArtists")
}

In the above example, I changed the key of the mainArtists association, so that:

• you can use both artists and mainArtists associations in the same request, when needed.
• the mainArtists association is decoded in the mainArtists property of a compound record type when you fetch from requests that include(all: Song.mainArtists), for example.

Answer 3

I was interested in this too, because that's a great example for HasManyThrough associations and for an implementation of edges in a graph-like data structure. I thought you had this schema in mind:

I first tried to solve it using the answer by Gwendal Roué but didn't quite get it to work. I opened a GRDB issue to ask for more help. He answered within hours, solved all my problems and explained the solutions in detail.

This is what i learned:

• Distinct has-many- and has-many-through-associations are necessary to make sure requests treat them independently. Otherwise it would, for example, not be possible to fetch main artists and feature artists using the same request. Without distinct associations the request would combine the filters and only return artists that are defined main and feature using the same SongArtist (which is not possible, so empty arrays are returned).
  • That's why my code defines associations for each ArtistRelation. My Song struct has five has-many- and an additional five has-many-through-associations.
• When dealing with joined requests it is better to include the entire row of the closure table. It is possible to fetch structs that contain isolated columns only (for example when we are only interested in the relation) but that would make the code more complex. Gwendal Roué does not recommend this.
  • That's why my struct in code example 6 contains an entire SongArtist row although i only need its relation.

The following code contains a full scenario and these fetch requests to test the implementation:

1. All songs and the corresponding artists, but without ArtistRelation info
2. All songs and the corresponding artists, grouped in separate arrays according to their ArtistRelation
3. All songs with their number of relationships
4. All songs that have no feature artists
5. Direct access to the closure table to get all SongArtist rows that define feature artists
6. All songs with their artists and their relationships

import GRDB

struct Artist: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let songArtists = hasMany(SongArtist.self)
    static let songs = hasMany(Song.self, through: songArtists, using: SongArtist.song)
}

struct Song: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    
    // Distinct has-many-associations are necessary to make sure requests treat them independently. See https://github.com/groue/GRDB.swift/issues/1068#issuecomment-927801968 for more information.
    static let songArtists = hasMany(SongArtist.self)
    static func songArtists(forRelation relation: ArtistRelation) -> HasManyAssociation<Song, SongArtist> {
        songArtists
            .filter(Column("relation") == relation)
            .forKey("\(relation.rawValue)SongArtists")
    }
    static let albumSongArtists = songArtists(forRelation: .album)
    static let featureSongArtists = songArtists(forRelation: .feature)
    static let mainSongArtists = songArtists(forRelation: .main)
    static let partnerSongArtists = songArtists(forRelation: .partner)
    
    // Distinct has-many-through-associations are necessary to make sure requests treat them independently. See https://github.com/groue/GRDB.swift/issues/1068#issuecomment-927801968 for more information.
    static let artists = hasMany(Artist.self, through: songArtists, using: SongArtist.artist)
    static func artists(forRelation relation: ArtistRelation) -> HasManyThroughAssociation<Song, Artist> {
        hasMany(
            Artist.self,
            through: songArtists(forRelation: relation),
            using: SongArtist.artist)
            .forKey("\(relation.rawValue)Artists")
    }
    static let albumArtists = artists(forRelation: .album)
    static let featureArtists = artists(forRelation: .feature)
    static let mainArtists = artists(forRelation: .main)
    static let partnerArtists = artists(forRelation: .partner)
}

enum ArtistRelation: String, Codable, DatabaseValueConvertible {
    case album
    case feature
    case main
    case partner
}

struct SongArtist: Codable, Hashable, FetchableRecord, PersistableRecord {
    let songId: Int64
    let artistId: Int64
    let relation: ArtistRelation
    static let song = belongsTo(Song.self)
    static let artist = belongsTo(Artist.self)
}

let queue = DatabaseQueue()
try queue.write { db in
    
    try db.create(table: "artist") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "song") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "songArtist") { t in
        t.column("songId", .integer).notNull().indexed().references("song")
        t.column("artistId", .integer).notNull().indexed().references("artist")
        t.column("relation").notNull()
        // We do not define primary keys here using `t.primaryKey(["songId", "artistId"])` because we allow multiple `SongArtist` rows with the same id combination, e.g. when the album artist is also the main artist of a song. See https://github.com/groue/GRDB.swift/issues/1063#issuecomment-925735039 for an example that defines primary keys for a closure table.
    }
    
    // Testing real song data from https://music.apple.com/de/album/magnet/1102347168
    
    var missK8 = Artist(name: "Miss K8")
    try missK8.insert(db)
    var mcNolz = Artist(name: "McNolz")
    try mcNolz.insert(db)
    var radicalRedemption = Artist(name: "Radical Redemption")
    try radicalRedemption.insert(db)
    
    var scream = Song(name: "Scream (feat. Mc Nolz)")
    try scream.insert(db)
    try SongArtist(songId: scream.id!, artistId: missK8.id!, relation: .album).insert(db)
    try SongArtist(songId: scream.id!, artistId: mcNolz.id!, relation: .feature).insert(db)
    try SongArtist(songId: scream.id!, artistId: radicalRedemption.id!, relation: .main).insert(db)
    try SongArtist(songId: scream.id!, artistId: missK8.id!, relation: .partner).insert(db)
    
    var raidersOfRampage = Song(name: "Raiders of Rampage")
    try raidersOfRampage.insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: missK8.id!, relation: .album).insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: missK8.id!, relation: .main).insert(db)
    try SongArtist(songId: raidersOfRampage.id!, artistId: mcNolz.id!, relation: .partner).insert(db)
}

// 1: All songs and the corresponding artists, but without `ArtistRelation` info
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var artists: Set<Artist>
        var description: String { "\(song.name) → artists:[\(artists.map(\.name).joined(separator: ", "))]" }
    }
    let request = Song.including(all: Song.artists)
    let result = try SongInfo.fetchAll(db, request)
    print("1: \(result)")
    // > 1: [Scream (feat. Mc Nolz) → artists:[Radical Redemption, McNolz, Miss K8], Raiders of Rampage → artists:[Miss K8, McNolz]]
}

// 2: All songs and the corresponding artists, grouped in separate arrays according to their `ArtistRelation`
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var albumArtists: Set<Artist>
        var featureArtists: Set<Artist>
        var mainArtists: Set<Artist>
        var partnerArtists: Set<Artist>
        var description: String { "\(song.name) → albumArtists:\(albumArtists.map(\.name)), featureArtists:\(featureArtists.map(\.name)), mainArtists:\(mainArtists.map(\.name)), partnerArtists:\(partnerArtists.map(\.name))" }
    }
    let request = Song
        .including(all: Song.albumArtists)
        .including(all: Song.featureArtists)
        .including(all: Song.mainArtists)
        .including(all: Song.partnerArtists)
    let result = try SongInfo.fetchAll(db, request)
    print("2: \(result)")
    // > 2: [Scream (feat. Mc Nolz) → albumArtists:["Miss K8"], featureArtists:["McNolz"], mainArtists:["Radical Redemption"], partnerArtists:["Miss K8"], Raiders of Rampage → albumArtists:["Miss K8"], featureArtists:[], mainArtists:["Miss K8"], partnerArtists:["McNolz"]]
}

// 3: All songs with their number of relationships
try queue.read { db in
    struct SongInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var albumSongArtistCount: Int
        var featureSongArtistCount: Int
        var mainSongArtistCount: Int
        var partnerSongArtistCount: Int
        var description: String { "\(song.name) → album:\(albumSongArtistCount), feature:\(featureSongArtistCount), main:\(mainSongArtistCount), partner:\(partnerSongArtistCount)" }
    }
    let result = try Song
        .annotated(with: Song.albumSongArtists.count)
        .annotated(with: Song.featureSongArtists.count)
        .annotated(with: Song.mainSongArtists.count)
        .annotated(with: Song.partnerSongArtists.count)
        .asRequest(of: SongInfo.self)
        .fetchAll(db)
    print("3: \(result)")
    // > 3: [Scream (feat. Mc Nolz) → album:1, feature:1, main:1, partner:1, Raiders of Rampage → album:1, feature:0, main:1, partner:1]
}

// 4: All songs that have no feature artists
try queue.read { db in
    let result = try Song
        .having(Song.featureArtists.isEmpty)
        .fetchAll(db)
    print("4: \(result.map(\.name))")
    // > 4: ["Raiders of Rampage"]
}

// 5: Direct access to the closure table to get all SongArtist rows that define feature artists
try queue.read { db in
    struct SongArtistInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var song: Song
        var artist: Artist
        var relation: ArtistRelation
        var description: String { "\(song.name) → \(relation):\(artist.name)" }
    }
    let request = SongArtist
        .including(required: SongArtist.song)
        .including(required: SongArtist.artist)
        .filter(Column("relation") == ArtistRelation.feature)
    let result = try SongArtistInfo.fetchAll(db, request)
    print("5: \(result)")
    // > 5: [Scream (feat. Mc Nolz) → feature:McNolz]
}

// 6: All songs with their artists and their relationships
try queue.read { db in
    // It is possible to fetch structs that only contain `relation` as an isolated column but that would make the code more complex. It is easier to fetch the entire `SongArtist` row and get the relation from there. See https://github.com/groue/GRDB.swift/issues/1068#issuecomment-927815515 for more information.
    struct SongInfo: Decodable, FetchableRecord, CustomStringConvertible {
        struct ArtistInfo: Decodable, Hashable, CustomStringConvertible {
            var songArtist: SongArtist
            var artist: Artist
            var description: String { "\(songArtist.relation):\(artist.name)" }
        }
        var song: Song
        var artists: Set<ArtistInfo>
        var description: String { "\(song.name) → \(artists)" }
    }
    let result = try Song
        .including(all: Song.songArtists
                    .including(required: SongArtist.artist)
                    .forKey("artists"))
        .asRequest(of: SongInfo.self)
        .fetchAll(db)
    print("6: \(result)")
    // > 6: [Scream (feat. Mc Nolz) → [feature:McNolz, main:Radical Redemption, album:Miss K8, partner:Miss K8], Raiders of Rampage → [album:Miss K8, main:Miss K8, partner:McNolz]]
}