Best way to handle "two-to-many" relation

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?

That sounds quite unique :slight_smile:

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.

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?

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.

So in other words, you have following for one game, right?

game

|-- entrant 1

|-------- profile X

|-- entrant 2

|-------- profile Y

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;



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:

[color="#ff0000"]b[/b][/color] can rate it. Average of all ratings given to all tournaments is stored in player profile [I think that is not your scenario]

[color="#2e8b57"]b[/b][/color] 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[color="#2e8b57"] (2).[/color] 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.

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?

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...



P.S. Off course you need to define all those relations in Models (but this shouldn’t be complicated :D)

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.

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 :)

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.

Luckily for me, yes.