What should my Model relationships be for this CakePHP app?

Question

I am having trouble figuring out how to structure this. Here is my app:

It is a games website...say the games are "word search", "sodoku", and "crossword puzzle".

Each game has many puzzles. So there are 100 or so different word searches to play, 100 or so different sodokus, etc.

Each game also has many modes. So there are 5 or so ways to do each word search (timed, not timed, etc), 5ish ways to play sudoku, etc.

Each game-mode-puzzle combinationhas many highscores. So if you play a wordsearch #3 timed, a certain highscore table appears. If you play the same puzzle not timed, a different highscore table appears, and if you play #4 timed a third highscore table appears etc.

Lastly each highscore has one user.

The way I made it originally was that Each game has many puzzles and modes, and each puzzle has many highscores, and each highscore has one user. As you can probably see, this doesn't make much sense, because why does a highscore belong to a puzzle, but not a mode? When I displayed a highscore, I would display Game->Puzzle->Highscore, and add conditions that the mode of the highscore=the current mode. There's no reason I should need to have conditions, because the relationships are clear.

So this is what I had:

Game->Puzzle->Highscore->User
    ->Mode

I realized that a better way to structure this would be a HABTM relationship with the puzzles and modes. So Each game has many mode_puzzles, and each mode_puzzle has one mode, one puzzle, and many highscores.

So it could be revised to this:

Game (has many)->ModePuzzle (has many)->HighScore (has one)->User
                            (has one) -> Mode
                            (has one) -> Puzzle

The problem with this is that now when I want to add a new mode to a game, or add a new puzzle to a game, I have to deal with this cumbersome new table that I have to create manually even though it should be possible to have this relationship without creating an entire new table. For each game, any mode can be with any puzzle, so why should I have to manually create this table?

People suggested to use bake but from what I've seen bake auto-generates models, but I need the table itself to be auto-generated.

I have a feeling the solution has to do with joins or something but I am not experienced in MySQL joins or how cakephp handles them or anything like that. Also people suggested to say each puzzle has many modes and each mode has many highscores but I don't want to do this because there is no difference between each puzzle having many modes or each mode having many puzzles...and I am caching the entire structure so if I have to repeat every mode for each puzzle or vise versa the array becomes huge. Does anyone have a solution to this problem?

Answer 1

You may want to better familiarize yourself with the model associations CakePHP offers. Here is the solution I would propose:

class Game extends AppModel {
    public $hasMany = array('Puzzle');
    ...
}

class Mode extends AppModel {
    public $hasMany = array('Puzzle');
    ...
}

class Puzzle extends AppModel {
    public $belongsTo = array('Game', 'Mode');
    public $hasMany = array('HighScore');
    ...
}

class User extends AppModel {
    public $hasMany = array('HighScore');
    ...
}

class HighScore extends AppModel {
    public $belongsTo = array('Puzzle', 'User');
    ...
}

The puzzles table should have two foreign keys, game_id and mode_id. Same with the high_scores table, its foreign keys should be puzzle_id and user_id. I think the organisation of puzzles in relation to mode and game is obvious enough as these are basically categories. The highscores table is essentially a "has and belongs to many" relationship between a puzzle and a user (many users play many games) with a bit of extra information, their score.

This solution seems obvious if you sketch the relationships, as I did while reading the first few lines of your question.

 Game  Mode
    \  /
     \/
   Puzzle  User
        \  / 
         \/
      HighScore