April 23, 2012 at 5:58 pm
Can I ask for some clarification or resources that could help me with the design of a database I am having difficulty with. I am a beginner and have designed my database in the snowflake design and added all tables to MSSQL.
In essence I am doing a project based on Sport, Australian Rugby League. I have defined that 'games/matches' are my central table. That is whilst players, teams, venues, coaches, player statistics, Officials(referees), round identifiers are all important they are all central to the game occurring.
After creating all these tables however I am not exactly sure how to 'tie in' all this detail to the 'games' table, I have created relationships and foreign keys but still I am not sure how to maintain that all records statistics, teams played, players who played are all maintained 'tied' to that game and round.
Is their any advice or reference material that could assist me?
I essentially think I need to copy off a larger example of a well designed database. Most of the small customer/inventory/orders examples you see aren't particularly relevant to my design.
April 23, 2012 at 6:11 pm
i find when im doing database design to just start writing every thing on paper. i start with the information i want to store first (players, teams, game stats) and list out the columns i want to store and do it in no particular order. once i run out of things i can think of i start organizing the columns into tables and in the process think of more columns. once i have every thing sorted out into the tables i look at each table and see if i can normalize the table better. if i can i make out the new tables. once i have all my tables i then look for primary keys. Foreign keys are primary keys in other tables. since you have all ready picked your centeral table you start with all the tables that link to it which should be obvious based on the columns. you just keep going from there. eventually you get every thing but it takes a while.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 7:34 pm
Where I an coming unstuck though is. Players play games and for a team. I have separate player and team tables as players could change teams transfer etc even during a season.
It's the players to games
Games to teams
Players to teams and which players played which I am becoming unstuck with.
April 23, 2012 at 10:17 pm
Perhaps you could think of the relationships like this...
Players are members of a club for a period of time (i.e. they play for one club this year, and a different club next year). This does not mean that they play any games. It really means that they are eligible to be a member of a team that plays a game.
Members of a club may be in a team for a period of time.
A team plays a game on a given date.
i.e. there a number of many to many relantionships that get resolved adding a date of date range
There are other ways to model this but I think the above is fairly simple and represents what you are talking about.
April 24, 2012 at 2:21 am
So sort of make the players table a junction of players, teams and seasons and then create a foreign to games and create the relevant fields there.
I should mention there are other tables(about 15) but its joining this relationship that has caused the problem.
The only downside to this method appears to be if a player transfers between teams in the same season.
April 24, 2012 at 4:16 am
When I start designing a database I divide everything in terms of entities.
And then start designing master tables for every uniquely identifiable group of identities.
Then I create other tables and map them to the master tables.
For example, in your case, I would start by identifying the various uniquely identifiable entity groups which are - players, teams and games.
I would start by making master tables for players(this will house the data of all players), a PlayerID would be assigned to each player(primary key).
Similarly I would create master tables for Teams and Games which will have the Primary Keys.
Then I would further create other tables like Teamwiseplayers, PlayerStats, TeamStats etc. and link all these tables using Foreign keys to the Master Tables.
Hope this gives you a good headstart.
April 24, 2012 at 8:14 am
Player is not a junction of players, teams and seasons. It is more like a list of people who play the sport. It may include other information about the player but nothing about any team afiliation.
Teams are a collection of players who represent a club for a period of time. To me, this means that there is a Club entity and a team entity. I would expect that there would be a time based relationship between teams and clubs (to represent the team that represented the club a a point of time or for a series of games or matches).
Particular teams are made of the players who are selected to play in the team.
As Vinu says, you should start thinking about what are the major entities that you are modelling and what are the relationships. I think that you may be getting confused about the temporal relationship between some of the entities. A lot of relationships I see only exist for some period of time (a season or a range of dates etc). Once you start thinking about these and start adding some of these date based items to your model, things should start to become a little clearer (although, initially, you may find it a little challenging)
April 24, 2012 at 8:25 am
as vinu said about the "master" tables. many players can play on a team and a player can play on many teams (not at the same time but could play for 2 teams per season). so a players table, a team table and an intersection table with playerid, teamid, datestart, dateend would be where i would go. for games you have a match table, matchid, hometeamid, visitteamid, datetime you could also include homescore awayscore. for a stats table i would have all the stats (shots on goal, goals scored...) in a table that has gameid,playerid. a view for team stats that totals up the player stats for that team. as you see once you have your tables figured out the relation ships and intersection tables become almost self explanitory.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 24, 2012 at 2:18 pm
capn.hector (4/24/2012)
as vinu said about the "master" tables. many players can play on a team and a player can play on many teams (not at the same time but could play for 2 teams per season). so a players table, a team table and an intersection table with playerid, teamid, datestart, dateend would be where i would go. for games you have a match table, matchid, hometeamid, visitteamid, datetime you could also include homescore awayscore. for a stats table i would have all the stats (shots on goal, goals scored...) in a table that has gameid,playerid. a view for team stats that totals up the player stats for that team. as you see once you have your tables figured out the relation ships and intersection tables become almost self explanitory.
My tables that I have setup are already very close to the layout you have described.
Importantly the intersection table looks like it would resolve a lot of the problems. Intersection table is the same as a junction table Yes? The two foreign keys Players and Teams (one to many, players to junction).
for games you have a match table, matchid, hometeamid, visitteamid, datetime you could also include homescore awayscore.
Currently I have a Match table with a foreign_key from teams to match one->many, you mention hometeamid and awayteamid. With the relationship I have already setup these fields are okay to derive the data from there.
April 24, 2012 at 3:26 pm
flebber.crue (4/24/2012)
capn.hector (4/24/2012)
as vinu said about the "master" tables. many players can play on a team and a player can play on many teams (not at the same time but could play for 2 teams per season). so a players table, a team table and an intersection table with playerid, teamid, datestart, dateend would be where i would go. for games you have a match table, matchid, hometeamid, visitteamid, datetime you could also include homescore awayscore. for a stats table i would have all the stats (shots on goal, goals scored...) in a table that has gameid,playerid. a view for team stats that totals up the player stats for that team. as you see once you have your tables figured out the relation ships and intersection tables become almost self explanitory.My tables that I have setup are already very close to the layout you have described.
Importantly the intersection table looks like it would resolve a lot of the problems. Intersection table is the same as a junction table Yes? The two foreign keys Players and Teams (one to many, players to junction).
for games you have a match table, matchid, hometeamid, visitteamid, datetime you could also include homescore awayscore.
intersection and junction tables are different names for the same basic principal
Currently I have a Match table with a foreign_key from teams to match one->many, you mention hometeamid and awayteamid. With the relationship I have already setup these fields are okay to derive the data from there.
intersection and junction tables are different names for the same basic principal. once you figure out there will be a 1 to many or many to many relation ship you generate an intersection table inheriting foreign keys from both parent tables.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 24, 2012 at 9:52 pm
flebber.crue (4/24/2012)
My tables that I have setup are already very close to the layout you have described.Importantly the intersection table looks like it would resolve a lot of the problems. Intersection table is the same as a junction table Yes? The two foreign keys Players and Teams (one to many, players to junction).
for games you have a match table, matchid, hometeamid, visitteamid, datetime you could also include homescore awayscore.
Currently I have a Match table with a foreign_key from teams to match one->many, you mention hometeamid and awayteamid. With the relationship I have already setup these fields are okay to derive the data from there.
If you have got the tables(entities & relationships) set up like this then your database design is very good. A good and simple DB Design is very important for good functioning of the System/Application.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply