Referencing multiple user id's in a single field

by David   Last Updated June 13, 2019 09:06 AM

I'm creating a system where a user can make a prediction about an event, and if they get all the predictions correct they win.

I have three tables: users, games and predictions. The structure of each:

Users:
    id
    username
    password
Predictions:
    id
    game_id (linked to Games id)
    user_id (linked to Users id)
    prediction
Games:
    id
    questions
    answers
    winners *****

Relations:

Prediction.belongsTo(Game) Game.hasMany(Prediction) Prediction.belongsTo(User) User.hasMany(Prediction)

In the Games table, for the winners, I was going to have an array of user_ids of who won - I was going to store this as an array but realised MySQL doesn't support arrays. After a little googling, it seems my initial solution 'breaks the relational model'. I obviously don't want to do this.

What is the best way to store all the users that have won a game? I only need to do this operation once per game. It's not like more winners can occur after the event happens.

Tags : mysql sequelize


Related Questions


Updated April 18, 2019 03:06 AM

Updated February 25, 2019 15:06 PM

Updated April 11, 2019 21:06 PM

Updated November 15, 2016 08:02 AM

Updated April 09, 2015 22:02 PM