[SOLVED] Prep School League Database

[86Stang]

Hi All,

 

I've been tasked with building a prep school league database that shows team standings, schedules, scoreboards and results.  Now I know I can wade my way through the inserts, updates, etc. but I am a complete noob when it comes to designing the tables that I would need.    My thinking (and I'm sure I'm way off) is that I need a Teams table, a Games table and a Results table.  Or should I wrap results into each game on the Games table? 

 

Man, I just can't get my head around this stuff!!  Any help from you MySQL jedi's would be most appreciated.

[im8kers]

I just gave this link to someone else.

http://www.asp.net/learn/sql-videos/video-106.aspx

 

This is a video tutorial on database normalization and keys.  Yeah, it's MS SQL, but the concept is the same accross most all databases.

 

A good way to approach this is writing down all of the data you want to collect and then normalize (basically start breaking down the tables to eliminate redundant info then linking together by primary keys).

 

[Barand]

I generally start with the outputs required and work backwards.

 

List samples of all the output and identify the items of data that need to be stored. Then look at relationships between the data and decide which tables are needed and which items belong in each table (normalize).

 

Then look at how data can best be input to populate those tables.

 

I'll also look at other potentially useful outputs that could be provided with little or no extra data/effort and iterate round the process

[86Stang]

Thanks for the pointers guys!!  I've attached my first attempt at this and am really hoping for some feedback.  I am thinking that I need two foreign-keys for each game and stats because each of those have two teams associated with them.  Am I on the right track with that thinking or am I way off?  If I'm way off please feel free to enlighten me and don't hold back - I'm a big boy and can take the criticism - I just want to get better at this stuff.

 

Basically, how the whole setup should work is we first plug in the games of the season along with which two teams are playing.  Then, as the games are played, we would update the games stats.

 

[attachment deleted by admin]

[Barand]

The game tells you the teams so no need to repeat the teams in stats table. Also I'd remove the repetitions from the stats table giving

[pre]

League            Team            Game                Stats

--------          ---------        -------------      -----------

league_id(PK)--+  team_id(PK)-+    game_id(PK) ---+    stat_id

league_name    |  team_name  |    game_date      +--  game_id(FK)

              +-- league_id  |    game_time          quarter

                              |    game_class          home_pts

                              +--  home_team_id(FK)    away_pts

                              +--  away_team_id(FK)

[86Stang]

Thanks so much for the help Barand!

 

What about the 1:1 , 1:many , many:many relations between these?  Am I correct in assuming that if there is a many:many relation I would need an "inbetween" table?  Anyway, here's my thinking on what these need to be:

 

League to Team: 1:many

Team to Game: 1:many

Game to Stats: 1:many

 

Right?

[Barand]

Agreed.

 

The next step - from that design, can you you get the outputs you need? Such as as league table

[pre]

Position  Team            P  W  D  L  Pts

---------+----------------+--+--+--+--+----+

  1      Team C          5  4  1  0  13

  2      Team A          5  3  1  1  10

  3      Team B          5  2  2  1    8

[/pre]

 

 

[86Stang]

It would certainly appear so.  I'm going to go put the screws to it and see how much of a mess I can make.  Wish me luck!!

 

Thanks again for all the help Sensei.