Jump to content


Photo

Hockey Team DB


  • Please log in to reply
4 replies to this topic

#1 seveneleven

seveneleven
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 25 October 2006 - 10:51 PM

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.

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


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 October 2006 - 05:35 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 seveneleven

seveneleven
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 26 October 2006 - 06:17 PM

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

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 26 October 2006 - 11:01 PM

Yes. You only need text if the column content is going to exceed 255 chars
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 October 2006 - 03:51 PM

Moreover, if you stick to CHAR, you get a fixed-width table, which is faster to search.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users