Data scheme for a litle football league [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 11 years ago.

• In one year, there are two seasons: Season Spring and Season Fall
• On one season we play on each monday night
• On every monday nights we divide ourselves to a two 5 player teams, randomly selected by picking a red og black card.
• In one game, we play untill either team has won by scoring 5 goals.
• In one monday night we play somewhere betweeen 2 and 4 games, depending on how much we score.
• Every player in the winning team gets a one point for winning a game, so on each monday the player can get several point, depending on how many games he wins.
• There are same players in red og black teams the whole night, so you will get same amount of points like other 4 players if you were in the same team as them at the same night.
• the scoring board holds the name of the player and how many games he has won over the season.

---

I have a headache, how can a create a datascheme on sql database that carries this kind of data.

It should witholds a datarow with every game we play on the season and the result of the game.

It should be linked with some other table that holds in what team each and every player was playing with (red or black team)

Should i have one row, that carries one monday night and this row say something like:

Monday - 21.9.2011 - 3 - 2

that would say that that the match was on monday with the date and the result were 3 games won by red team and 2 games won by black.

---

I´m not asking for a complete solution, just some tips on how i shou开发者_StackOverflow中文版ld have the data scheme lined up.

thank you

---

I would start with the Player table (player_id, player_name, etc...)

I would also have a Weekly_Match_Results table (weekly_match_id, match_date, games_won) You will put 2 records in here each week, one for Red, one for Black.

Then create the Player_Match_Reference table (player_match_id, player_id, weekly_match_id) You'll put a record in here each week for every player.

You should then be able to join the 3 tables to get each player's points.

---

Break it down this way:

First, identify the entities. An entity is a person, place, thing, event etc that is stored in the database. Each entity will typically have attributes. An attribute is a detail about the entity. For example, if you had a table of cars, you may store several attributes about the car (year, make, model, color, miles, condition)

Look at the question and make a list of the entities listed (e.g. player, game etc)

The next step is to think about the relationships between the entities (e.g. many players will belong to many teams. One teams will play many games.) You'll want to read about many to many relationships to best implement that.

The alternative approach is to start with sample data in one big table. Then follow steps to normalize the data. http://databases.about.com/od/specificproducts/a/normalization.htm