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.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_confessplayer_2_payoff_confess_confessplayer_1_payoff_silent_silentplayer_2_payoff_silent_silentplayer_1_payoff_silent_confessplayer_2_payoff_silent_confessplayer_1_payoff_confess_silentplayer_2_payoff_confess_silentThis feels kind of clunky. Is there a better way to structure this data?
How to store a matrix in a database
database;database design;mysql
If only two prisoners are possible for a given matrix like in the original dilemma :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: