Database Structure - Fantasy Football League!

[baggyboy26]

Hello,

I've been asked to create a database for a friend to help him run his Fantasy Football League.

I'm not new to access though I am having problems with structuring my database.

So far I have two tables, one with all the football players in. Containing their ID, Name, Postion, Club, Price, etc... The second table contains the Fantasy Teams: ID, Owner, Team Name, GoalKeeper, Defender1, Defender2, Defender3, Defender4, DefensiveMidfielder, Midefielder1, Midfielder2, Midfielder3, Striker 1 and Striker 2, plus 10 fields for the substitutes.

My problem is I want the user to use a form for each of the 38 weeks that contains the total list of 'selected' players. Then allowing him to just enter the info about whether they started, got sent off, clean sheets, goals, etc... This info is then referred to by each of the seperate Teams to get a weekly score for each team. But still maintaining a history week by week of what each 'selected' player achieved.

I have tried several ways of organising this and none seem to have the effect i'm after. I'm beginning to think that i shouldn't have offered but i haven't been defeated yet. Hence I'm asking for some supperior wisdom! Any ideas would be gratefully recieved.

BaggyBoy

Sometimes the answer stares you right in the face!!!

 

[fneily]

You mean soccer.

Have you read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Your tables are not normalized. Example - Defender1, Defender2, etc. Drop the number you have duplicate column headings, variable length records, table within a table - all violates first normal form.

So something like this:
tblPlayer
PlayerID Primary Key
FirstName
LastName
Price
Club

tblTeam
TeamID Primary Key
Owner
TeamName

tblTeamPlayers multi-field primary Key
TeamID First Part of PK
PlayerID Second Part of PK
Position

tblStats
StatID Primary Key
PlayerID Foreign Key
Started
Goals
Assists
RefCards
DateOfGame

So to get someone's team, connect tblStats to tblTeamPlayers through PlayerID.

 

[baggyboy26]

Thanks for your advice.

I have read the article and tried my best to use it to help. I agree with your normalization, just need to persevere with the relationships.


Sometimes the answer stares you right in the face!!!