Need advise on MySQL table structure

I'm setting up a DB to house info on football games and have people select which team(s) they think will win each week. Here is the data I will have to work with. I need help in figuring out the best way to setup the table.

ID field (unique key value set to auto increment)
Week number (each week will have a different number)
Team 1 vs Team 2 (teams playing that week)
Team 3 vs Team 4 (teams playing that week)

There will be 12 to 14 "team vs team" fields. The winners will be decided by either manual input or through a script. The issue becomes should I divide up the Team 1 and Team 2 into separate fields or put them in a single field?

 

Table - Teams
teamid
team name

Table - Matches
weeknumber (or date/timestamp if you need to know what day and time)
team1 - foreign key teamid
team2 - foreign key teamid
result

 

Will the users be making their selections within the system? If so, you will need a users table and a selection table. I would suggest naming the two columns in your matches table to be home_team and away_team. Also, add a matchID in your match table. Best practice to avoid any error when matching rows if any data was improperly entered.

The selection table would have:
selectionID
userID
matchID
choice (this could be a bit field on for home, off for away. Or it could be a char field).

Users table:
userID
name
password (possibly)

 

Excellent point druidelder. How would you suggest I work out how the users are shown who picked which games correctly?

 

Do you want to show the entire ranking or just the winner?

 

Both would be awesome.

 

Here is how I wrote it out on paper:

tblUser
-userID int pk (primary key) autogenerate
-userName char(25) [or varchar if you have it]

tblTeam
-teamID int pk autogenerate
-teamName char(25)

tblMatch
-matchID int pk autogenerate
-homeTeam int fk (foreign key) [teamID from tblTeam]
-awayTeam int fk [teamID from tblTeam]
-matchWeek int (must have some way to determine which matches to calculate)
-homeScore int
-awayScore int
-matchWinner bit (boolean) [true for home, false for away]

tblSelection
-selectionID int pk
-userID int fk
-matchID int fk
-choice bit

Those are the tables I would have for the query below. This set up is simplistic and will only work if you're picking winners and not scores. Also, the tables would need to be cleaned up or the weeks would have to ever increase if you used it over multiple seasons. If you added a matchYear column to tblMatch then you could avoid this.

Query:

SELECT u.userName, (SELECT count(s.*) AS pickCount FROM tblSelection AS s INNER JOIN tblMatch AS m on s.matchID = m.matchID WHERE s.userID = u.userID and m.matchWeek = @week and s.choice = m.matchWinner) AS wins FROM tblUsers ORDER BY wins DESC

This gives you the list from the top to the bottom. @week is a parameter that is passed in (or hard coded, but that would have to be changed each season). You can do a Select Top 1 to get the winner.

I didn't run this, so there may be a syntax error or two, but you get the general idea. This only gives you totals, not a list of the matches with picks/wins.