The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Complex Sports Team Database

Hi,

I'm currently trying to develop a database for my University Rugby team, to include, amongst other things, player profiles, matches, match statistics, league tables etc. At the moment I'm having a lot of trouble getting around the different tables and relationships that need to be created. I have a player table, linked to a team table, linked to a match table but every week a different set of players, playing for the team, are involved in a match. Any help or advice regarding how to model this situation would be greatly appreciated.

Thanks

Thanks a lot guys, I'm quite new to the whole database thing, that helps quite a bit. It looks like I'm dealing with a whole load of many-to-many relationships and as you say, I'll have to create quite a few middle tables to deal with the complexity. If I have any more problems, I know where the experts are!

Another Question

I got the bit about the player_match and player_team tables, but what if different players are playing for the same team in each match? (ie. the team changes from match to match) does that mean I need some sort of Player_Team_Match table, or how would I go about incorporating that information? Any help is greatly appreciated.

many players, many teams

Yes, not only can different players play in the same team but they can play in many teams, for example, in my position, I could play for the first team or the second team, or someone else might play in my place.

I was thinking about it quite a lot last night, I don't really need to record the details of opposition players - only my club. That means I don't really need to identify the players with the team as it will be taken as given.

While I'm on that note, are there any good books/websites that you would recommend on data modelling or relational database design? I can see a lot of books out there when I search but none seems to stand out above the other. I'll be taking a course next semester on web programming and databases to supplement what I do at the minute as a hobby, but I fear that might just go over the basics rather than go a little more in depth. When it gets to this stage the relationships get a bit confusing!

Yes, not only can different players play in the same team but they can play in many teams, for example, in my position, I could play for the first team or the second team, or someone else might play in my place.

ignoring your second comment about not needing to record the other team's details, i suggest this adaptation of Hartmann's solution:

Players
-------
player_id
player_name
position_id
etc.

Team
-------
team_id
team_name
etc.

Team_Players
-------
team_id
player_id
start_date
end_date

Match
-------
match_id
home_team_id
away_team_id
etc.

Positions
-------
position_id
position_name

Players_Match
--------------
player_id
match_id
team_id
position_id

this arrangement allows the following:

identify which team a player played for during a particular date range (leave end_date NULL for players actively assigned to a team)

allow you to record when a player temporarily joins another team for a specific match, since the team_id is in the players_match table

allow you to record when a player competes in a position during a match other than their usual, since the position_id is in the players_match table