sql - 如何设计循环赛数据库

Question

我正在开发巡回锦标赛，但是在设计数据库时遇到问题。

首先我有季节。季节包含锦标赛列表。比赛包含组列表。组包含列表参与者。然后我有球员名单。参与者与参与者之间的区别在于参与者是属于组的注册参与者。然后，我有由2个参与者及其得分定义的实体游戏。因此参与者有游戏列表。

P =参与者
G =游戏

   P1 P2 P3
P1 X  G1 G2
P2 G3 X  G4
P3 G5 G6 X

这是我比赛的好榜样吗？我不这样认为，因为我的数据库中存在重复性。 G1与G3相反，但我不知道如何更好地实现此模型

-- --------------------------------------------------------

--
-- Table structure for table `GAME`
--

CREATE TABLE IF NOT EXISTS `GAME` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `HOME_PARTICIPANT_ID` int(11) NOT NULL,
  `AWAY_PARTICIPANT_ID` int(11) NOT NULL,
  `HOME_SCORE` int(4) DEFAULT NULL,
  `AWAY_SCORE` int(4) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `HOME_PARTICIPANT_ID` (`HOME_PARTICIPANT_ID`),
  KEY `AWAY_PARTICIPANT_ID` (`AWAY_PARTICIPANT_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `GROUPS`
--

CREATE TABLE IF NOT EXISTS `GROUPS` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  `TOURNAMENT_ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `NAME` (`NAME`,`TOURNAMENT_ID`),
  KEY `TOURNAMENT_ID` (`TOURNAMENT_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `PLAYER`
--

CREATE TABLE IF NOT EXISTS `PLAYER` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `SURNAME` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `CLUB` varchar(255) DEFAULT NULL,
  `USER_ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `USER_ID` (`USER_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `PARTICIPANT`
--

CREATE TABLE IF NOT EXISTS `PARTICIPANT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `POINTS` int(4) NOT NULL DEFAULT '0',
  `RANK` int(4) DEFAULT NULL,
  `GROUP_ID` int(11) NOT NULL,
  `PLAYER_ID` int(11) NOT NULL,
  `SCORE` varchar(10) NOT NULL DEFAULT '0:0',
  PRIMARY KEY (`ID`),
  KEY `PLAYER_ID` (`PLAYER_ID`),
  KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 


-- --------------------------------------------------------

--
-- Table structure for table `SEASON`
--

CREATE TABLE IF NOT EXISTS `SEASON` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `USER_ID` (`USER_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `TOURNAMENT`
--

CREATE TABLE IF NOT EXISTS `TOURNAMENT` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  `SEASON_ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `SEASON_ID` (`SEASON_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;    


--
-- Constraints for table `GAME`
--
ALTER TABLE `GAME`
  ADD CONSTRAINT `GAME_ibfk_1` FOREIGN KEY (`HOME_PARTICIPANT_ID`) REFERENCES `PARTICIPANT` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `GAME_ibfk_2` FOREIGN KEY (`AWAY_PARTICIPANT_ID`) REFERENCES `PARTICIPANT` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `GROUPS`
--
ALTER TABLE `GROUPS`
  ADD CONSTRAINT `GROUPS_ibfk_1` FOREIGN KEY (`TOURNAMENT_ID`) REFERENCES `TOURNAMENT` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;  


--
-- Constraints for table `PARTICIPANT`
--
ALTER TABLE `PARTICIPANT`
  ADD CONSTRAINT `PARTICIPANT_ibfk_1` FOREIGN KEY (`GROUP_ID`) REFERENCES `GROUPS` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `PARTICIPANT_ibfk_2` FOREIGN KEY (`PLAYER_ID`) REFERENCES `PLAYER` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;    


--
-- Constraints for table `TOURNAMENT`
--
ALTER TABLE `TOURNAMENT`
  ADD CONSTRAINT `TOURNAMENT_ibfk_1` FOREIGN KEY (`SEASON_ID`) REFERENCES `SEASON` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

Answer 1

如果您说自己有重复性，则需要重新访问设计。您是否曾经使用过增强型关系图？这是确保您具有正确的数据库设计的好工具。

如果您以前没有这样做过，这是一个很好的网站，可以为您提供帮助:http://users.csc.calpoly.edu/~jdalbey/205/Lectures/HOWTO-ERD.html