Database Design for Baseball (was "New to DB Design, Please Help..")
Hello All.. I'm somewhat new to creating databases, let alone relational databases, so please bare with me.
I'm taking on a project where I'm creating a web site for a local HS baseball team. I want to start this out right so I figured I should check on my db design before I start doing any Web Form creations. I've started the process but quickly got flustered with creating relations with the different tables, it almost seems like I have to many.
My goal for the website page will have a Roster that is tied to a specific Team, (Varsity, JV, Freshman, etc), which will show Name, Number, and Position of each Player. It will indicate which which Season (2010, or 2011, etc..), and which Subseason (Regular Season, Playoffs, etc), the user is viewing. The user will be able to click on a Player to get a detail view of the player's profile which will include their additional info, Stats, Batting and Pitching, (Pitching only if they are a Pitcher).
Players can be in multiple teams, (however they will have to be entered a second time within the other Teams Roster section). Teams Players Stats will be inputted and calculated from Game input information, (Second part of this design). The admin (Coach) will be able to create new Seasons, and/or a new SubSeason, within a Team and select which Season or Subseason will be the default view when the enduser enters the site. End Users will have the option to review previous Seasons or SubSeason via a dropdown.
The second part I haven't included a pic just because I want to make sure i'm on the right track before I spending more time on this. I have it in a spreadsheet, which I used so I can add test data to see how things "should" flow, but just haven't moved it over to Visio yet. However it will be for Game Scheduling, and Game Stats which also have relations back to the Season, Subseason, and Teams Tables. I will post it as well, once I know where I am with this one.
Just to let you know I doing this all on my box using SQL Express, I will also be creating this in DotNetNuke using a 3rd party module that will help me create the Web Forms, (adding, editing, updating,) and Views that exist in the Database.
I appreciate any thoughts or opinions, like I said this is all pretty much all new to me, but I have done some research, I hope it shows.. :-)
As a quick idea for you wouldn't it be a bit easier to manage your players, if you did not have to put an entry in the table for each team and subseason that they play? Wouldn't it be a little less cumbersome if you turned the relationship around, so that team had a foreign key to player or an intermediate table like team_roster? Also, in this way when you want a historical look at a player you wouldn't have to link all of the different ids for a player together to get their entire history.
Dav1mo..Thanks for your reply. I can see your point, at least I think I can, and its seems like a vaild idea. However I'm not sure it would work, if it does I just don't know how to execute it.
You see the plan is to create the Season, Subseason and Teams via a webform first, during the creation process the form will also pass values for the respctive table IDs to the FK realations. So that the tables stays related as expected. However if I was to setup a Players FK relationship in the Teams table I couldn't (or at least don't know how) pass the PlayersID to the FK relation in the Teams table, with the Teams being created first from the web form.
If I'm missing this completely please let me know, or if this is doable and I'm just over looking something please let me know as well.
The point of it is as follows. A player can not be made up of teams, instead the reverse is true a team is made up of players, and that is why I described turning the relationship around. It does not have to be a direct relationship though, you can have your TEAM table which gives you a single row to name your coach(es) team name, season,etc... And you have your PLAYERS table, which lists your athletes and their identifying info, birth date names, etc... Then you would tie these two together with a third table, say, TEAM_ROSTER, which would have your team_id and your player_id and foreign keys defined appropriately.
Dave..Ok I see where you going now. For the most part I can see what you’re talking about, and that does seem to make since. I've mapped it out, kind of, to get a better feel for how it should work. My concerns are that the coaches will still need to be able to create a new Subseason, within a Season, for example if they make the playoffs and be able to create a new Season, for example 2011-2012 all without losing relations to the Players, otherwise I'm concerned they will have to re-enter each player again. Which I'm thinking if I had an associated FK to the Team_Roster tab for each of those tables that may eliminate that concern.
Since I'm using a web form to submit my data I can see how to keep the relations between all the tables with the new Team_Roster table current, but with the exception of the Players column, since they won't be filling that out right away, then when they go to add a new Team, say JV this time, then it throws the relations off for the PlayersFKID.
Sorry if I'm overlooking the obviously here, I'm extremely new to this, but I want to get it right. So I do really appreciate your input, and patience..