need help with my database design

Question

I really really cant get my head around this, i've thought about this for ages but the penny wont drop. I have just started learning php and sql so for all i know its probably really simple. I want to create a football application with leagues, fixtures, and results for a pc football game that a group of us in college play. The idea is this,

User can log in. Manage a team, check fixtures, update their match results, view results, and check league table.

I can do pretty much all of this except for the fixtures/results. Here is my database so far, for what it's worth.

tblUsers ( id PK, username, password, team_id FK (referencing id of tblTeams) )

tblTeams ( id PK, name )

tblFixtures ( id, homeTeam, awayTeam )

This is where im stuck. Should tblFixtures be like this instead:

tblFixtures ( id, ownteam, oppteam )

Ultimately, what i want to achieve is this. User logs in, checks fixture. Plays against opponent. The home player reports match, puts in score, submits. The away player gets sent the report to accept/deny. Then that saves to the database, updates results table and and league table. Im sure i could code this in php im just not sure how i would structure the database. Can anyone give me advice to understand how to do this? THanks

Answer 1

Jonny, I think I'd approach this with a "games" table that includes the following fields:

Table Games: id (pk, int, auto-increment) homeTeamId (int) awayTeamId (int) homeTeamScore (int) awayTeamScore (int) approved (enum Y/N)

So, game is entered initially with a home/away team id. Now, the system knows there's a game to display upcoming (perhaps add date/time/field/etc) If score isn't set, system knows the game hasn't happened yet (or use date as described previously) Home team logs in, adds score, system auto emails opponent for approval. Upon approval, approved field is updated to a Y, and process is complete.