1

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?

Nick Manning
  • 2,828
  • 1
  • 29
  • 50

1 Answers1

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
Pádraig Galvin
  • 1,065
  • 8
  • 20
  • Thank you sooo much for the response! I think I wasn't very clear about the "modes", and I apologize for this. The modes are specific to each game, so the correct relationship would be that the Modes model is in the hasMany array in the games model. I think I am onto something with "joins" http://bakery.cakephp.org/articles/nate/2009/01/21/quick-tip-doing-ad-hoc-joins-in-model-find but I'll still have to do a little bit more research and studying, because it's in the middle of a huge threaded array so it doesn't perfectly match what's on the link. – Nick Manning May 02 '13 at 01:22
  • You're welcome! I don't think you need to do any joins, try and stick to the conventions. You can adapt this solution either by putting Mode in between Game and Puzzle or simply, as you said, by making Mode belong to Game and leaving the rest as it is, including the Game/Puzzle relationship. – Pádraig Galvin May 02 '13 at 14:10
  • Sigh...I'm pretty sure I have to use joins. I need a model that contains both information from the Modes table and Puzzles table, called "PuzzleMode", so that I can have the relationship Game->PuzzleMode->Highscore. I am trying to find a way to have this generated without having to manually create a table for it but I am finding no answers. It's basically a full join, because for each game you can have any combo of mode or puzzle, which should lead to a unique highscore table. The problem is, how do I tell this to a model without making an entire unnecessary new table? – Nick Manning May 02 '13 at 14:58
  • If you are trying to get the get the highscores from the Game controller your find query should look like `$this->Game->Puzzle->HighScore->find(...)` or `$this->Game->Mode->Puzzle->HighScore->find(...)`. What makes you think you need a PuzzleMode model? – Pádraig Galvin May 02 '13 at 15:58
  • Thank you for your response...it isn't correct to say Game->Mode->Puzzle because both the Puzzle and the Mode belongs directly to the game. The Puzzle does not belong to the Mode nor vise versa. Although it might work in the vacuum of my question, this relationship will lead to problems down the road. If I do it the right way from the beginning then nothing can go wrong, but I'm now seeing that it's not easy. I just thought of a possible solution to just get the Highscores in a separate query with session variables as conditions...not my ideal solution but the best I can come up with. – Nick Manning May 02 '13 at 16:13
  • Puzzle can belong to both Mode and Game even if Mode also belongs to Game. If Mode and Puzzle are related then there should be an association between them. What problems are you expecting with this? – Pádraig Galvin May 02 '13 at 16:25
  • Well for example, my puzzles table doesn't have a mode_id field, it has a game_id field. So getting the puzzle from the mode won't work because cakephp doesn't understand it. If I were to add a mode_id field, it would just make my table huge unnecessarily. If I could just do my ModePuzzle solution everything would be great but this just doesn't seem possible in CakePHP. Should have spent the last 3 months learning a different framework I guess. – Nick Manning May 02 '13 at 22:00
  • I doubt adding a `mode_id` field would affect performance as much as adding extra models and tables or doing complex joins. – Pádraig Galvin May 02 '13 at 23:54
  • You're right but I'd have to manually add the mode_ids to the hundreds of puzzles in the table...I could write code to do it but that would be a pain and hard to keep track up for such a simple relationship. I think I am going to try make the ModePuzzle model, and its datasource will be a generated array. I didn't know that was possible until now and that's the best solution I can think of. It's also important to me, especially in this stage, for the logic to be correct and saying each mode has many puzzles is simply not correct, even though it might work. – Nick Manning May 03 '13 at 07:27