Jump to content


Photo

Normalized DB design question


  • Please log in to reply
3 replies to this topic

#1 KevinM1

KevinM1
  • Moderators
  • Snarkimus Prime
  • 5,248 posts

Posted 26 September 2006 - 10:17 PM

I'm in the process of creating a hockey site for my fantasy hockey team.  I've broken down the roster into three tables -- skaters (wingers, centers, defensemen), goalies (self-explanatory), and positions (a go-between table that I created during the normalization process).  The relationship(s) are 1:M:1, as there are two goalies, a plethora of other players at other positions (multiple wingers, for example), but each skater or goalie only plays one position, and each has a unique jersey number.  I believe I've created the tables correctly, but I'll put my structure below in case anyone can see anything catastrophic in my design:

Position table:
CREATE TABLE positions (
pos_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
goalie_id TINYINT UNSIGNED NOT NULL REFERENCES goalies(goalie_id),
skater_id TINYINT UNSIGNED NOT NULL REFERENCES skaters(skater_id),
position VARCHAR(2) NOT NULL,
jersey_num TINYINT UNSIGNED NOT NULL,
PRIMARY KEY(pos_id));

Skaters table:
CREATE TABLE skaters (
skater_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(40) NOT NULL,
goals TINYINT UNSIGNED NOT NULL,
assists TINYINT UNSIGNED NOT NULL,
points TINYINT UNSIGNED NOT NULL,
pim SMALLINT UNSIGNED NOT NULL,
plus_minus TINYINT(4) NOT NULL,
attributes TINYTEXT NOT NULL,
biography TEXT NOT NULL,
oldstats TEXT NOT NULL,
PRIMARY KEY(skater_id));

Goalies table:
CREATE TABLE goalies (
goalie_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(25) NOT NULL,
last_name VARCHAR(40) NOT NULL,
gaa FLOAT UNSIGNED NOT NULL,
save_percent FLOAT UNSIGNED NOT NULL,
shots_faced SMALLINT UNSIGNED NOT NULL,
goals_allowed TINYINT UNSIGNED NOT NULL,
saves SMALLINT UNSIGNED NOT NULL,
shutouts TINYINT UNSIGNED NOT NULL,
games_played TINYINT UNSIGNED NOT NULL,
wins TINYINT UNSIGNED NOT NULL,
losses TINYINT UNSIGNED NOT NULL,
ties TINYINT UNSIGNED NOT NULL,
minutes MEDIUMINT UNSIGNED NOT NULL,
attributes TINYTEXT NOT NULL,
biography TEXT NOT NULL,
oldstats TEXT NOT NULL,
PRIMARY KEY(goalie_id));

My main concern is with the players' oldstats columns.  It's typical for hockey sites to list a player's career stats in a table.  My method of doing the same thing has been to code the table (with stats) right into the database, but that seems to be pretty archaic.  I can't think of a way to separate it, though, without creating more tables, which I really don't want to do.  From a design point of view, are there any options I haven't thought of that are relatively efficient, short of creating new tables?  Is this design adequate?  Can it be considered normalized?

Thanks :)
Using 'global' is a sign of doing it wrong

#2 fenway

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

Posted 26 September 2006 - 10:32 PM

Well, I'm not sure I understand the FKs in the positions table... but as far as oldstats, you should simply have multiple records in each table, and add a link to the team/year.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 KevinM1

KevinM1
  • Moderators
  • Snarkimus Prime
  • 5,248 posts

Posted 26 September 2006 - 11:16 PM

Yeah, I'm not exactly sure about the foreign keys myself.  I was using Larry Ullman's old Peachpit PHP/MySQL book, but his section on database normalization was a bit vague.  How should I be using them?

I'm not quite sure what you mean about the oldstats column.  A sample record would look like (stats not accurate):

SELECT * FROM Skaters WHERE first_name = 'Patrice' AND last_name = 'Bergeron';

skater_id | first_name | last_name | goals | assists | points | pim | plus_minus | attributes | biography | oldstats
1           Patrice      Bergeron    32      73        105       24   3            Height:...   Born in...  Table with stats from 2003 onward





(go Bruins!)

Right now, I have multiple records in each of the players' tables (skaters and goalies), but they're for one and only one player each.  How would what you say change that column without adding redundant entries?
Using 'global' is a sign of doing it wrong

#4 fenway

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

Posted 27 September 2006 - 06:33 PM

Oh, I see now... the positions table will have either the goalie_id OR the player_id, and the other one will be null.

I was under the impression that the old stats column contains aggregate, formatted data for all of the columns in this table.
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