Help with hockey team history database

[Hockeyguy1924]

hi all! I am very new to database. I am trying to set up a database of one hockey teams history. I am using access 2010 to creat tables etc. but I am getting lost very quick. I also want this database to be installed to my website so visitors can pull info from it, such as all time numbers worn by players (multiple players,multiple numbers) all time record vs other teams from league, all time players with stats by year. I want them to be able to find a this for example: all defense that worn number 10 or all plyers to wear number 10 in 1997.

Currently I have the following tables in data base. what am I Missing, what could be done better> be nice

Tables :
Players[PLayerID, first Name, last name]
Coaches[coachID, first,last]
Jerseys [playerID, seasonID,Jersey]
Seasons [seasonID seasonType(1 for reg.season,2 for playoffs), Season (by year)]


Opponents [teamID,teamCity,teamName]
StatsPlayer[PlayerID,seasonID,seasonTypeGP,G,A,P,PIM]
StatsPlayerPost[same as above except playoffs]
StatsGoalie(same but diff stats)
StatsGoaliePost(same)
CoachesRecord(coachID,W,L,T,etc)

am I heading in the right direction>

Thanks for any help

[June7]

Could combine StatsPlayer with StatsPlayerPost (and same for Goalie tables) into one table. The Seasons table has field to indicate if Reg or Post. Since the SeasonID is in Stats tables, the Reg/Post value can always be retrieved by joining tables in query.

[Hockeyguy1924]

Thanks June. Here is what I have for tables now
Tables :
Players[PLayerID, first Name, last name]
Coaches[coachID, first,last]
Jerseys [playerID, seasonID,Jersey]
Seasons [seasonID seasonType(1 for reg.season,2 for playoffs), Season (by year)]
Opponents [teamID,teamCity,teamName]
Games [seasonID,dateID,teamIDAway,teamIDHome,goalsAway,go alsHome,W(true/false),L (t/F) T(t/f), OT (t/f) SO (t/f)]
StatsPlayer[PlayerID,seasonID,seasonTypeGP,G,A,P,PIM]
StatsGoalie(same but diff stats)
CoachesRecord(coachID,W,L,T,etc)

The one I am concerned about is the Games. When I get this to my site I will have a page where users will be able to search the teams all time games played. Searchable by season, all time vs another team,by home games or away. Is that set up correctly?

[June7]

To search by Season will probably need query that joins Games to Seasons, otherwise the Season and SeasonType fields will not be available, only the SeasonID is on Games table. Users should not see the ID fields, the value means nothing to them. They pick Reg/Post and Season year but the search will use the SeasonID.

[Hockeyguy1924]

June can you please have a look at database. I have enclosed a link for your review. My head is spinning. Getting confused with primary and foreign keys....can you set me straight on this?
https://dl.dropbox.com/u/61582742/Database3.accdb

[Hockeyguy1924]

Can Anyone have a look at this?

[June7]

One issue with Relationships is the double link from Games table Away and Home fields to Opponents table. Need to bring the Opponents table into the Relationships builder twice. One for each join to the Away and Home fields.

Another thing to consider is combining the Player and Goalies tables (goalies play too). Then the statsGoalie and statsPlayer tables would each join to the PlayerID.

Not sure Jerseys table is necessay. Maybe this could just be a field in each of the stats tables especially since stats are not kept at the game level. On the other hand it will probably be just fine.

Instead of two yes/no fields for Reg and Post in Seasons table, have a single text field with two possible values (R, P).

For Results table, I would not use resultID. The result descriptions are so short I would just save them to the Games table.

Also recommend avoid special characters in field names or in values, such as %. SAV% would be better as SAVPCT

[Hockeyguy1924]

Hi June,
Thanks for reply. I set up jersey table bc players wore different jerseys in the same year. Also the reason was bc I was going to set up a different page on site for jerseys. Would this still require a jerseys table or just be added in stats as you suggested

[Hockeyguy1924]

How do I go about setting up two values for one field? Would I set it up as seasontypeID table?

[June7]

If they could have more than one jersey in a season then the Jerseys table is justified.

Could have a table for just two value choices but not required. Use a combo or list box (a radio button option group is also possible but more complicated) with properties:
RowSource: R;P
RowSourceType: ValueList

[Hockeyguy1924]

thank you again Keep checking back for more questions I set up the results key becauseI wanted useers to be able to search by wins losses etc....still justified?

[June7]

My comment about Results table basically same. The descriptive Result field has just as much info to base search/sort for wins/losses/ties as the resultID field. I am guessing that OT stands for overtime but what does SO stand for? These make more sense to me:

Loss
Loss OT
Loss SO
Win
Win OT
Win SO
Tie

[Hockeyguy1924]

Loss in overtime (OT) loss in Shoot out (so) etc. Ive actually scrapped results table and decides to put type in myself or again...make a drop down box as I did with reg/post. I am still having issues getting the home/away relation to work properly. you can download to have a look if you can from the same link above. thank you again for your guidance and helping me understand this as much as possible. I want the user to be able to seach by opponent ..."x amount of wins" vs team a or team b ....

EDIT: I got the teams table figured out. I simply took the two fields away that said home and away and made one column with drop down box. 2 values

[Hockeyguy1924]

oh and the tie was from earlier rules in the leagues history...they didnt have shoot out...just settled for tie game

[June7]

So in the Games table each game will have two records? What you had before seemed fine to me. What was the difficulty you encountered?