Yii Framework Forum: Best way to handle "two-to-many" relation - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Best way to handle "two-to-many" relation Rate Topic: -----

#1 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 10 March 2010 - 09:08 AM

Dealing with 3 tables: `profile`, `entrant`, and `game`. Profile:entrant is one:many. Entrant:game is effectively "two-to-many", in that each game always has exactly two entrants, while each entrant can have many games.

`Game` has a `player1` column and a `player2` column. Each of these columns has a BELONGS_TO relation with the `entrant` table. And `entrant` has a BELONGS_TO relation with `profile`.

I'm trying to work out the best way to include profile data when finding games. As is I get a column ambiguity error, because both `player1` and `player2` are referencing the same relation in `entrant`. It seems to me I could either add another relation to `entrant`, with a different name but effectively a duplicate of the `profile` relation. Or I could always do two queries when finding games. Any thoughts?
0

#2 User is offline   intel352 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 196
  • Joined: 05-February 10
  • Location:Southport, NC

Posted 10 March 2010 - 11:53 AM

That sounds quite unique :-)

For tables that have multiple references to the same external table, I create a new relation for each column (Yii auto-builds relations the same way).

So Player1 and Player2 would each have a relation pointing to profile.
Need live Yii support? - Join the #yii IRC channel on Freenode!
0

#3 User is offline   aztech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 206
  • Joined: 12-December 08
  • Location:Poland

Posted 10 March 2010 - 12:31 PM

Could you paste "create" SQL for that 3 tables? I think then it will be much easier to help.
From which point of view are you looking for games. From profile, from entrant?
BTw, why do you need entrant. Can't be those data stored in game?
I'm not complete idiot... some parts are missing!
0

#4 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 10 March 2010 - 12:40 PM

View Postintel352, on 10 March 2010 - 11:53 AM, said:

That sounds quite unique :-)

For tables that have multiple references to the same external table, I create a new relation for each column (Yii auto-builds relations the same way).


Right, which is already how I have `game` vis-a-vis `entrant`: `game` has two relations pointing to `entrant`. As the tables are now I can't point these directly, because `game` only knows the `entrant` id, which is not the same value as the `profile` id (profile:entrant is one:many). I suppose a third alternative to those I listed above is to add a player1profile and player2profile column to `game`.
0

#5 User is offline   aztech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 206
  • Joined: 12-December 08
  • Location:Poland

Posted 10 March 2010 - 12:56 PM

So in other words, you have following for one game, right?
game
|-- entrant 1
|-------- profile X
|-- entrant 2
|-------- profile Y
I'm not complete idiot... some parts are missing!
0

#6 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 10 March 2010 - 01:01 PM

View Postaztech, on 10 March 2010 - 12:31 PM, said:

Could you paste "create" SQL for that 3 tables? I think then it will be much easier to help.
From which point of view are you looking for games. From profile, from entrant?
BTw, why do you need entrant. Can't be those data stored in game?


OK, for a more complete picture there are really 5 tables involved. Entrant serves the purpose of associative table between Profile and Tournament, a many:many relation. Additionally, Entrant stores the Profile's rating as of that Tournament (the rating stored in Profile changes over time). I've left out a few other columns, but here's the SQL:

