OK, here we go (all tale show only minimum tables I think are needed, also i use postgres so the syntax for the table creates may be different, i dunno):
create sequence team_id_seq;
create sequence game_id_seq;
create table team (
team_id int4 not null default nextval('team_id_seq'),
name varchar(25),
primary key (team_id)
);
create table game (
game_id int4 not null default nextval('game_id_seq'),
location_id int4 not null references team(team_id),
play_date date,
primary key (game_id)
);
create table score (
game_id int4 not null references game(game_id),
team_id int4 not null references team(team_id),
score int4 default 0,
primary key (game_id,team_id)
);
This query will give you a list of games with results, identifying home/visitor score:
select t1.name as home_team,
t2.name as visitor,
g.play_date,
s1.score as home_score,
s2.score as visitor_score
from team t1,
team t2,
game g,
score s1,
score s2
where t1.team_id = s1.team_id
and t1.team_id != t2.team_id
and t1.team_id = g.location_id
and t2.team_id = s2.team_id
and s1.game_id = s2.game_id
and s1.game_id = g.game_id;
From here, you do just about anything you want, including yearly breakdown, etc... let me know if this is helping....
