Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Football League Database


  • Please log in to reply

#1
adamdillon

adamdillon

    Member

  • Member
  • PipPip
  • 67 posts
I have been debating on how to set up the database. Here is what i have so far

Team - team_id(pk), team_name, team_logo(this would be a path to an image), year formed, years in league, active(Boolean),
Player - player_id(pk), team_id(fk), player_name, p_height, p_weight, year_league,
positon - position_id(pk), player_id, positon_name (did this bc there could be more than one position)



Now, where I'm struggling is doing the scores. I want this to be legit. I want to do weekly scores and total scores.

I have though of several ways.

week 1 - team_id, team_1_score, team_id, team_2_score I didn't really like this way.


team 1 scores, team_id, week_21_2011, week_02_2011,....,week_10_2001 (same for every team.)


I want to be able to go back to previous seasons and add in scores for other teams in past years (why I put 2011) I will need to be able to make bye weeks for team and, also, some teams will make it to play offs and championship game.

If someone can just point me in the right direction, that would be great.

If you want to look at all the numbers, the website is www.mtflonline.com

thanks in advance,
Adam
  • 0

Advertisements


#2
Raikia

Raikia

    Member

  • Member
  • PipPip
  • 17 posts
Firstly, the database schema looks good so far except for one thing:

positon - position_id(pk), player_id, positon_name (did this bc there could be more than one position)


Be careful of making position_id the primary key. I assume you will have one quarterback for each team, so if you are storing all the positions for each team in one table (which is a totally acceptable way of doing it), your "position_id" will no longer be unique (assuming your storage will be, like "id of 1 for quarterback, id of 2, 3, 4 for receivers, etc").

My suggestion of doing it (and utilizing the full potential of a relational database) is:

Positions - position_id (pk), position_name

PlayerPositions - position_id, player_id (both pks) (you can make two attributes primary keys by: CREATE TABLE PlayerPositions ( position_id int, player_id int, PRIMARY KEY(position_id, player_id));


This keeps positions constant (and centralizes all the names so it doesn't have redundant data), and then maps each player to each position.


Now onto the scoring:

Could you expand more on what specifically you are trying to store? As well as what you mean about "weekly" and "total" scores? Then I can help you further in mapping the schema for your database.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP