Proper table design for proper queries, how to normalize 1

[BigRed1212]

Big post, sorry in advance.

I run a site for a local soccer league. I currently have all the pages which have the game schedule, team standings, and leading scorers, as well as the registered players database driven using ASP and an Access database. My problem is that the schedule table I use is embarrasingly non-normalized (I just use the table structure as a way to line up the elements for display). What I think I want to do is move toward proper tables and proper queries for information. I'm asking this here and not in "general database discussion" because my eye is toward the queries I need to have to drive my site and I think all the query experts are here.

A couple of tables are easy (I think).

tbl_Players (each record is a player):
player_id (autonumber, primarykey)
lname
fname
jersey_number
other basic demographic elements
team_number (from tbl_Teams)
paid_status
[this table loads from a Web form when the players register to play]

tbl_Goals_Scored (each record represents a goal scored):
player_id
game_id (from tbl_Games)
[I'll know Jones scored in the 3:00 game and I'll data enter his/her player id and the game id in a web interface I made]

tbl_Cards_Given (each record represents a card given):
player_id
game_id (from tbl_Games)
card_type
[I'll do this once where card_type is yellow and once where card_type is red]

After that I can think of a few more tables, but I start getting into deep water really quickly when I try to think of how to write the
queries I think I will need to produce the info I need to display.

I think I probably need a team table and a games table and I need some way to assign teams to a game.

tbl_Teams:
team_name
captain_id (player_id from tbl_Players)
team_number

tbl_Games
game_id
game_date
game_time
field_location

tbl_Game_Assignments????
game_id
team_number
(but this will duplicate because every game_id will be in twice - two teams per game- and it will look ultimately look suspiciously like the tbl_Schedule I do now with game_id, team1, team2, game_date, etc that seems nonnormalized with the repeating field).

From these tables I can display a team roster pretty simply:

select fname. lname, jersey_number, etc
from tbl_players
where team_number = whichever one I want at the moment
order by lname

Displaying the listing of goal scorers is

select b.lname, count(a.player_id)
from goals_scored a left join players b on a.player_id = b.player_id
group by lname
order by count (a.player_id) desc
(I will put in a dummy player number in the players table to handle unattributed own goals where a team scores on itself)

Problem is, I don't know how to do the big things. I think I want them to be query results:

1. Schedule. On this day, at this time, these two teams will play on this field. (I do this with the above mentioned horribly nonnormalized table now)
I don't know whether this can or should be done as a query or whether the thing to do is return the whole table(s) and march through it(tem) with some ASP loops somehow someway.

2. Results- The score of each game (team one beat team two x to y)

3. Standings- Team one has x victories, x losses, and x goals for and against.
We would know that for game 3 team green had 4 goals and team red had 3 goals. I would want to be able to calculate that team green won the game and had a record of was 1-0-0 (one win, zero losses, and zero ties) with 4 goals for and 3 goals against. I do the standings now by manually entering the goals for, goals against, and a 1 or zero for win, loss, or tie for each team for each game, and then summing and counting.

Any thoughts on any piece of this welcome. I've cruised the net and lurked around here for awhile but I haven't found the insight I need yet.

 

[fneily]

First start over - delete the command button you have. In design view, create a command button. Do not use the wizard. If the wizard appears, cancel it.
Click on the command button and bring up the property sheet. Click on the Event tab. Click next to On Click in the box. You'll see a square with three buttons on it. This is called the build button. Click it. You'll see a little list. Select Code Builder. This'll throw you into VBA. If you have everything spelled the same way as my code, field names, table names, query name, just copy/paste my code.

You may want to click Tools - References, while in VBA and check the library references. You should have one called Microsoft DAO library version X checked. If not, scroll down and check it. Select the lastest version. Even ESPN doesn't have real time updating of standings.

Close VBA and try it out.

How are you inputted the score of the game? If in a form, then put the code on the AfterUpdate event of the form.
If over the net, it's more complicated. You have to remember, over the net, the web page and your database are not connected directly, It's separated by about 100 other computers and telephone lines and you use a different language.

 

[BigRed1212]

Okay, that ran and made the table. The "check the Microsoft DAO library" was the key. I checked "Microsoft DAO 3.6 Object Library" and it ran.

fneily, your willingness to invest this much time and energy in my project is phenomenal and much appreciated.

"Even ESPN doesn't have real time updating of standings."
Well, what I have done in the past is stored the wins, losses, and game scores directly in a table. I made a little series of pages that do update queries and allow me to enter game scores directly from a browser on whatever machine I am sitting at so that I don't have to work only on the home machine and FTP up the database everytime I get a new score. Works well. The ASP pages on the Web site that display the game score info, the standings, the cards, etc are just queries against the database (SELECT tbl_schedule.teamid AS team, count([teamid]) AS [games played], Sum(w) AS wins, Sum(l) AS losses, Sum(t) AS ties, Sum(gfor) AS gf, Sum(gagin) AS ga, Sum([w]*3)+Sum([t]) AS points
FROM tbl_schedule
WHERE gfor is not null
GROUP BY teamid
ORDER BY Sum([w]*3)+Sum([t]) DESC; if you are interested) so as soon as I make a change to anything with my little web interface, if I reload the Web page in question, it is shown. I don't know if that is what "real time" updating is, but that is more or less what I want.

