Joining pivot table vs. duplicating columns

Question

I'm making a game, where players make moves one by one. I have following tables:

╔═════════╗
║ players ║
╠═════════╣
║ id      ║
║ name    ║
╚═════════╝
╔════════════╗
║   games    ║
╠════════════╣
║ id         ║
║ started_at ║
╚════════════╝
╔═════════════╗
║ game_player ║
╠═════════════╣
║ id          ║
║ game_id     ║
║ player_id   ║
║ turn        ║
╚═════════════╝

Now, I'd like to add moves table to keep track of game's history. I'm not sure which way is better:

1) Connect moves with pivot table game_player like this:

╔════════════════╗
║     moves      ║
╠════════════════╣
║ id             ║
║ game_player_id ║
║ made_at        ║
╚════════════════╝

2) Duplicate game_id and player_id pair like this:

╔═══════════╗
║   moves   ║
╠═══════════╣
║ id        ║
║ game_id   ║
║ player_id ║
║ made_at   ║
╚═══════════╝

The first solution makes my data more concise, there is no way to put unexisting game-player pair if I use foreign keys.

The second solution is easier to handle with ORM.

How should this problem be solved? Is there any way to use first idea comfortably with ORM, perhaps by creating another entity for game-player pairs, like Participation? How would you guys do it?

Answer 1

Initial Response

Your files do not have uniqueness or integrity. As I understand it, you want a Relational database, with Relational integrity, power, and speed.

Here is a Data Model, as a discussion point. Obviously it is not complete, as many details need to be raised and closed. However, getting somthing out there for you is faster than a question-and-answer back-and-forth, using this SO media.

Fist Draft Game Data Model

Please comment/discuss.

If you are not used to the Notation, please be advised that every little tick, notch, and mark, the solid vs dashed lines, the square vs round corners, means something very specific. Refer to the IDEF1X Notation.

Response to Comments

It seems to be far too complicated for the goal

Which goal ?

If your goal is a Relational Database (as part of whatever other goals you have for the project), then that is the minimum "complexity", the configuration that is required to produce a Relational Database, with Relational Integrity. So the onus is on you to climb that learning curve.

Otherwise, please remove the database and relational-database tags from your question.

How is my first idea against database integrity?

It isn't against integrity, it is absent integrity, the kind that is normal, ordinary, expected, in a Relational Database. Clearly, you have been reading books that allege to be about "relational databases", written by people who are evidently clueless about Relational Databases, and you have implemented what they advise, or what you have seen others do.

• You have no row uniqueness as demanded by the Relational Model. Eg. duplicate Player.names are allowed. Eg there is no context for a game.

• You have ID fields in every file. That gives Record ID (in an RFS) uniqueness, but no row uniqueness. Please read this Answer carefully.

• Eg. In both your (1) and (2), you allow moves by players that are not constrained to the game that they are playing. Any existing player_id and game_id is allowed.

The above is just an example of the type of integrity errors you have, I have not listed them all.

My suggested model fixes all the integrity errors, I did not enumerate all the errors that I fixed, I just gave you a valid database.

getting somthing out there for you is faster than a question-and-answer back-and-forth

Eg:

• The notion of a game that exists independently is not reasonable. A game only exists in the context of a player who started one. So I have implemented that. That improves the Identifiers and game is now more contextual.

• Likewise, the notion of a move that exist without a game, or without players who are playing in that specific game, is not reasonable. Yo have that. The model fixes that.

• First I have responders who join the game, that one player started. The sum of them are the players for that specific game.

• Next, I have moves that are constrained to each specific game.

• Next, I have moves that are constrained to each specific palyer in each specific game.

• Next, in order to implement that correctly, I use a basic, Relational, Basetype::Subtype structure. This differentiates the initiating_player and the responding_players. It also allows me to apply the relevant constraints to the correct tables. The is_initiator column Discriminates the Basetype.

  • If you would like more information on Subtypes, please study this document.

• All but one ID columns have disappeared, naturally, without trying to get rid of them. The one remaining for player_id is valid only because player.name might be too wide. If player.name is not their real name, if it is an user_name (eg. CHAR(12)), then change player_id to user_name, and the whole structure will be better. Please advise, and I will improve the model.

Now we have the basis for a Relational Database. I did label it a first draft, I expected questions and perhaps minor changes. I did not expect to cover the large gap between Record Filing Systems and Relational Databases. Feel free to ask questions, I might suggest opening a new question for those that require a detailed answer.