Ideas for Table Structure

[dmilani]

I have a DB that is used to register teams for a soccer league. The primary purpose/function is managing team contact info and payment histories.

Right now the DB contains the following tables:

Contacts
-contactID
-email
-fname
-lname
-phone
-address
-date

Teams
-teamID
-tname
-contactID
-club
-age

Registration
-regID
-teamID
-league
-session
-reqdiv
-date
-misc

Payments
-payID
-regID
-payer
-note
-date
-type

I would like to add a scheduling component to this system and am trying to figure out the best way to do this. How would people store this schedule? Do you suggest a "Schedule" table with each record being a game? OR a schedule table with an individual record for each team and unique fields contain the game assignment. (There's 8 games per season.)

I have search for examples but can't find anything that fits. Mostly just turned up calendar apps or resource scheduling. That are only scheduling one entity (person/equiment) not two entities (teams).

Thanks for any thoughts. . .

[oracleguy]

A schedule table would work fine in this instance, each row can be one individual game.

[Old Pedant]

You could even combine your schedule with your results, all in one table.

Maybe something like:

homeTeamID : fk to Teams table
awayTeamID : fk to Teams table
when : DateTime
homeTeamScore : int, default NULL (indicates no results in this game yet)
awayTeamScore : int, default NULL

Then it's reasonably easy to find the record for a given team or for all teams from just that data.