Guys, I'm designing a database to deal with sporting fixtures, specifically Soccer.
Obviously a fixture has two teams, but a team takes part in many fixtures.
I'm unsure how to deal with this. Do I require 3 tables, fixture, team and fixture_team (fixture id, hometeam, awayteam)?
Or, is it possible I could just include hometeamid and awayteamid in the fixture table and not use fixture_team?
Fixture>Team Question
Moderator: General Moderators
Re: Fixture>Team Question
Neither method is wrong, each schema makes some things easier and some things harder. When I was working on an application that had to keep track of various kinds of sports results, I went provisionally with putting the team ids in the fixture table.
* It's slightly easier to understand and less likely to create errors, as the schema reflects the everyday understanding that every match has a home and away team.
* But you can't create events with open-ended list of participants like a horse race or a golf tournament, which may or may not be something you want to do.
* You can use the database to ensure that every match has a home team and away team and no other participants.
* But the database can't enforce the rule that the two teams have to be different.
* It's slightly easier to understand and less likely to create errors, as the schema reflects the everyday understanding that every match has a home and away team.
* But you can't create events with open-ended list of participants like a horse race or a golf tournament, which may or may not be something you want to do.
* You can use the database to ensure that every match has a home team and away team and no other participants.
* But the database can't enforce the rule that the two teams have to be different.
Re: Fixture>Team Question
Thanks for the reply DML.
I was wondering, if I just used hometeam and awayteam in the fixture table, how do I show that on the ERD, as both create foreign key constraints to team?
A further point is how I've handled individuals in my design. Before I had players, refs, managers, etc as seperate tables. I've now moved to using a generic person entity and created a role and role_fixture link entity, this is how I handle, for example, adding a ref to a fixture. I wondered how you handle this in your design?
However, I am stuck in this area. I want to "extend" my person class to a player entity, because a player entity will hold specific data, such as estimated value, preferred moves, etc. However, what relationship does person have to player? Thus far I have linked like this person(personid, name, etc), player(playerid, personid, value, etc), I'm not quite sure how this relates on an ERD, since it appears a 1-1 relationship, or is it? Remembering a player could also be a manager or a manager a former player and so forth?
I was wondering, if I just used hometeam and awayteam in the fixture table, how do I show that on the ERD, as both create foreign key constraints to team?
A further point is how I've handled individuals in my design. Before I had players, refs, managers, etc as seperate tables. I've now moved to using a generic person entity and created a role and role_fixture link entity, this is how I handle, for example, adding a ref to a fixture. I wondered how you handle this in your design?
However, I am stuck in this area. I want to "extend" my person class to a player entity, because a player entity will hold specific data, such as estimated value, preferred moves, etc. However, what relationship does person have to player? Thus far I have linked like this person(personid, name, etc), player(playerid, personid, value, etc), I'm not quite sure how this relates on an ERD, since it appears a 1-1 relationship, or is it? Remembering a player could also be a manager or a manager a former player and so forth?
Re: Fixture>Team Question
I'm not familiar with ERD's, seems strange that there can't be two kinds of relationship, appropriately labelled, between two entities.
I didn't need to keep track of players in my application, which just needed schedules and scores. It sounds like you're doing IMDB for footballers, managers, and referees. Are you sure you want to go that far? What's the plan for keeping it up to date?
The way you have it set up, it sounds like there won't be very much information in that Player table, that most of the interesting information will be in tables representing the participation of players in matches, and the spells players spend playing for various teams, which makes sense, since a playing career is defined by what a player does for specific teams in specific matches. If you wanted to go even further with that logic, you could even put estimated value into an Estimation table with the possibility of tracking the player's value throughout his career, and similarly with a preferred moves table. Then again, if there are few enough attributes, you might be making life much easier for yourself if you throw them all into the Person table - if it's a ref, let them have a null value in the column.
If you're going with players and persons, each player is a person, and every person may or may not have a playing career, so the cardinality is 1 on the person side and 0..1 on the player side. I find it confusing to say that a Player entity has a relationship with a Person entity - it makes it sound like we're talking about the player's wife. And it's not a case of Player being a straightforward subclass of Person, since somebody can start as a player, become a player-manager, and then a manager. I prefer to have a relationship between a noun and a role that noun plays - a Person who can have a PlayingCareer and/or a ManagerialCareer (or PlayerDetails or ManagerialDetails), or maybe it's more natural to flip it around and have Players and Managers who have PersonalDetails.
I didn't need to keep track of players in my application, which just needed schedules and scores. It sounds like you're doing IMDB for footballers, managers, and referees. Are you sure you want to go that far? What's the plan for keeping it up to date?
The way you have it set up, it sounds like there won't be very much information in that Player table, that most of the interesting information will be in tables representing the participation of players in matches, and the spells players spend playing for various teams, which makes sense, since a playing career is defined by what a player does for specific teams in specific matches. If you wanted to go even further with that logic, you could even put estimated value into an Estimation table with the possibility of tracking the player's value throughout his career, and similarly with a preferred moves table. Then again, if there are few enough attributes, you might be making life much easier for yourself if you throw them all into the Person table - if it's a ref, let them have a null value in the column.
If you're going with players and persons, each player is a person, and every person may or may not have a playing career, so the cardinality is 1 on the person side and 0..1 on the player side. I find it confusing to say that a Player entity has a relationship with a Person entity - it makes it sound like we're talking about the player's wife. And it's not a case of Player being a straightforward subclass of Person, since somebody can start as a player, become a player-manager, and then a manager. I prefer to have a relationship between a noun and a role that noun plays - a Person who can have a PlayingCareer and/or a ManagerialCareer (or PlayerDetails or ManagerialDetails), or maybe it's more natural to flip it around and have Players and Managers who have PersonalDetails.
Re: Fixture>Team Question
Some real good stuff there DML, thanks for that.
One issue outstanding in my mind is this. I have used as I stated a generic person entity. I have then setup a role entity and obviously via that I can define the role a person takes. Clearly, a person takes a role for a team between x dates, thus I have the ability, therefore, to define the career of a person.
I've used the person_role approach to eliminate the need for seperate entities defining refs, chairmen, chief execs, scouts, physios etc, because I don't see they would need to hold unique attributes in my design. However, with players it is different. I do have a person entity and this links to a player entity, as a player has several attributes not related to him/her as a person. Example, predominant foot, strengths, weaknesses, set piece status, ability rating and so forth. I then link player to team via a link entity, defined as his playing_career.
I'm sure you are with me thus far. However, this has led to almost a duplication in how I'm handling a player as apose to any other role. Let me expand. Obviously a player takes part in a fixture for a team, i use player_fixture_stats to therefore define his role in a game. However, this is seperate from person_role_fixture, where here I use the entity to define the role for someone who isn't a player, namely a referee, physio, manager. This seems almost like an unacceptable duplication, yet to define an overall entity structure generic to say a ref and a player would create a lot of waste, for example, no data on passes completed would ever be used for a ref, whereas it is required for a player. I guess what I'm really asking is do you feel it wise to seperate a "player" from other generic "roles" in this manner?
Then I have also seperated person_role_team from person_role_team_fixture. I have done this because while I may want to know john smith was chief scout at a club between x and y date, I don't need to know this was a role in a specific game, it isn't of relevance. Whereas, I really need to know who was manager for x team or who was ref for a specific game. This also brings about one further problem. In person_role_fixture_team, a manager is clearly stated for a specific game and in charge of a specific team, this is fine for the majority of roles, since in a fixture people are employed by a team. However, that doesn't apply to the referee. So I'm using a foreign key to team, yet a ref isn't strictly part of a team, unless, I define "officials" as a "team", which seems a bit of a hack. Either that, or is it valid to leave the foreign key for an official set to null?
One issue outstanding in my mind is this. I have used as I stated a generic person entity. I have then setup a role entity and obviously via that I can define the role a person takes. Clearly, a person takes a role for a team between x dates, thus I have the ability, therefore, to define the career of a person.
I've used the person_role approach to eliminate the need for seperate entities defining refs, chairmen, chief execs, scouts, physios etc, because I don't see they would need to hold unique attributes in my design. However, with players it is different. I do have a person entity and this links to a player entity, as a player has several attributes not related to him/her as a person. Example, predominant foot, strengths, weaknesses, set piece status, ability rating and so forth. I then link player to team via a link entity, defined as his playing_career.
I'm sure you are with me thus far. However, this has led to almost a duplication in how I'm handling a player as apose to any other role. Let me expand. Obviously a player takes part in a fixture for a team, i use player_fixture_stats to therefore define his role in a game. However, this is seperate from person_role_fixture, where here I use the entity to define the role for someone who isn't a player, namely a referee, physio, manager. This seems almost like an unacceptable duplication, yet to define an overall entity structure generic to say a ref and a player would create a lot of waste, for example, no data on passes completed would ever be used for a ref, whereas it is required for a player. I guess what I'm really asking is do you feel it wise to seperate a "player" from other generic "roles" in this manner?
Then I have also seperated person_role_team from person_role_team_fixture. I have done this because while I may want to know john smith was chief scout at a club between x and y date, I don't need to know this was a role in a specific game, it isn't of relevance. Whereas, I really need to know who was manager for x team or who was ref for a specific game. This also brings about one further problem. In person_role_fixture_team, a manager is clearly stated for a specific game and in charge of a specific team, this is fine for the majority of roles, since in a fixture people are employed by a team. However, that doesn't apply to the referee. So I'm using a foreign key to team, yet a ref isn't strictly part of a team, unless, I define "officials" as a "team", which seems a bit of a hack. Either that, or is it valid to leave the foreign key for an official set to null?
Re: Fixture>Team Question
That design will allow for a lot of possibilities.
The special information about players and their performances in a game: what about using the generic tables for getting the basic information and having separate tables that point to the generic ones, and that you can join to if extra information is required?
It's perfectly ok to have a foreign key constraint that allows null, in the case of a match official not being part of a team.
The special information about players and their performances in a game: what about using the generic tables for getting the basic information and having separate tables that point to the generic ones, and that you can join to if extra information is required?
Code: Select all
persons table: id, name, dob, ...
players table: id, person_id (fk to persons.id), predominant foot, ...
person_role_team_fixture: id, person_id, role_id, team_id, fixture_id, ...
player_fixture_stats: prtf_id (fk to person_role_team_fixture.id), num_passes, ...
Re: Fixture>Team Question
Basically what I have done dml
The only difference is I actually didn't plan to hold any player data in the person_role_team_fixture, I planned to hold that information in player_fixture_stats ONLY i.e. p_r_f_t only held data on non playing staff. Thus my FK was from player_fixture_stats was to player, not p_r_f_t.
I feel putting the fact x was a player in p_r_f_t was duplication of the player_fixture_stats data. If only I could extend p_r_f_t and use that to store extended information relating to a player, rather than having to store it in a seperate table.
Do you feel it essential I store a player in the p_r_f_t? Say I wanted to see all people who played a role in a game, I was planning to use two queiries to show players and non players from the differing tables, rather than just one? Which approach do you feel is advised?
Apart from that it seems happy days!
The only difference is I actually didn't plan to hold any player data in the person_role_team_fixture, I planned to hold that information in player_fixture_stats ONLY i.e. p_r_f_t only held data on non playing staff. Thus my FK was from player_fixture_stats was to player, not p_r_f_t.
I feel putting the fact x was a player in p_r_f_t was duplication of the player_fixture_stats data. If only I could extend p_r_f_t and use that to store extended information relating to a player, rather than having to store it in a seperate table.
Do you feel it essential I store a player in the p_r_f_t? Say I wanted to see all people who played a role in a game, I was planning to use two queiries to show players and non players from the differing tables, rather than just one? Which approach do you feel is advised?
Apart from that it seems happy days!
Re: Fixture>Team Question
I don't know what the application is about, so I can only treat this as an exercise in abstraction. My schema is an exercise in pushing a certain kind of generalisation as far as it can - it only deals with person entities or match role entities, which may or may not have player stats attached. Your way of doing it treats players in a specialised way, and support staff in a generic way.
If the application is about football people in general, where groundskeepers and goalpost putter-uppers are just as much first class citizens as players, then the generic schema will probably give you the most options with your data. If the application is mainly about players and their performances, and where you expect to add more kinds of player stats in future, then it may cause more trouble than it's worth to try to shoehorn both players and support staff under a common abstraction.
If the application is about football people in general, where groundskeepers and goalpost putter-uppers are just as much first class citizens as players, then the generic schema will probably give you the most options with your data. If the application is mainly about players and their performances, and where you expect to add more kinds of player stats in future, then it may cause more trouble than it's worth to try to shoehorn both players and support staff under a common abstraction.