I need help with my database table design.
I have group which contains a list of players. In every group each player plays games with other players. Game has also some result for example 1:0 and there can be overtime too.
I already created some tables but I dont think this is a good solution. There is problem when I create java classes from these tables.
PlayerResult has lists of games. Game has opponent which is again PlayerResult which have listOfGame again etc… I dont know how to solve this cycle because every playerResult should know all their game to count score and point. Please help me improve the table design.
CREATE TABLE IF NOT EXISTS `GROUP` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `NAME` (`NAME`,`GROUP_ID`),
KEY `TOURNAMENT_ID` (`TOURNAMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `PLAYER_RESULT` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`POINTS` int(11) DEFAULT NULL,
`RANK` int(11) DEFAULT NULL,
`GROUP_ID` int(11) NOT NULL,
`PLAYER_ID` int(11) NOT NULL,
`SCORE` varchar(10) NOT NULL,
PRIMARY KEY (`ID`),
KEY `PLAYER_ID` (`PLAYER_ID`),
KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `RESULT` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`LEFT_SIDE` int(11) DEFAULT NULL,
`RIGHT_SIDE` int(11) DEFAULT NULL,
`OVERTIME` tinyint(1) DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
CREATE TABLE IF NOT EXISTS `GAME` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`PLAYER_RESULT_ID` int(11) NOT NULL,
`OPPONENT_ID` int(11) NOT NULL,
`RESULT_ID` int(11) NOT NULL,
PRIMARY KEY (`ID`),
KEY `VYSLEDKYHRACA_HRACA_ID` (`PLAYER_RESULT_ID`),
KEY `RESULT_ID` (`RESULT_ID`),
KEY `OPPONENT_ID` (`OPPONENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Best Answer
UPDATED version based on feedback:
...and UPDATED explanation: I think
group
andplayer
are self-explanatory. The tablegame
will have one record per match. Theplayer_game
table records the players in a match and the results; each match will have two records, one for each player.Since you appear to be using InnoDB as the storage engine you may want to use foreign keys to enforce referential integrity, and obviously you can add other constraints and indexes as needed. If you need some functionality that this doesn't seem to cover, ask and I will either explain or add it.