CREATE TABLE `entrant` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `playerId` int(10) unsigned NOT NULL,
  `tournamentId` int(10) unsigned NOT NULL,
  `rating` decimal(3,1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `playerId` (`playerId`),
  KEY `tournamentId` (`tournamentId`),
  CONSTRAINT `entrant_ibfk_2` FOREIGN KEY (`tournamentId`) REFERENCES `tournament` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `entrant_ibfk_1` FOREIGN KEY (`playerId`) REFERENCES `profile` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `event` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tournamentId` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tournamentId` (`tournamentId`),
  CONSTRAINT `event_ibfk_1` FOREIGN KEY (`tournamentId`) REFERENCES `tournament` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `game` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `player1Id` int(10) unsigned NOT NULL,
  `player2Id` int(10) unsigned NOT NULL,
  `eventId` int(10) unsigned NOT NULL,
  `score1` tinyint(3) unsigned DEFAULT NULL,
  `score2` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `player1Id` (`player1Id`),
  KEY `player2Id` (`player2Id`),
  KEY `eventId` (`eventId`),
  CONSTRAINT `game_ibfk_3` FOREIGN KEY (`eventId`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `game_ibfk_1` FOREIGN KEY (`player1Id`) REFERENCES `entrant` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `game_ibfk_2` FOREIGN KEY (`player2Id`) REFERENCES `entrant` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `profile` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `lastName` varchar(31) NOT NULL,
  `firstName` varchar(31) NOT NULL,
  `rating` decimal(3,1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tournament` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `start` date NOT NULL,
  `end` date NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

0

#7 User is offline   aztech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 206
  • Joined: 12-December 08
  • Location:Poland

Posted 10 March 2010 - 04:45 PM

Thx, this gives me better picture. Looking on that I understand that your idea is following (let me know if I'm wrong):
Your main object is tournament. During tournament several events may be organized. During each event some games are played. One game is always played between two players. Now comes two scenarios into my mind (because I'm unsure what do you mean by rating)
So, if player played game in event of tournament, he:
(1) can rate it. Average of all ratings given to all tournaments is stored in player profile [I think that is not your scenario]
(2) is collecting some achievements called by you rating (something like points for 1st, 2nd... last place in Formula 1 or other sports). Depending on some criteria (e.g. date, number of tournament played) average players rating is calculated and stored in users profile [I think this is your scenario]. Profile shows always most up-to-date players rating.

All below suggestion assumes that we are in scenario (2). I feel that you have complicated constraints to much, therefore I suggest to do following:
1) change assignment for player1Id and player2Id to point profile id => simplify searching games played player in tournament and event.
2) remove id from entrant table and create composite primary key: tournament_id + player_id => you will be easy to find players/profiles rating for particular tournament.

P.S. I'm a big fan of scrabble and currently I'm rebuilding my club home page which serves similar to your data (player is playing in tournaments, each tournament has several rounds - usually up to 12, during each round one player is playing one game, so N players are playing floor((N-1)/2) games per round; after each round classification is calculated, each player earns something what is called scalp; scalps depends on win/tie/defeat and opponent strength; at the end of tournament I'm recalculating players ranking using scalps collected in all games played during tournament, etc;).

So if you will be so kind and you will try to explain you biz rules which you are tying to describe using your tables (as I did in P.S.1 of this post) I can help you in refactoring your table schemes.
I'm not complete idiot... some parts are missing!
0

#8 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 10 March 2010 - 05:27 PM

Thanks very much for giving it so much thought. Your description is correct, tournament has several events, each event has several games, always between two players. Rating is as you say in (2), an ongoing scoring system. All in all very similar to the Scrabble system you describe. Perhaps the main difference is that in my case the rating/ranking is not recalculated until the end of the tournament (although in the future we may go to a different system, where rating is recalculated after each game).

You're quite right that a composite primary key makes sense for Entrant. I started out that way, and can't remember why I added the extra column.

My only concern about your suggestion #1, relating game directly to profile, is that when doing the rating calculation I need the value from Entrant, not Profile. And probably want to display this on the public side also, i.e. when displaying games I need both profile info as well as Entrant rating for each player. So it seems to me that if I relate game directly to profile, I solve the profile problem but then create the same problem with regard to rating. Am I missing something?
0

#9 User is offline   aztech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 206
  • Joined: 12-December 08
  • Location:Poland

Posted 10 March 2010 - 06:43 PM

Hm... maybe we can also add tournament_id field (foreign key to id of tournament). This seems to be reasonable, cause your game always belongs to only one tournament. With tournament_id in game table it will be really easy to calculate rating.
#Find out all ratings in all tournaments for all players
Profile::model()->with( 'entrants ' )->together( )->findAll( );

#Find out all ratings in all tournaments for all players with all events and tournaments data
Profile::model()->with( 'entrants' , 'events.tournaments' )->together( )->findAll( );

#Find out all games played in tournament
Tournament::model( )->with( 'events.games.profiles' )->together( )->findByPk( 1 ); 

#etc...

I'm not complete idiot... some parts are missing!
0

#10 User is offline   aztech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 206
  • Joined: 12-December 08
  • Location:Poland

Posted 10 March 2010 - 06:47 PM

View Postaztech, on 10 March 2010 - 06:43 PM, said:

Hm... maybe we can also add tournament_id field (foreign key to id of tournament). This seems to be reasonable, cause your game always belongs to only one tournament. With tournament_id in game table it will be really easy to calculate rating.
#Find out all ratings in all tournaments for all players
Profile::model()->with( 'entrants ' )->together( )->findAll( );

#Find out all ratings in all tournaments for all players with all events and tournaments data
Profile::model()->with( 'entrants' , 'events.tournaments' )->together( )->findAll( );

#Find out all games played in tournament
Tournament::model( )->with( 'events.games.profiles' )->together( )->findByPk( $tournament_id ); 

#Find out all data of one game
Game::model( )->with( 'events.tournament' , 'profiles' )->together( )->findByPk( $game_id );
#or all games in particular tournament
Game::model( )->with( 'events.tournament' , 'profiles' )->together( )->findAll( array ( 'condition' = > 'tournament_id = :tournament_id' , 'params' => array ( $tournament_id ) ) );

#etc...



P.S. Off course you need to define all those relations in Models (but this shouldn't be complicated :D)
I'm not complete idiot... some parts are missing!
0

#11 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 10 March 2010 - 07:39 PM

Thanks again; I'll have a go at it.

Game always belongs to one event, and event always belongs to one tournament, and I need to organize games by event, so I thought it enough for games to relate to events.
0

#12 User is offline   aztech 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 206
  • Joined: 12-December 08
  • Location:Poland

Posted 11 March 2010 - 04:38 AM

Generally you are right. But adding tournament_id allows you to find out games belonging to tournament in easiest way. You case is quite simple. But imagine database which keeps information about players who started in all Olympic games.
Those tables (and its hierarchy) will look following:
olympic_games => sports => rounds => classifications => players
If you will keep in all tables only one reference, to find out official clasification for hockey in Vancouver you will need to go from olympic_games through sports, rounds to classification. But if you will keep in classification information about all parent, this will be only one query without any joins, or at least one query with really simple and light join.

Porting this example into your case, if you would like to find out all games for particular tournament you will need just to call
Tournament::model()->with( 'games ')->findAll( )
or opposite, if you would like to find out information about game (with relevant tournament and event data)
Game::model()->with( 'tournament' , 'event' )->find( /*here conditions for tournament and/or event */)


I know that this tournament_id key is redundant, but it speeds up searching data in many cases.

P.S. After rewriting most of my code into new version (form PRADO to Yii) I've changed also organization of primary keys also using above approach of storing all relations in each object and this seems to be good decision (page is working much much faster, I've less queries, all of them are stressing my database much much less than previous queries ).

P.S.2. I've a feeling at beginning that our project are really close to each other :)
I'm not complete idiot... some parts are missing!
0

#13 User is offline   jsoo 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 65
  • Joined: 01-March 10
  • Location:Durham, NC, USA

Posted 11 March 2010 - 08:16 AM

View Postaztech, on 11 March 2010 - 04:38 AM, said:

After rewriting most of my code into new version (form PRADO to Yii) I've changed also organization of primary keys also using above approach of storing all relations in each object and this seems to be good decision (page is working much much faster, I've less queries, all of them are stressing my database much much less than previous queries ).


OK, I'm convinced! This is exactly the sort of practical advice I need, because at this point I don't have a feel for how these decisions now will affect performance later, when there are thousands of games to query.

Quote

P.S.2. I've a feeling at beginning that our project are really close to each other :)


Luckily for me, yes.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users