How to deal with metadata in a Many-To-Many relationship with Spring Data JDBC?

Question

I have a project, where bot_users play in game_tables. So I have a join table. I also store the buy_in (points available to the player at the table) in that join table. My SQL:

CREATE TABLE bot_users (
    user_id bigint PRIMARY KEY,
    free_points bigint CHECK (free_points >= 0),
    frozen_points bigint CHECK (frozen_points >= 0)
);

CREATE TABLE game_tables (
    channel_id bigint PRIMARY KEY,
    owner bigint REFERENCES bot_users(user_id),
    in_game boolean NOT NULL
);

CREATE TABLE game_tables_participants (
    game_table_id bigint REFERENCES game_tables(channel_id),
    participant_id bigint REFERENCES bot_users(user_id),
    buy_in bigint NOT NULL,
    PRIMARY KEY (game_table_id, participant_id)
);

My question now is: how do I represent that buy_in metadata in entities in Java? If buy_in didn't exist, I would simply have a ParticipantRef of which I would have a Set in the GameTable entity, and then have methods that work on IDs there. But I want to have buy_in available in the code too, so should I create a ParticipantRef-like entity that contains the buy_in? If yes, then how would persisting it work?

Answer 1

Your proposed solution is pretty much the way to do this. Instead of having a ParticipantRef with just the userId of the BotUsers it also gets a buyIn attribute.

Since you have an object references from GameTable via a Set to the ParticipantRef it will be considered part of the GameTable-Aggregate and get persisted whenever you persist a GameTable instance.