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.

 

[r937]

your Schedule/Games table is fine as just one table

it should have game_id, game_datetime (not separate date and time columns, please), field_location, and hometeam and awayteam as two foreign keys to the Teams table

the score goes in there as well -- homescore, awayscore

i would personally not bother storing results and standings, just calculate them on the fly



r937.com | rudy.ca

 

[fneily]

You have two main tables:
tblPlayers
Player_ID Primary Key
fname
lname
jersey_number
Paid_status

tblTeams
Team_number Primary Key
Teman_name

Now you need to associate players with teams:
tblTeamPlayers
TPID Primary Key
Team_Number Foreign Key
Player_ID Foreign Key

Now you need team captains:
tblTeamCapts
TCID Primary Key
Team_Number Foreign Key
Player_ID Foreign Key

You could argue to have a "Captain" indicator in tblTeamPlayers, but then for 11 people, one would be checked and 10 blank. Bad normalization design.

You need game assignments:
tblGame
Game_ID Primary Key
Game_DateTime
Location

Two teams play in a game. So the above table will be in a one-to-many with:
tblGameTeams
GTID Primary Key
Game_ID Foreign Key
Team_Number Foreign Key
Score
Home_Away_Indicator

You need to keep stats of players.
tblStats
StatID Primary Key
Game_ID Foreign Key
Player_ID Foreign Key

A player may have many stats for one game so in a one-to-many relationship with the above table:
tblStatDetails
SDID Primary Key
StatID Foreign Key
Action (G=goal,A=Assist,YC=Yellow card, RC=Red Card,E=ejection)

Now with queries, everything you want just drops out.

 

[BigRed1212]

Wow, Fneily, I think this is what I want.

