Need advice on MySQL table structure

Forum Moderators: open

Message Too Old, No Replies

Need advice on MySQL table structure

MySQL Tables

jawinn

6:51 pm on Nov 19, 2006 (gmt 0)

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?

physics

9:12 pm on Nov 19, 2006 (gmt 0)

Since the teams will play in more than one game you will want a teams table with a unique id. So something like:

team
----
id
name
(other optional team info fields)

game
----
id
week
id_team_1 (refers to id values from team table)
id_team_2
(other optional game info fields)