What is the right way to insert records with 1:N and N:M relationships in a Room database using MVVM?

I'm a newbie using MVVM and Kotlin + Jetpack Compose + Room + Hilt, etc. I want to learn how to do things in the right way, and I'm getting into trouble with so much information.

I have the following entities: Game, Team and Player. A game can have many teams but a team only belongs to a game (1:N). A team can have several players and a player can be in several teams (N:M) (this might sound weird but it's a game where players play for several teams). According to this structure, I wrote the code you can see below, but now, when I try to create a game with its teams and players, I don't know if I'm doing it right or applying bad practice.

In the Game View Model, I try to create a game with the teams and the players. I would like to know if you see my code OK or there are better ways to do it. For example, is it OK to make a DAO depend on other DAO? As you can see, my Game DAO depends on Team DAO...

How would you insert the players in the database when inserting the teams? I've already have the variable teamsWithPlayers, which is a list of TeamWithPlayers, but when should I insert this in the database and how? After inserting the game? Creating more dependencies between DAOs?

ENTITIES

Game Entity

@Entity(tableName = "game")
data class Game(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    ...
)

data class GameWithTeams(
    @Relation(
        parentColumn = "id",
        entityColumn = "gameId"
    )
    val teams: List<Team>
)

Team Entity

@Entity(
    tableName = "team",
    foreignKeys = [
        ForeignKey(
            entity = Game::class,
            parentColumns = ["id"],
            childColumns = ["gameId"],
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class Team(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val gameId: Long = 0,
)

data class TeamWithPlayers(
    @Embedded val team: Team,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        associateBy = Junction(
            TeamPlayerRel::class,
            parentColumn = "teamId",
            entityColumn = "playerId"
        )
    )
    val players: List<Player>
)

Player Entity

@Entity(tableName = "player")
@Parcelize
data class Player(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val name: String,
): Parcelable

REPOSITORIES

Game Repository

class GameRepository @Inject constructor(
    private val gameDao: GameDao,
    private val teamDao: TeamDao
) {
    suspend fun insertGameWithTeams(game: Game, teams: List<Team>) {
        gameDao.insertGameWithTeams(game, teams, teamDao)
    }
    ...
}

DAOS

Game DAO

@Dao
interface GameDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertGame(game: Game): Long

    @Transaction
    suspend fun insertGameWithTeams(game: Game, teams: List<Team>, teamDao: TeamDao) {
        val gameId = insertGame(game)
        teams.forEach {
            team -> teamDao.insertTeam(team.copy(gameId = gameId))
        }
    }
    ...
}

Team Dao

@Dao
interface TeamDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertTeam(team: Team)
    ...
}

VIEW MODELS

Game View Model

val game = Game(...)
val teamsWithPlayers = createTeams(...)
val teams = teamsWithPlayers.map { it.team }
gameRepository.insertGameWithTeams(game, teams)

Solution

From a database aspect for the many-many (team(s)-player(s)) you would utilise an additional table that has two core columns. One column to reference a team, and the other column to reference a player. The primary key being a composite of the two columns.

You appear to have such a table as your TeamWithPlayers class refers to it via the association/junction specifying TeamPlayerRel::class.

As such players and teams can be added/inserted whenever BUT to add a team a game is required.

at this stage the player(s) and team(s) are unrelated to each other, although the team MUST be related to it's game.

Once the Team and players have been added/inserted you can then add/insert TeamPlayerRel row(s) building the team-player relationship(s). This assumes that the teamId and the playerId both have a foreign key constraint (via the foreignKeys parameter of the @Entity annotation).

if there are no foreign key constraints then you could have whatever values and add rows whenever.

So the important aspect is when to add rows to the TeamPlayerRel::class but this is flexible as players and teams can exist and be created whenever. It is just when inserting a row that the player and team combination must exist.

I would like to know if you see my code OK or there are better ways to do it.

You very much appear to have a grasp on the above. Except when you say I've already have the variable teamsWithPlayers, which is a list of TeamWithPlayers, but when should I insert this in the database and how?

You do not store (insert) TeamWithPlayers objects, rather you insert the TeamPlayerRel::class rows and then you can extract (query) TeamWithPlayers objects.

As previously said WHEN simply requires that the player and team exist when inserting a TeamPlayerRel row.

For example you could add/insert TeamA, then Player1, then Player3, then Player99, then Player2, then TeamZ and then TeamF .....

At this there would be no player-team (or team-player) relationships BUT the underlying teams and players exist.

For example, is it OK to make a DAO depend on other DAO?

For example you could have data that has a game list of TeamWithPlayer objects built with data that is not stored and then wish to store the data including the relationships.

So YES you can have functions (DAO's) that have a dependency on others.

again you seem to have a grasp on this

but when should I insert this in the database and how? After inserting the game? Creating more dependencies between DAOs?

When is, as said above, is flexible. You will have core functions to INSERT into the 4 tables (Game,Player,Team and PlayerTeamRel) and these could then be used within encompassing functions as required.

One consideration is that as you have integer type primary keys, for the game/player/team tables, and that this id value is valuable (e.g. you use it to uniquely identify related data), that you may find it useful to ascertain the id when inserting.

As such a suggestion is to take advantage of a feature of Room (actually of the underlying SQLite API) that when using the @Insert convenience function/method, that it returns a value (in SQLite terms the rowid) of the inserted row. This returned value equates to the id (in the case of a single integer type primary key column).

So instead of:-

@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertTeam(team: Team)

Conisder:-

@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertTeam(team: Team): Long

Your insertGame function appears to return the id of the inserted game, so perhaps you already understand this.
One thing to note is that the return value from an insert into the PLayerTeamRel table will be of little or no use.

Another suggestion is to NOT use autoGenerate = true but to instead use, for example:-

@PrimaryKey
val id: Long? = null,
...

what autoGenerate does is use the SQLite AUTOINCREMENT keyword which DOES NOT cause generation of the id BUT adds a rule saying that the generated id must be greater than any that has existed. The generation of a rowid (and hence the id) is dependant upon a single integer type PRIMARY KEY column and either null or no value.
Room takes the default value of 0, when autoGenerate=true is used, to then be null/no value. However, it doesn't convert 0 to null/no value, when autoGenerate is false, but it does handle null. ** although seldom utilised 0 is in fact a valid *rowid value, in fact you can even have negative rowid values.

You may wish to consider looking at https://www.sqlite.org/autoinc.html, the very first point The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed., should be sufficient to explain why the suggestion.

I'm a newbie using MVVM

Due to even less MVVM experience, this aspect has not been covered. However, I believe that invoking a suspend function from another can cause issue and as such the inserts should really not have suspend. Noting again the lack of MVVM experience.