Jump to content

Archived

This topic is now archived and is closed to further replies.

seveneleven

Hockey Team DB

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Yes. You only need text if the column content is going to exceed 255 chars

Share this post


Link to post
Share on other sites
Moreover, if you stick to CHAR, you get a fixed-width table, which is faster to search.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.