Recommended approach to store relationships

[jcavar]

Hello, thanks for amazing library!

Our REST API returns data with the following structure (simplified):

{"id": "1", "teams": [{"id": 1, ...}]}

which would be represented as:

struct UserResponse {
  let id: String
  let teams: [TeamResponse]
}

User can belong to many teams, and multiple users can belong to the same team.

I was wondering what would be recommended approach for data model to write combined records?

With CoreData model, that is abstracted away and we can simply say:

class UserModel: NSManagedObject {
    let id: String
    let teams: [Team]
}

Now, with GRDB, as associations guide suggests, record should only write to its table (which makes sense).

So an option would be to decompose REST API response, create data model for each table and write that in specific order. That would look something like this:

struct UserModel {
  let id: String
}

struct TeamModel {
  let id: String
}

struct UserTeamModel {
  let userId: String
  let teamId: String
}

let teams = response.teams.map { TeamModel($0) }
teams.write()
let user = UserModel(response)
user.write()

let userTeams = teams.map { UserTeam($0.id, user.id) }
userTeams.write()

[groue]

Hello @jcavar,

User can belong to many teams, and multiple users can belong to the same team.

This is a classic many-to-many relationship. In a relational database, those are indeed usually implemented with three tables:

+------+       +----------+        +------+
| user |       | userTeam |        | team |
+------+       +----------+        +------+
| id   < - - - • userId   |    / - > id   |
| ...  |       | teamId   • - /    | ...  |
+------+       +----------+        +------+

The name of the pivot table is always tricky to choose, but "userTeam" is a fine convention. "TeamMembership" would be fine as well.

Make sure you define foreign keys from the columns of the pivot table to the primary keys of the main tables, so that SQLite can maintain the integrity of the database.

Now that the database schema is properly defined, you can define record types that map those tables. I define below record types with fetching and persistence methods, backed by the standard Codable protocol (because it is so handy):

struct User: Codable, FetchableRecord, PersistableRecord {
  let id: String
  ...
}

struct Team: Codable, FetchableRecord, PersistableRecord {
  let id: String
  ...
}

struct UserTeam: Codable, FetchableRecord, PersistableRecord {
  let userId: String
  let teamId: String
}

Those record types can now read and write in the database. Due to SQLite integrity constraints, make sure your insert a UserTeam after its user and team are stored in the database:

try dbQueue.write { db in
    let team = Team(id: "foo", ...)
    let user = User(id: "bar", ...)
    try user.insert(db)
    try team.insert(db)
    try UserTeam(userId: user.id, teamId: team.id).insert(db)
}

That's it for the basic setup.

The actions that your application has to do with the responses of your REST api are beyond the scope of this answer. For example, do you have to synchronize the content of the database with the API responses? If so, you may have to delete, update, or insert teams, users, and userTeams. There is no one-size-fits-all algorithm here: each application has its own requirements.

Make sure you get familiar with persistence methods and the updateChanges method (this one can spare database accesses, and helps optimizing your app when there are a lot of users or teams to synchronize).

If you have any other specific question, please ask!

[jcavar]

Great, thank you! I think that answers my question. I will take a deeper look at links you provided.

[groue]

Happy GRDB :-)

[groue]

FYI @jcavar, I've just released https://github.com/groue/SortedDifference. It helps synchronizing the content of the database with the content of the server, and may partially address your particular use case.

[jcavar]

Very nice, thank you!