Jump to content

Normalized DB design question


KevinM1

Recommended Posts

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:

[code]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));[/code]

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 :)
Link to comment
Share on other sites

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 [b]should[/b] 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):

[code]
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!)
[/code]

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?
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.