Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
I don't understand your system well enough to give a proper answer. The DDL code you have included is helpful, but not quite enough for me. Could you describe or further define the four entities your tables represent? It seems like PLAYER_RESULT might represent the players, but that's not clear; nor is the difference between GAME and RESULT. –  mdoyle Dec 4 '12 at 16:40
    
the system is common in sport sitation. One group where is players or teams which play matches (games) between us. In my case table game is has id opponent and player and Id result which I divided into left and right side and also overtime. Table game has just IDs to join player result and result –  hudi Dec 4 '12 at 19:32
    
I'm still not quite clear on some of this but I will take a stab at what I think should work. You can tell me if there is any functionality you need that my suggestion doesn't seem to address. –  mdoyle Dec 4 '12 at 20:34
    
Any feedback? Was it what you were looking for? –  mdoyle Dec 6 '12 at 18:26
    
I need some time to think about ur example –  hudi Dec 6 '12 at 22:14

1 Answer 1

up vote 2 down vote accepted

UPDATED version based on feedback:

CREATE TABLE group (
    group_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY(team_id)
);

CREATE TABLE player (
    player_id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    group_id INT,
    player_rank INT,
    PRIMARY KEY(player_id)
);

CREATE TABLE game (
    game_id INT NOT NULL AUTO_INCREMENT,
    match_date DATE,
    overtime TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (game_id)
);

CREATE TABLE player_game (
    game_id INT NOT NULL,
    player_id INT NOT NULL,
    player_score INT,
    PRIMARY KEY (game_id, player_id)
);

...and UPDATED explanation: I think group and player are self-explanatory. The table game will have one record per match. The player_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.

share|improve this answer
    
you dont understand my question. I dont have any teams in my model. Lets say we have some tournament in ping pong. There are players in group which need to play games between us and I need to convert this situation into database model –  hudi Dec 7 '12 at 9:01
    
I see, the groups aren't teams, they are collections of individuals that play against each other. Give me a bit to rework this. –  mdoyle Dec 7 '12 at 21:19
    
So can a player be in more than one group at a time? For example, could there be one group that plays each other in billiards, and another that plays each other in darts, and someone could be in both groups? –  mdoyle Dec 7 '12 at 21:25
    
nope same player can be just in one group –  hudi Dec 8 '12 at 18:31
    
thx but I have still one problem. How to select game from db: Game has player and oponent. Player and oponent have list of games so another game has new player etc... –  hudi Dec 11 '12 at 7:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.