Geek asks and answers

Softball League Database Modeling

I am modeling a database for use on the softball league website. I am not so good at DB Modeling, and it is difficult for me to ask about the future.

Now I have the following tables:

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

team table (team_id, name, captain [player_id], logo, wins_Regular_season, loss_regular_season)

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])

In order for data to be saved from season to season, but also have an easy way to access the data, I have to:

A) Include the 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 I can't come up with.

+3
source share
4 answers

This design is not only bad in the future. This is also not true of the past. You are not following the story properly.

Suppose a player changes a team: how will this fit into this project? It should be easy to get such information ...

And the best way to do this (IMHO) would also be to represent the season as an entity, as a specific table. Then you must copy this information in every respect. For example, a player does not just belong to a team: he belongs to a team in a certain season and may belong to another team when the season changes.

OTOH, regular_season playoff : , - , .

. , :

enter image description here

  • , Season.
  • A Player a Team a Season.
  • . ; ONE.
  • playoff, , . OP , .

, , . , , year, (, ).

, , .

+3

- . , . SQL Server . , , .

+2

I would choose option A and the year column in the season table and playoff table.

0
source

You already have a date column, you can use it to search for the year

SELECT * FROM regular_season WHERE YEAR(date) = 2011
0
source

More articles:


All Articles