Hockey Team DB

[seveneleven]

Hi
With some great help I have come up with a partial solution to a DB I am trying to create. Its for a hockey team that I would like to track statistics for. Things like, win/loss record, players goals, assists and penalty minutes, as well as team totals. Typical sports stat stuff. Anyway, I am not sure on creating the foreign keys. Here is what I have created in DBdesigner4, I look forward to any suggestions.

[code]
CREATE TABLE Assists (
  Stat_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Goals_Stat_ID INTEGER UNSIGNED NOT NULL,
  Games_Game_ID INTEGER UNSIGNED NOT NULL,
  Players_Player_ID INTEGER UNSIGNED NOT NULL,
  Time TIME NULL,
  PRIMARY KEY(Stat_ID),
  INDEX Assists_FKIndex1(Players_Player_ID),
  INDEX Assists_FKIndex2(Games_Game_ID),
  INDEX Assists_FKIndex3(Goals_Stat_ID)
);

CREATE TABLE Games (
  Game_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Assists_Stat_ID INTEGER UNSIGNED NOT NULL,
  PIM_stat_id INTEGER UNSIGNED NOT NULL,
  Players_Player_ID INTEGER UNSIGNED NOT NULL,
  Goals_Stat_ID INTEGER UNSIGNED NOT NULL,
  Teams_Team_ID INTEGER UNSIGNED NOT NULL,
  Home_team TEXT NULL,
  Away_team TEXT NULL,
  GF INTEGER UNSIGNED NULL,
  GA INTEGER UNSIGNED NULL,
  Date DATE NULL,
  Time TIME NULL,
  PRIMARY KEY(Game_ID),
  INDEX Games_FKIndex1(Teams_Team_ID),
  INDEX Games_FKIndex2(Goals_Stat_ID),
  INDEX Games_FKIndex3(Players_Player_ID),
  INDEX Games_FKIndex4(PIM_stat_id),
  INDEX Games_FKIndex5(Assists_Stat_ID)
);

CREATE TABLE Goals (
  Stat_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Games_Game_ID INTEGER UNSIGNED NOT NULL,
  Players_Player_ID INTEGER UNSIGNED NOT NULL,
  Time TIME NULL,
  PRIMARY KEY(Stat_ID),
  INDEX Goals_FKIndex1(Players_Player_ID),
  INDEX Goals_FKIndex2(Games_Game_ID)
);

CREATE TABLE PIM (
  stat_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Games_Game_ID INTEGER UNSIGNED NOT NULL,
  Players_Player_ID INTEGER UNSIGNED NOT NULL,
  Minutes INTEGER UNSIGNED NULL,
  Infraction TEXT NULL,
  PRIMARY KEY(stat_id),
  INDEX PIM_FKIndex1(Players_Player_ID),
  INDEX PIM_FKIndex2(Games_Game_ID)
);

CREATE TABLE Players (
  Player_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Name TEXT NULL,
  Number INTEGER UNSIGNED NULL,
  Email TEXT NULL,
  Position TEXT NULL,
  PRIMARY KEY(Player_ID)
);

CREATE TABLE Season (
  Season_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(Season_ID)
);

CREATE TABLE Season_has_games (
  SeasonID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  GameID INTEGER UNSIGNED NOT NULL,
  Games_Game_ID INTEGER UNSIGNED NOT NULL,
  Season_ID INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(SeasonID, GameID),
  INDEX Season_has_games_FKIndex1(Season_ID),
  INDEX Season_has_games_FKIndex2(Games_Game_ID)
);

CREATE TABLE Teams (
  Team_ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Team_Name TEXT NULL,
  PRIMARY KEY(Team_ID)
);

[\code]

Thanks for looking[/code]

[fenway]

It all looks reasonable... it's hard to judge each index without accompanying queries, and I would caution against TEXT column types unless you really need them.

[seveneleven]

Thanks, do you recommend that I use, VARCHAR instead?

[Barand]

Yes. You only need text if the column content is going to exceed 255 chars

[fenway]

Moreover, if you stick to CHAR, you get a fixed-width table, which is faster to search.