Database Modeling of a Softball League

I am modeling a database for use in a softball league website. I'm not that experienced in DB Modeling, and I'm having a hard time with a question about the future.

Right now I have the following tables:

players table (player_id, name, gender, email, team_id)

teams table (team_id, name, captain[player_id], logo, wins_Regular_season, losses_regular_seaso开发者_Go百科n)

regular_season table (game_id, week, date, home[team_id], away[team_id], home_score, away_score, rain_date)

playoff table (pgame_id, date, home[team_id], away[team_id], home_score, away_score, winnerTo[pgame_id], loserTo[pgame_id])

To make the data persist from season to season, but to also have an easy way to access the data should I:

A) include a year column in the tables and then filter my queries by year? B) create new tables every year? C) Do something else that makes more sense but that I can't think of.

This design is not only bad about the future. It is also wrong regarding the past. You're not keeping history in a proper way.

Let's suppose a player changes team: how would that fit into this design? It should be easy to get that kind of information...

And the best way of doing that (IMHO) would be also representing the season as an entity, as a concrete table. Then you should replicate this information in each relationship. Meaning, for instance, that a player does not simply belong to a team: he belongs to a team in a specific season, and may belong to another team when the season changes.

OTOH, I don't think it's wise to keep regular_season and playoff as distinct tables: they could be easily merged into one, by adding some sort of flag in order to keep that information.

Edit This is what I'm meaning:

• Notice that there is a Season table.
• A Player belongs to a Team in a Season.
• NO NEED TO DUPLICATE ANYTHING. A team has only ONE record in the DB; a player will be associated to only ONE record.
• I did NOT design the Playoff table, because I believe it should not exist. If the OP disagrees, just add it.

That way you can keep track of all seasons, without needing to replicate the whole DB. I think this is also better than using a year column, which is not meaningful, and cannot be easily constrained (like a foreign key can).

But, please, feel free to disagree.

The standard way would be to add year columns to your tables. That way you can easily call up the past with a select query, or view. SQL Server has good support for this. I've dealt with cleanup of the other route, and it isn't pretty after a few years of data have accumulated.

I would go with option A and have a year column in the seasons table and playoff table.

You already have a date column, you can use that to find the year

SELECT * FROM regular_season WHERE YEAR(date) = 2011