It will take me the weekend to work through your post and understand what you have written, what I need to do, and how to make it work. (I'm slow).

I will post back with success or failure. Thanks again.

 

[BigRed1212]

game_datetime (not separate date and time columns, please)

Which will require a better facility at working with datetime fields in Access and ASP than I currently have. This is something I need to acquire anyway so this will be a learning opportunity as they say.

i would personally not bother storing results and standings, just calculate them on the fly

That is my thinking, too. I don't want to have stuff like standing in tabular format. I think they ought to be queries.

Going to work on this some more now. Thanks.

 

[r937]

fneily, i appreciate that you have split off the two teams (hometeam and awayteam) from the Games table into the GameTeams table, and that some might regard this as "proper" normalization, but the query to determine who the winner is is much more complex, and for what benefit?

r937.com | rudy.ca

 

[fneily]

He can play with
faq701-4233
to get a listing of who won and create standings.

"and for what benefit?" -- sigh.

Access is a relational database where a table's structure follows rules. Dr. Ted Codd developed these rules and call them normalization. Unless someone can show me anywhere in his writings that a designer can arbitrarily dismiss a rule, then I will no longer try and be a purist.

A major benefit will be the knowledge BigRed1212 will gain in playing with the code. Unless gaining knowledge is just a waste of time.

This reminds me of when Sonny Bono, of Sonny and Cher fame, was a member of Congress. He was asked to give a speech on his opinion of illegal immigration. He walked to the podium and said "It's illegal." Then sat down.

 

[r937]

Unless someone can show me anywhere in his writings that a designer can arbitrarily dismiss a rule, then I will no longer try and be a purist."

i think what you actually meant to say was "If someone can show me..."



r937.com | rudy.ca

 

[r937]

p.s. bigred, you should do it fneily's way

first



r937.com | rudy.ca

 

[fneily]

r937 - now there is more then one way to normalize. So if there's another way which sticks to the rules, then I can't debate it. Also, you'll notice that I didn't say your way would not be easier and, since there will probably never be more than two teams in a game, would not create any future problems. But if I agreed with you then there'd be no discussion.

 

[BigRed1212]

I'm back.

I made a database in the recommended structure with some fake data and tried to write some of the queries I need.

With this data:
tbl_players
[tt]player_id fname lname
1 alfred alpha
2 benjamin bravo
3 charles charlie
4 david delta
5 edward echo
6 fredrick foxtrot
7 george golf
8 harry hotel
9 issac india[/tt]
etc

and these stats
tbl_stats
stat_id game_id player_id
1 1 1
2 1 1
3 1 2
4 1 5
5 1 6
6 1 7
7 2 9
8 2 9
9 2 9
10 2 12
11 2 11
12 2 14
13 3 20
14 3 23

tbl_stat_details
sd_id stat_id action
1 1 g
2 2 g
3 3 g
4 4 g
5 5 g
6 6 y
7 7 g
8 8 g
9 9 g
10 10 g
11 11 r
12 12 g
13 13 g
14 14 g


I can list the goal scorers:

SELECT tbl_players.lname AS last_name, count(action) AS goals_scored
FROM tbl_players INNER JOIN (tbl_stats INNER JOIN tbl_stat_details ON tbl_stats.stat_id=tbl_stat_details.stat_id) ON tbl_players.player_id=tbl_stats.player_id
WHERE action="g"
GROUP BY lname
ORDER BY count(action) DESC;

which will nicely produce
last_name goals_scored
india 3
alpha 2
whiskey 1
tango 1
november 1
lima 1
foxtrot 1
echo 1
bravo 1

I can produce part of the schedule with:

SELECT a.gameday, a.gametime, a.game_location, b.team_name, a.home_away
FROM [SELECT format(game_datetime, "mmm d") as gameday, format(game_datetime, "h am/pm") as gametime, game_location, team_number, home_away
FROM tbl_games left join tbl_game_teams on tbl_games.game_id = tbl_game_teams.game_id]. AS a LEFT JOIN tbl_teams AS b ON a.team_number = b.team_number
ORDER BY a.gameday, a.gametime, a.home_away DESC;

which makes
gameday gametimegame_location team_name home_away
Mar 10 12 pm field 6 Dortmund h
Mar 10 12 pm field 6 Portsmouth a
Mar 10 2 pm field 6 Arsenal h
Mar 10 2 pm field 6 Cruz Azul a
Mar 10 4 pm field 7 Rangers h
Mar 10 4 pm field 7 Hearts of Oak a
Mar 17 12 pm field 7 Hearts of Oak h
Mar 17 12 pm feild 7 Dortmund a
etc

I don't understand that query as I don't know what the . after the last bracket does but Access blows up without it.

I can make a list of game scores:
SELECT game_id, team_name, count(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 action="g"
GROUP BY game_id, team_name;

which looks like
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

(I'd really like to have the game scores and the schedule somehow combined so that I can show the scores on the schedule for those games that have already been played.)

After staring at I realize I have absolutely no idea how to even attack the standings query. What I did before was to count manually entered 1's and 0's to determine the win-loss record. I don't know how to calculate even one win under this structure, much less the total of wins and losses.

Hints? Ideas? Help?

 

[fneily]

I'll look at the standings part later. But quickly:
If you look at:
FAQ701-4233: How to concatenate multiple child records into a single value

it'll show you how to make:
1 Dortmund 3
1 Portsmouth 2

look like:
1 Dortmund 3 Portsmouth 2

Same for:
Mar 10 12 pm field 6 Dortmund h
Mar 10 12 pm field 6 Portsmouth a

Look like:
Mar 10 12pm Field 6 Dortmund h Portsmouth a

If you include the Date with the scores, then you can also relate the game schedule with the score.

 

[r937]

hometeam and awayteam in the same row starting to look a bit more attractive yet, bigred?

;-)

r937.com | rudy.ca

 

[fneily]

Now, now r937. I already said you're way would be easier...though not correct.

 

[BigRed1212]

I don't mind info in two lines in the recordset like

gameday gametimegame_location team_name home_away
Mar 10 12 pm field 6 Dortmund h
Mar 10 12 pm field 6 Portsmouth a
Mar 10 2 pm field 6 Arsenal h
Mar 10 2 pm field 6 Cruz Azul a

because I can just ignore the extra information and write out what I want on my page easily enough as:

no = 1 

If rs.Eof Then 
response.write "<div align='center'>Sorry there are no current records.</div>" 
Else 

response.write "<table cellspacing=0 cellpadding=3 border=0><tr class=tableheader>"
Response.Write "<th align=left valign=bottom width=50>Date</th><th align=left valign=bottom width=50>Time</th><th align=left valign=bottom width=50>Field</th><th align=left valign=bottom colspan=2>Home<br>Visitor</th><th valign=bottom>&nbsp;</th></tr>"

Do while not rs.Eof 

if no mod 2 > 0 then
response.write "<tr><td valign=top>" & rs("date") & "</td>"
response.write "<td valign=top>" & rs("time") & "</td>" 
response.write "<td valign=top>" & rs("field") &  "</td>"
response.write "<td colspan=2 valign=top>" & rs("teamname") & "</td>"
response.write "<td valign=top>" & rs("I would write the score here if I knew it") &  "</td></tr>"
else
response.write "<tr><td valign=top>&nbsp;</td>"
response.write "<td valign=top>&nbsp;</td>" 
response.write "<td valign=top>&nbsp;</td>"
response.write "<td colspan=2 valign=top>" & rs("teamname") & "</td>"
response.write "<td valign=top>" & rs("I would write the score here if I knew it") &  "</td></tr>"
end if

no = no + 1
rs.movenext

Loop

I'll have to work some more on adding the date in with the scores somehow to see if I can make those two come together.

 

[fneily]

Standings.
I used the output of game scores query:
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

I called your query game_query for the following.
First create two tables -
TempTable that has one field, Game_ID and make it a primary key.
Standings that has four fields, Team (Text) and WonField(Numeric), LossField(Numeric) and TiedField(Numeric). NO primary key.

Create a form with a command button. On the OnClick event putcopy/paste)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim vScores As String

Set db = CurrentDb

'delete Standings info, then open it for new data
db.Execute "DELETE Standings.* FROM Standings;"
Set rs2 = db.OpenRecordset("Standings", dbOpenDynaset)

'clear out TempTable
db.Execute "DELETE TempTable.* FROM TempTable;"

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

Set rs = db.OpenRecordset("select distinct [game_id] from TempTable")
'outer loop containing list of games

Do Until rs.EOF = True
Dim sql As String
sql = "select [team_name], score from [game_query] where [game_id] = " & rs![game_id]
'select team_names and scores for current game
Set rs1 = db.OpenRecordset(sql)

Do While rs1.EOF = False
holdteam = rs1![team_name]
holdscore = rs1![score]
rs1.MoveNext
If holdscore > rs1![score] Then
rs2.AddNew
rs2![team] = holdteam
rs2![WonField] = 1
rs2![LossField] = 0
rs2![TiedField] = 0
rs2.Update
rs2.AddNew
rs2![team] = rs1![team_name]
rs2![WonField] = 0
rs2![LossField] = 1
rs2![TiedField] = 0
rs2.Update
End If
If holdscore < rs1![score] Then
rs2.AddNew
rs2![team] = rs1![team_name]
rs2![WonField] = 1
rs2![LossField] = 0
rs2![TiedField] = 0
rs2.Update
rs2.AddNew
rs2![team] = holdteam
rs2![WonField] = 0
rs2![LossField] = 1
rs2![TiedField] = 0
rs2.Update
End If
If holdscore = rs1![score] Then
rs2.AddNew
rs2![team] = rs1![team_name]
rs2![WonField] = 0
rs2![LossField] = 0
rs2![TiedField] = 1
rs2.Update
rs2.AddNew
rs2![team] = holdteam
rs2![WonField] = 0
rs2![LossField] = 0
rs2![TiedField] = 1
rs2.Update
End If
rs1.MoveNext
Loop
'move to next game
rs.MoveNext
Loop

'release the memory
Set rs = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

If you look at the table Standings, all you have to do is a regular query, grouping on team and summing the other fields. Sort it and there's your standings.

 

[BigRed1212]

Oh my....

Let me try that when I get home. Will this allow standings that will update automatically when I enter game information into my Web interface or will I need to run something (a command button? ) in the database itself to get it to update?

Happy US Turkey Day to all.

 

[fneily]

All data is stored in tables. After the data comes in from the web interface and is stored, you'll rerun the query and refresh the web page. Remember, web pages and Access forms,queries, reports, have a nothing to do with each other. Different environments. Web pages operate in the Internet Explorer environment.

 

[fneily]

Forgot.
Standings is a table. So after you rerun the query, it'll update the table. Then you just display the new data.

 

[BigRed1212]

Sorry, got busy with family and the holiday. Back working on this now.

I have no idea at all how to do this.

I have gone into Access and created the two tables TempTable and Standings. I changed the name of my game score query.

I made a form and put a command button on it. I don't know how to put stuff on it correctly I don't guess.

I made the "On Click" line under the "Event" tab say [Event Procedure] and I copy and pasted the code into a box that I don't know what is but seems to run when I click the button. It starts with "Private Sub Command1_Click()" and then has the code. When I click on the button in the form, Access brings up this window with the code and pops up a little box that says Microsoft Visual Basic across the top bar and inside says "Compile error: user-defined type not defined" stopping at the "Dim db As DAO.Database" statement. If I click back to the form screen it is frozen and I have to quit Access to get to the form again.

Nothing loads to the temp tables.

I guess I don't understand what is going on. All of the "Do Until rs.EOF = True, Dim sql As String, etc." stuff looks like something I would put in an ASP page.

I don't really want to have to come into my database and hit a form button everytime I need the thing to update. When I enter a game score I want that reflected in the query results when they are refreshed.

I hate to keep asking, but....help?