My problem is best explained by example:
Suppose I have a database about chess games and players.
Which one of the following structures should I choose
player (player_id,name... etc) game (game_id,date... etc) game_player (game_id,player_id,white)
game_player.white is True/False depending if the player was playing black or white
game (game_id,black_player,white_player,date... etc) player (player_id,name... etc)
I think Option 2) is the best because I know that every game has exactly 2 players and one of them is black and one is white. However, somehow the queries in this setup don't feel so natural to me. Even in simple queries I have to join to player table twice and need conditions like
game.black_player = player.player_id
Option 1) feels good because it's immediately clear where each column is referencing. However, otherwise I don't like option 1) so much.
- So which one is the best practice and why? Or is there even better Option 3)?
- If I choose option 2) how should I name the black_player and white_player columns to be clear that they are referencing to the player table.
Hopefully my question makes sense.
I realize that this is a very basic question but I can't stop thinking about this and didn't find anything by searching.