How to store a matrix in a database

Question

I am going to create a game that involves decision matrix's like the prisoner's dilemma. Much like the image below, except not necessarily symmetrical, and the values will change with each matrix I generate.

prisoner's dilemma

I plan to query the data against a decision table to understand what decisions players made depending on the value for each payoff. I plan to use MySQL. I'm trying to figure out how to structure the data.

The obvious solution to me is to create a single table, with a column for each of the 8 decision payoffs.

player_1_payoff_confess_confess
player_2_payoff_confess_confess
player_1_payoff_silent_silent
player_2_payoff_silent_silent
player_1_payoff_silent_confess
player_2_payoff_silent_confess
player_1_payoff_confess_silent
player_2_payoff_confess_silent

This feels kind of clunky. Is there a better way to structure this data?

• RubberDuck: Hmm... How do you plan to query the data? I suspect that how you want to query it will have a big part to play in how you store it.
• mkk: Why do you want to store it in DB in the first place ? It seems that this data doesn't change at all, why not just use some kind of constants ?
• Goose: @mkk, I'll be generating thousands of these matrix's with random values, and then users will "play" the decision matrix against each other.
• Goose: @RubberDuck I'm not sure, I was thinking the structure would come first. It will definitely be only INSERT and SELECT, no UPDATE if that helps.
• whatsisname: @Goose: the difference between a "works for this" and a general solution can often be a gigantic difference in complexity.

Answer 1

If only two prisoners are possible for a given matrix like in the original dilemma :

enter image description here

• ANSWER contains the possible answers (confess, remain silent, etc.)
• PLAYER_ANSWER contains the years (result) for each prisoner for each possible PLAYER1,PLAYER2 and ANSWER combination.

Adding a third entity (SCENARIO) allows for multiple scenarios (different than the original dilemma) to be set up separately:

enter image description here

• Goose: That's a good idea. I don't understand what the Scenario table would hold/do, can you elaborate?
• Tulains Córdova: The scenario only contains an ID and a NAME. It allows you to have separate matrixes and tell them apart since the PLAYER_ANSWER table will have a FK to scenario. You can have an scenario where the years obtained by the players vary from the original classic dilemma, or even then answers differ from the original dilemma, all stored in the same central table, as long as you can filter them apart thanks to the FK to scenario.
• Goose: Ah, makes sense. I'm thinking since there's only a binary choice (coness or silent), that I'd just add two action_player1 and action_player2, effectively making it a payoff table, then 4 payoff rows for each scenario, and the player table would basically be the user table. Does that sound reasonable?
• Goose: And perhaps, have columns player1_key and player2_key in the scenario table, and those relate to my player/user table.