Beginner: Design of Football (soccer) team Fantasy setup. Players, Teams, Matches -- Statistics!

[redhoax]

Hello there :-)



I'm new to this forum, and very new to Access. I just read up on some of the related threads here (keyword: fantasy, league), and read the RDBMS Principles article by D.Ravey. So, I'm quite new to Access, and have no previous programming knowledge. Anyway;

I have an Excel spreadsheet covering player info, team info, player statistics, etc. I now want to create an access database to replace that spreadsheet. So the scope of my project is a database that contains information on 1 team, its players and match results. This is what I've come up with so far:

Scope: Players, fixtures and team information and statistics for 1 team

Entities: Team, Player, Fixture (match), Position, Point Triggers..

Now, PointTriggers would be the parameters Goal, Assist, Own goal, Penalty miss, Penalty save, Clean sheet, etc... standard Fantasy stuff. I need a table where I can store the score rewarded by each trigger. But this is where I'm kind of confused. Shouldn't the object "Goal" have its own table, with e.g. MatchID and PlayerID? And same for Assist? And so on, basically for all the so-called "PointTriggers".

Relationships:
Many-to-Many
- a position has many players, a player can play in several positions
- a player plays many matches, a match has many players
- a match has 1 record, which has multiple teams, a team has multiple matches, OR: 1 match has 2 records, one for each team, with a field indicating H(ome) or A(way). (Read this suggestion in an other thread. Comment by June7 I think.)
One-to-Many
- a player can have goals, a goal can only have 1 player
- a player can have many assists, an assist can only have 1 player
etcetc...

Does this make sense so far? Am I on the right track? I've entered very little data, but set up several relations. Want to get the "design" in place first. The key question for me now is: Should all the different point triggers have their own table? I tend to think so. This would be "normalization" of the database tables?

Thanks

[accedeholdings]

If I will make a fantasy football software, I would prefer one to many approach.

[redhoax]

My tables and relations so far:


I believe most of it's OK, but the MatchesTbl is unfinished...can't quite get my head around it yet. If you spot anything weird, or have any questions, please fire away ;-)

What I basically want to extract from this is a player score table, a league table, fixtures list and match reports. Maybe add something like comparing two players with respect to points, player form, etc., but haven't really decided. Maybe I should decide on that before I move on.

[orange]

This model (example) may be helpful.

[redhoax]

Thanks, orange.

I've removed the duplicate PositionTbl_1and the SideTbl, added AwayTeam to the MatchesTbl and removed the field Goals, and I added TeamID to PlayerTbl. I'm quite satisfied with the way it looks now. I'll start working on forms, queries, GUI etc.

Cheers!