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 *****
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.