Database design question - 1:2 relation 1

[paskuda]

Hello,

I'm designing a small database where soccer results are going to be stored.
I have created following tables:

FB_Teams, with primary key I_TEAM_ID, storing team data.
FB_Competitions, with primary key I_COMPETITION_ID, where I want to keep details of leagues, cups, etc.
As most of you know, exactly two teams participate in soccer game. Order is not important for me. My question is - how to design FB_Fixtures table, which I'd like to use to keep matches results in ? Each fixture is a single game which belongs to some competition.

My initial idea was:

I_FIXTURE_ID - int, identity, primary key
I_COMPETITION_ID - int, foreign key referencing FB_Competitions
I_TEAM_A_ID - int, foreign key referencing FB_Teams
I_TEAM_B_ID - same as above
I_TEAM_A_GOALS - int, amount of goals scored by team A
I_TEAM_B_GOALS - int, amount of goals scored by team B

I don't like this though. First, it introduces some order (team A and team B), second - it makes writing queries quite difficult. Any ideas how to solve it in a better way ?

 

[AlexCuse]

Perhaps try to have two rows in your table for each I_COMPETITION_ID, and add a goals allowed field (basically replacing your amount of goals scored by team B) and a bit field for win/loss? Another thing to add might be an indicator for whether the team was home or away.

This would allow you to query wins/losses, goals scored, goals allowed on a team by team basis (and for home or away games) fairly easily.

Are you going to be populating this through some kind of front end, or directly into the table?

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Olaf Doschke]

Then how about this:

design 1
========

table fixtures
I_FIXTURE_ID
I_COMPETITION_ID

table participants
I_PARTICIPANT_ID primary key
I_FIXTURE_ID foreign key
I_TEAM_ID foreign key
I_GOALS amount of scored goals

Any number of participants of a fixture, while there should be exactly 2.

design 2
========

table fixtures
I_FIXTURE_ID prime key
I_COMPETITION_ID foreign key
I_PARTICIPANT_A_ID foreign key
I_PARTICIPANT_B_ID foreign key

table participants
I_PARTICIPANT_ID prime key
I_TEAM_ID foreign key
I_TEAM_GOALS amount of scored goals

same problem again, in finding the fixture for a given participant_id, found from a query with a team_id. That participant_id must be searched in both I_PARTICIPANT_A_ID and I_PARTICIPANT_B_ID.

Bye, Olaf.

 

[lgarner]

I'd change the participants table like so:

I_PARTICIPANT_ID prime key
I_FIXTURE_ID foreign key
I_TEAM_ID foreign key
I_TEAM_GOALS amount of scored goals

Then, drop the team fields from the fixture table. Finding the teams in a fixture could be done with "select * from participants where I_FIXTURE_ID=fixture_id;"

 

[ceh4702]

I see some problems.

When you set up the games or matches you may want to know who is playing before you know who wins.

Think of this The winner could be a computed column in a report or you could have a field for status 1 and status 2.

GAME_EVENT
GAME_ID
GAME_TIME_DATE
TEAM_1
STATUS_1
TEAM_2
STATUS_2

Since status 1 and status 2 are computed columns they are are just one possiblity. You could have a pointer to the winning team instead like this

GAME_ID
GAME_TIME_DATE
GAME_LOCATION
TEAM_1
TEAM_2
WINNER
GAME_STATUS (i.e. Cancelled, Won, Forfeit,pending)


Another Table:

SCORES
GAME_ID
TEAM_
SCORE

Another table

GAME_STATS
GAME_ID
PLAYER_ID
GOALS_SCORED
YELLOW_CARD
RED_CARD
POSITION_PLAYED
FOULS_MADE
FREE_KICKS
FREE_KICK_GOALS

You might be able to do a querry and link the game to the teams and order the teams based on the score, so that the winning team is always on top. Still knowing who the winner is has an advantage to speed up certain queries.

You could nick pick this to death. The advantage of a status is the ability to record a Forfeit, a win , a loss, a kick-off, Called Game or cancelled game for weather, etc.

Of course you could go nuts and record the position of every player for every game or just like the players to the team. You might want to know who played in which game if you are doing stats.

You could have a tournament where there are mulitple games for a team in a given day. So the time is probably important. Maybe the overtime info is important also. So you could have a tournament table with multiple games linked to it. Tournaments may be single elimination double elimination or whatever.

I like to think of the problem from the object level. No one approach will solve all problems easily. You may not like my ideas at all.

If you do not like my post feel free to point out your opinion or my errors.

 

[r937]

actually i like the design as shown in the initial idea in the very first post (using "home" and "away" as distinguishing column names)

it makes the queries a bit harder, but not all that much harder

i've even got (somewhere) a sample query which calculates "latest streak" (e.g. 5 wins), but that one was difficult -- as it will be no matter which way you store the data

r937.com | rudy.ca

 

[paskuda]

Hello,

Thank you all for your help and suggestions. I decided to use Olaf's design 1. I'm going to write the application using the database, so I can programatically limit the amount of teams assigned to one fixture. It's more important to keep team+fixture data in separate rows.

ceh4702,
I'm not sure if I got it right. Do you suggest I should keep both participating teams in one record ? That might speed up some queries, but overall is a pain in the back, I used that design before and after a while I got a lot of problems writing more complex queries. Thank you for bringing up the idea of multiple games during the day, I'm going to add some datetime field.


Michal

 

[Olaf Doschke]

Hi paskuda,

glad you go with my design, changing I_GOALS to I_TEAM_GOALS, as lgarner suggested might be a bit closer to your naming convention.

It's of course true that you can store much more info than just the goals. I didn't want to start a discussion of the whole database design....

thanks for the awarded star.

Bye, Olaf.

 

[ceh4702]

I kind of like the design

I_PARTICIPANT_ID prime key
I_FIXTURE_ID foreign key
I_TEAM_ID foreign key
I_TEAM_GOALS amount of scored goals

Only you cant look at the row and see if they won or lost.

I have seen some point systems like they use in Hockey where you get something like 1 for a win and 1 for a tie and 2 for a win in overtime. Then any time the game is tied at the end of regular competition gets a point if they lost in overtime. I am guessing you have 10 free kicks for each time if it is a tie or something like that.

If you do not like my post feel free to point out your opinion or my errors.