Soccer Team Database

[Juan]

Hi everyone.
I'm very new to these of Access World. I'm planning to make a database
to keep track of the historic results os my home soccer team, just
storing info about games played, players, seasons schedule,
trainers...
By now, I'm just trying to make simple games tracking as the first
goal of the project. To these, I've made 2 tables: tblGames and
tblPlayers. In the first one I plan to store any info of a particular
game:
- Date
- Competition
- Home Team
- Away Team
- Home Team score
- Away team score
- Local Lineup (11 starters + 3 reserves)
- Away Lineup (11 starters + 3 reserves)
- Goal (this is for any of the 28 players)
- Yellow card (this is for any of the 28 players)
- Red card (this is for any of the 28 players)
- Minutes played (this is for any of the 28 players)

As the result, this is a 148 field table. I also want to store the
info in the players table, I guess that should be made with direct
relation between tblGames and tblPlayers by IdPlayers. Also, I do want
that Local lineup and away lineup is in a droplist taking values from
the tblPlayers.
And at least, I do want to store these info trough a form.
Any suggestion would be highly appreciated.

 

[Douglas J. Steele]

STOP RIGHT NOW! Any time you have 148 fields in a table, you've likely got
at least 125 more than you should.

Before you go any further, you need to learn about database normalization.
(Jeff Conrad has lots of good references to the topic at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101 )

I would think that you'd need a minimum of 7 tables, but I've probably
missed some because I haven't thought this out in as much detail as I should
(specifically, I'm not sure whether you need to link the players to the
teams and to the matches, or only to the matches)
- one for Teams (1 row for each team)
- one for Matches (to provide a mechanism to link to two different teams in
the Team table and show the date of the match)
- one for Players (1 row for each player)
- one to handle the linkage between Teams and Players (since players can and
will change teams)
- one for Points (you should be able to combine goals and assists into a
single table), linked to both the Players and Matches table
- one for Infractions (you should be able to combine red and yellow cards
into a single table), again linked to both the Players and Matches table
- one for time played (linking the Players and Matches tables yet again)

 

[Juan]

Thanks Doug for your quick answer.
Well, that's getting complex, hahaha!!.
I've read about normalization, it's the first time I've heard about
it.
Now the model is so:
http://s3.subirimagenes.com/otros/2528267aaa.jpg
Where:
"Eq" is the table for teams
"Jug" is the table for players
"PartJug" is the table for facts of player in one particular game
"Part" is the table for games
"Compet" is the table for competition
"Pos" is the table for position
I guess the model is now clear for me, and normalizated trough the
recommendations given. So if it is right, now comes the second part.
How can I can store these info trough a form?
I've tried to made it, but couldn't. This might be a very newbie
question, as I said...I am.

Thanks in advance for your help.

 

[Douglas J. Steele]

Typically you'll use a combination of forms and subforms.

You'd have a form that's bound to a query based on the Part table. That form
would have a couple of combo boxes on it, with Eq as the RowSource for both,
so that you can assign the Home and Away teams. You'd then probably have a
couple of subforms, each bound to PartJug, to allow you to specify which
players were on which team for the match. Again, those subforms would have
combo boxes with Jug as the RowSource to let you choose players.