0

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.

  • 1
    Would just creating a winners table be possible? It could contain a column for the game id, the winner id, and maybe the date they won? – raphael75 Jun 13 '19 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.