Database – How to Store a Matrix in a Database

databasedatabase-designMySQL

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?

Best Answer

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

Related Topic