I think that I can write a query against the new standings table easy enough. The procudure appears to add a new line for each win loss or tie so I think I write a query to sum those up and group by team_name. I suspect with some effort I may be able to modify the procedure so that the standings table is also loaded with the sum of goals for and goals against which I will also sum and I will try to make that modofication. I think I can write the interface to let me add goal scorers to the stats and stat details tables (though I'm a little unclear yet as to how the stat details table is going to sync it's stat_id key to the stats table but I'm hoping that will become apparent as I get into it).

What I don't quite get yet is how I will run this new command button. Do I enter all my data and then via a web interface trigger the command button on the form to run so that my standings table updates and the next time somebody loads the page in a browser it is updated or just what?

This is long and slow, but I feel like we are making progress. I don't understand everything and I have a ton of implementation work to do to move this from concept to production, but this is exciting.

 

[fneily]

Yeah, the standings table just puts the data in the format you want. Now you just do a simple group and sum query. This will give the final table. Then you would use the output of the query to display. Remember, tables and queries are recordsets. So you can build your web page around the query output.

Again, you don't need to have the code on a command button. You can put it anywhere you want. If scores are enter via internet, your code stores the data in a table. The standings, since they depend on a prior query, can be ran in the appropriate order and then structured in your web page.

Noticed you talked about "reloading" the web page. That's not real time. It's just a refresh so we're talking the same thing.

 

[BigRed1212]

I'm still working on this and will post back.

 

[BigRed1212]

Ok. When I do this query:

SELECT tbl_stats.game_id, tbl_teams.team_name, Count(tbl_stat_details.action) AS score
FROM ((tbl_stat_details inner JOIN tbl_stats ON tbl_stat_details.stat_id = tbl_stats.stat_id) INNER JOIN tbl_team_players ON tbl_stats.player_id = tbl_team_players.player_id) INNER JOIN tbl_teams ON tbl_team_players.team_number = tbl_teams.team_number
WHERE (((tbl_stat_details.action)="g"))
GROUP BY tbl_stats.game_id, tbl_teams.team_name;

which is the game_query that serves as the basis for the standings table, I get these results:

game_id team_name score
1 Dortmund 3
1 Portsmouth 2
2 Arsenal 4
2 Cruz Azul 1
3 Hearts of Oak 1
3 Rangers 1
4 Dortmund 2
4 Hearts of Oak 2
5 Cruz Azul 3
5 Portsmouth 2
6 Arsenal 1
7 Dortmund 2
8 Cruz Azul 1
8 Hearts of Oak 1
9 Arsenal 3
9 Portsmouth 2

In my sample data Arsenal beat Rangers 1-0 and so Rangers doesn't show up as having a score for Game 6. Same thing for Rangers 2-0 loss to Cruz Azul (sorry Ranger fans- nothing personal) in Game 7.

The lack of record in this query (a zero score in a game) blows fneily's standings table code up.

I think I need to redo this query so that it picks up games with zero scores but I'm not joining on gameid anywhere and don't see how to do that.

 

[fneily]

This may sound like a stupid question, but why don't you keep a score of 0? 0 is a numeric, it actually means something. Not like null. Most games have two teams, your result shows one team playing a ghost. And how would my code, or anyone else's, know to give the Rangers a loss?? There's nothing to compare to. Sorry doesn't make sense to me.

 

[BigRed1212]

Well, that's the issue. The zero score doesn't show up. When I load the stats table you so nicely made for me (thank you again) with the events (scores, cards) that happened in example Game 6, there are no events or stats for Rangers. They didn't score any goals. So the query that shows all the stats for each team in each game doesn't pick them up. They aren't in the stats or stats_details table to be picked up.

I, however, still need to show that they lost 1 to nothing.

What I was trying to do last night was figure out if I could bring tblGameTeams into the picture on the join somehow so that I could get what was going in stats and stat details for every game- even if what is going on is nothing- but I had no success.

If Rangers have a goal, then they show up and all your code works great. I even modified it to pick up goals for and goals against in the standings table. I just need to be able to handle games with a score of 1-0 or 0-0.

 

[PHV]

I could bring tblGameTeams into the picture
You should with LEFT joins.

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Looking back through the posts, you have tblGameTeams that keeps track of who played and their score. You could dump this that'll produce GameID, Team_Name, score. So on this output you'll have the teams with 0 score.

 

[BigRed1212]

@PHV- I tried that with no luck. At my skill level I can't make a join expression Access likes, although it likely exists. Even if I bring in the fact that Rangers have a role in Game 6, there is nothing in the stats and stat details table for Rangers for Game 6 and I don't know how to write the instruction "if you don't find anything for that team for that game then put down a zero" in SQL. Inuitively I want to left join tblGameTeams to whatever so that all the game_id's in GameTeams get whatever data is available for them, but like I say, it appears to be a operator issue.

@fneily- I wasn't actually populating the score field in tblGameTeams following the advice of not storing summary things like total scores and generating those on the fly from queries.

To populate that field I would have to enter the game score in there, and then enter the individual goals and action in the stats and stats_details tables, where the idea was to enter the stats only once and construct the rest. Maybe I could update it based on the stats and stats_details entries but that sounds really nasty. Besides, the tblGameTeams isn't an input into your wonderful form button code and I'm not sure I would know how to make it into an input.

I wonder if something as simple as

'insert list of games into table TempTable
db.Execute ("insert into TempTable ([game_id]) select distinct [game_id] from [tbl_game_teams]")

would work? I'll check when I get home.

This has got to be a doable thing- people keep stats on sports stuff and they can't all do it in the butchered fashion I have done it. It has to be possible to do it correctly, right?

 

[PHV]

Have a look at the Nz function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

First, let me congratulate you on trying to keep to the rules of normalization. Most people wouldn't recognize that the score is a total of the goals and would want to store it.
Take the query that produces:
game_id team_name score
1 Dortmund 3
1 Portsmouth 2
2 Arsenal 4
2 Cruz Azul 1
3 Hearts of Oak 1
3 Rangers 1
4 Dortmund 2
4 Hearts of Oak 2

First create a query connecting tblGameTeams to tblTeams to pick up the name. Output Game_ID and Team_Name.
It should look like:

1 Dortmund
1 Portsmouth
2 Arsenal
2 Cruz Azul
3 Hearts of Oak
3 Rangers
4 Dortmund
etc.

Create a query connecting the above query to your other query. You'll manually connect Game_ID to Game_ID AND Team_Name to Team_Name. You'll select from the first query input Game_ID, Team_Name. Then select Score from your main query output. You'll right click the connecting lines, one at a time, and select Option 2. This'll create a left join. The sql will look like (NOTE DO NOT PAY ATTENTION TO MY NAMES)

SELECT game_Detail.Game_ID, game_Detail.Team_Number, game_table.Score
FROM game_Detail LEFT JOIN game_table ON (game_Detail.Game_ID = game_table.game_id) AND (game_Detail.Team_Number = game_table.Team_name);

This'll put the Rangers into the list. Then try the standings code.

 

[BigRed1212]

Ok. We are definitely closing in on it now. Fneily, I made your new table to feed the standings code, but it left nulls where there weren't any scores from the stats tables. PHV's suggestion of nz(score,0) as newscore fixed that [thanks!!]. But then the new query gave a zero score to all games it couldn't find anything for- which means games not yet played yet - which would not work.

So I took the score field I wasn't populating on the tblGameTeams table and turned it into a played field. 1= game has been played, 0 = game has not been played. The query to feed the standings code then was changed to only select the games which had been played. This results in a correct standings table which can then be queried and summed for presentation.

Upshot: I think I have function players table that I will load through a registration application via web form. I'll use that to generate team rosters [iif(tbl_team_captains.player_id = tbl_players.player_id, "Captain")], track paid status (the players have to pay a fee to cover refs and shirts but we are a nonprofit organization and this is volunteeer work for fun) and whatever else.

I think I have a functional query that will generate the goal scorers from the players and the stats, as well as a query for red cards, yellow cards.

I think I have a functional query that will generate the game schedule and populate the scores for the games that have been played.

I think I have a query that generate the standings table.

Now I will start to work on writing some ASP to display all these lovely results and then some ASP to create the backend Web app to load the scorers, cards collected, and games played, etc into the database. I still have some questions- I have code stuck on a button that I need to run to update things and I need to figure out how to do that through a web interface, but I think I will ask those in other threads. This one is way too long already.

I AM ON THE WAY!!!

A HUGE public thank you to fneily. Fneily, you think I ought to write all this up as an article or FAQ or something? "How to Set Up a Database to Track Sports League Information," meaningful content by fneily with a few unimportant connecting words by BigRed.

 

[fneily]

See that. When you're not the primary developer, like I, then I forgot you populated the game table with future matches. I was just thinking you populated on the fly, which, of course, is dumb. Oh well.
Nice creative idea to use the score field for played on not played. Good call.
After you get it working on the web, post the URL so we can enjoy YOUR work.

 

[BigRed1212]

After you get it working on the web, post the URL so we can enjoy YOUR work.

http://www.smslonline.com

Standings, schedule, and rosters query generated based on fneily's table structure and guidance.

 

[lespaul]

wow! the site looks awesome, Great Job!

Leslie

Come join me at New Mexico Linux Fest!