Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2010
    Posts
    21

    HS Baseball Database Design Help

    Hello,

    I've created a baseball database for a local high school that will be updated from a web form on a web site.

    The site will allow the team to show a Roster, Game Schedule and Stats within a Season. The team is pretty much made of up 3 types, Varsity, JV, and Freshman. A player can be on more than one team and I would like to be able to keep career stats through the course of their time in school.

    I want to be able to record and display stats, not just for a Player, but for the indiviual games as well for each team.

    I believe I've got most of that, however I'm pretty much stuck at the point of recording the Runs per Inning. If you visit any baseball site you'll see the score show number of Runs in each Inning, obviously some Innings have non so I didn't think I should create a "Inning" table with columns label 1-9 because several of those could be null so I'm not sure excatly how to record and display that info.

    I appreciate any thoughts or opinions, this is all pretty much all new to me, but I have done some research, I hope it shows.. :-)

    Thanks,
    Bob
    Attached Thumbnails Attached Thumbnails BaseballDB6.JPG  
    Last edited by bobmc; 07-28-10 at 22:33. Reason: Changed Image

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    First of all, I've been to varsity games with thirteen innings, so I wouldn't stop at nine. You need to read up on LEFT JOIN and Coalesce() to see one way to handle this problem, you are definitely on the right track.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2010
    Posts
    21
    Thanks for the reply and you are correct I wasn't going to stop at 9 innings but just trying to get the thought process of having 9+ plus columns out there that could end up having a lot of null vaules, over time.

    I looked up the Coalesce() expression and see where that would be very useful if I create an Innings table with, say, 20 columns to represent the InningNumber. But that just doesn't seem very efficient because of all the null values that would exist.

    However if that's my best option I'm fine with going that route.

    Any other thoughts on recording the score per inning...Anyone..

    Thanks,
    Bob

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    how about an inning table that has a column for 1 inning? You add innings as rows til you get to the last one, game called for rain, so you didn't get 9 in, game tied so you keep going for more than 9. It wouldn't matter, since there is one row per inning. You could then have another table under it to keep track of stats, such as strikeouts/walks/hits/etc....
    Dave

  5. #5
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Just a thought off the cuff:

    Pull Team_ID out of the Player table, you're already storing that in the roster table, and this way you won't be repeating players just to add them to different teams.

    Then make an Innings table using the Game_ID, Player_ID, PlayType_ID (would probably want to make a lookup table for this if wanted, i.e. groundout, flyout, single, rbi, etc), and Inning#. This way the game can be as short/long as it would like to be and if you don't want the PlayType lookup table you wouldn't need to record anything for innings that there are no hits.

    Personally I would use a surrogate key as primary in here due primarily that a single player can be at bat more than once in any given inning so using a concatenated key could be problematic...unless you wanted to add an "AtBat" field to track whether it was the first,second, etc time at bat for that inning.

    Just a random idea after a quick look at your relations,

    Sam

    Edit: D'oh! Beat to the punch
    Last edited by SCrandall; 07-29-10 at 14:04. Reason: I type too slow
    Good, fast, cheap...Pick 2.

  6. #6
    Join Date
    Jun 2010
    Posts
    21
    Thanks for the reply gents...

    Let me follow up so I think I can try to understand a bit more..

    Looking at my pic, you'll see I have a GameDetails table, which is where I orginally thought I'd be recording this info to. I can change that to Inning, no problem, but let me map this out so I'm on the same page.

    I should have an Table such as this:
    TableNameID
    GameFKID
    InningNumber
    Runs

    This way only the Inning in which a Run happens will be recorded for a specfic Game, correct? Also since I want to record the opposing Team runs as well I should probably add in the FK for the Opposing team, correct?

    Also I'm recording the stats to the cumlitivestats table, however its not recording stats per player, "per Inning", per Game, its there for overall stats for a Game, Team, and a Player. I have season in there as well because I want to keep a career stats during the time they were in school.

    Let me know if I'm getting closer!

    Thanks
    Bob

    P.S. @SCrandall - Thanks for the tip on the TeamID, I don't want to have the coach to enter in a player's details a bunch of times just because his on more than one team.

  7. #7
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Yep, looks like you're getting there.

    A few things now that I've printed out your diagram.
    (These are my opinions based on how I'm understanding your diagram and nothing more than my opinions<g>)

    Ditch:

    The OpponentTeam table, that information should be stored in the general team table, then within the Game table use that key twice, i.e. HomeTeamID/AwayTeamID.

    The CumlitiveStats table, it looks like it's going to hold nothing more than derived values, let your queries take care of that. Don't store calculated data unless it's absolutely necessary, with a correct relationship between tables you'll be able to track a player's entire career just by his ID, then you'll have various grouping/sorting options available.

    Then within your Innings table I would have your:
    TeamRosterID (which will designate the player, and can be used to pull season/team info if needed)
    InningNumber
    GameID (Designates the individual game, which will pull your season/teams information)
    Runs

    I think I would also move the JerseyNumber, PlayerImage, Grade, Height, Weight, and GPA fields out of the Player table and put them in your TeamRoster table, not very often players wear the same shirt/number through their various seasons/teams, same with any other characteristics that are likely to change from year to year.

    I would also change the relationship for the PlayerPosition table to be related to the TeamRoster table, in case someone plays first base on one team and center field for another team the next season.

    Like I said, just some thoughts...I have a tendency to over normalize my dbs. I think you are well on your way to having a good db scheme, you've got your desired fields/entities mostly figured out...now it's a matter of putting the pieces together so they flow and give you the date you are looking for.

    Sam, hth
    Good, fast, cheap...Pick 2.

  8. #8
    Join Date
    Jun 2010
    Posts
    21
    SCrandall..Thanks for your suggestions! I'm a bit confused on a few points so let me go over them. This is my first database and has basically been put together via suggestion and opinions I've gather here and elsewhere so I want to make sure I get this as close to "right" as I can before I actually start coding anything.

    Ditch:

    The OpponentTeam table, that information should be stored in the general team table, then within the Game table use that key twice, i.e. HomeTeamID/AwayTeamID.
    The Team table probably should have been labeled as TeamType, because it will represent the Type of teams within the School, such as Varsity, JV, and Freshman. The actual "Team", for example: Johnson Central Baseball, will be identified via the domain name, ie, URL. I wanted to give them and myself the flexiablity to name the team type something else if they so chose. I say myself cause if I get this right I can offer to other sports with little adjustment on my part. But back to your suggestion. Since the Team table doesn't actually represent the overall Team would you still suggest moving the OpponentTeam to that table, and if so can you go into a bit more detail on the use of HomeTeamID and AwayTeamID. I'm confused on those because there is no Home or Away Team defined until the Game is scheduled.

    Then within your Innings table I would have your:
    TeamRosterID (which will designate the player, and can be used to pull season/team info if needed)
    InningNumber
    GameID (Designates the individual game, which will pull your season/teams information)
    Runs
    I'm thinking I'm understanding this but shouldn't I have something in that table to record the Opponent's runs, such as a FK to the OpponentTeam table, if that table is kept based on the first suggest.

    I think I would also move the JerseyNumber, PlayerImage, Grade, Height, Weight, and GPA fields out of the Player table and put them in your TeamRoster table, not very often players wear the same shirt/number through their various seasons/teams, same with any other characteristics that are likely to change from year to year.
    I agree that these fields would change yearly, but if I don't want to maintain that information, what Number they wore, GPA, Height, etc in previous seasons or on previous teams would I actually need to make that change? My thought process is that when I pull a career stats report for Player X and load it to a web page view, I'm just looking to display his Cumlitive stats over a period of time. I wouldn't, or currently do not plan, to include what his GPA, or Number was during that period.

    I would also change the relationship for the PlayerPosition table to be related to the TeamRoster table, in case someone plays first base on one team and center field for another team the next season.
    I can see your point here, I do want to know what position the Player played on which Team during the differnt Seasons. But just to make sure I understand you, I do keep the PlayerPostion table, (because a player can play more than one position), I just add the relation for PlayerPosition and remove the Player relation, for the TeamRoster table, correct?

    The CumlitiveStats table, it looks like it's going to hold nothing more than derived values, let your queries take care of that. Don't store calculated data unless it's absolutely necessary, with a correct relationship between tables you'll be able to track a player's entire career just by his ID, then you'll have various grouping/sorting options available.
    The cumlitiveStats table will not actually hold calculated values, well except those that have to like Batting Avg, or Pitching ERA. But that would be calucated by a SP and added based on the other stats entered. Actually it may be better to calucate those values out when the page view is loaded vs storing it, not sure on that one. But the idea behind this table was store stats from the different teams for each game for each player during a season. So when the game is completed the coach will select Game 1, the player and add in 5 At Bats, 2 HRs, etc. then the next game he'd select Game 2, the player and add in 2 At Bats, 1 Double, 1 Single, etc.. Then I would use a SP to sum up the totals for a Player, or Game, or even a Team when its being displayed on the web page view.


    Thanks again for your suggestion, I can see the light at the end of the tunnel now...I think.. :-)

    Thanks,
    Bob

  9. #9
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Good morning,

    Looks like you're getting the hang of it for sure.

    Ok, here we go, it's early so bear with me, I'm not usually very coherent until I've had my morning coffee.

    The Team table should hold all of the teams information. Then I would definitely have a TeamType table related in with the type (good idea there). Then in the Game table, you would hold two FKs (HomeTeamID, AwayTeamID) each relating to the Team table (to avoid a Cartesian join, when it comes time to run your queries you'll need to actually make it appear to be two tables by aliasing one on the joins, but, we'll cross that bridge when we get to it). Some of the benefits of doing it this way are, consolidation of information, a team is a team is a team...the system isn't going to care less if it's the home team or away team. Less directions to go to input the same information. If you kept your opponent team table, you are basically limiting the DB to only working for one team. With only one Team table, *any* of the teams could theoretically get their stats from the db, as long as their game information had been entered.

    Team information wouldn't be relevant until the game information was entered into the Game table, which won't be entered until the game is scheduled, so no worries there.

    With the innings table, as long as you put in the TeamRosterID, which will pull the players team, season year, and the player's information, and the GameID to tell it which game the run was scored in, it won't matter which team they are on, the system will have all of it's information available to tell whether they were on the home or away team, so no need to specify with extra fields.

    As far as player stats, if you don't want to keep that information throughout the years, then absolutely keep that information in the Player table and just edit it when necessary.

    If I'm reading your question on the PlayerPosition table correctly then yes you have it correct. Remove the link between the Player/PlayerPosition tables and then link the TeamRoster/PlayerPosition tables.
    I would add the TeamRosterID to the PlayerPosition table, that way if a player goes from playing 1st base to playing right field in the same season, both those positions are saved.
    If you only want to store what position they ended the season with/or are currently playing then drop the PlayerPosition table completely and put the PositionID in the TeamRoster table.

    With what you've written for the CumlitiveStats table, I would definitly drop it. A query can easily pull that sort of information. And depending on what you wanted to track for stats, you can always easily add more raw stats into the Innings table, ie. Errors, strikeouts, foulouts, etc. It all really depends on how far you want/need to go with the detail of your information.

    I'll try to build a mock diagram up for you today, it might help if I could actually show you what I mean rather than expect you to picture it all in your head.

    Sam

    Edit: Added a mockup of the diagram.
    It's not polished, but, should give you a rough estimate of what I would start with.

    Team and Team_1 are actually the same table, I'm just demonstrating the relationship there for you, one copy of the table for each of those fields.

    You'll also notice I used surrogate keys, you can use your concatenated/natural keys if you wish, I just used those for demo purposes (altho to be honest in the real world I generally use surrogate keys in my dbs).

    2nd Edit: Forgot to show a Location table in the image. Reason for the location table it reduces location name input errors (such as typos...it's amazing how many different ways people can find to spell the same darn thing!) and when used as a lookup table it should help reduce input time.
    Attached Thumbnails Attached Thumbnails BBall.bmp  
    Last edited by SCrandall; 07-30-10 at 11:01. Reason: Add image
    Good, fast, cheap...Pick 2.

  10. #10
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    LoL this is what happens when I get an idea stuck in my head.

    I've modified the design a bit based on your comment that you'd like to make this available to any of the sports with little effort on your part. This will allow different sports and your team types (Girls Varsity, Girls JV, Boys Varsity, etc, etc).

    Again this isn't a polished idea, but hopefully it'll give you some ideas if you decide to go along that path.

    "If it's worth doing, it's worth overdoing" is apparently my motto when it comes to programming. I'm not sure how far you want to be able to take your application so I'm just throwing ideas out there for you...

    Sam
    Attached Thumbnails Attached Thumbnails AnySport.bmp   AnySportRevised.bmp  
    Last edited by SCrandall; 07-30-10 at 12:01. Reason: Add a new image, corrected some mistakes
    Good, fast, cheap...Pick 2.

  11. #11
    Join Date
    Jun 2010
    Posts
    21
    Sam..Thanks! I'm reviewing your Pics, and Suggestions. I still trying to wrap my head around a couple of things but with your explainations I can see several of your points and they are right on target. Give me some time to continue to review and I'll follow up a bit later, its a lot to take in.. :-)

    Thanks Again!!
    Bob

  12. #12
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    No problem, glad to help.

    Sam, have a good weekend.
    Good, fast, cheap...Pick 2.

  13. #13
    Join Date
    Jun 2010
    Posts
    21
    SamOk heres some initial thoughts..

    The team idea makes sense now, and I moving in that direction along with a TeamType table.

    You'll also notice I used surrogate keys, you can use your concatenated/natural keys if you wish, I just used those for demo purposes (altho to be honest in the real world I generally use surrogate keys in my dbs).
    Im not really following you here, I cant really tell whats surrogate and what natural in the diagrams, it could just me though. Are you referring to the tables that consist of all IDs, such as the TeamRoster table, are all those are consider surrogate keys?

    I noticed was that the Season relation for the Game Table and TeamRoster Table are not in your diagram. Im wondering if that is by design because the Season has relation in Teams Table, would I not need a relation to the other tables? I dont lose my association for being able to review previous Season Teams, or previous Season Games by doing this, do I?

    I saw where the JerseyNumber was moved to the TeamRoster Table, that makes since, because the player may not have the same number if his playing on two or more teams in the same season.

    Id like to try and explain the CumlitiveStats table again, if I could explain it right you might see what Im wanting to keep it around. Since Im dont know of all the specific Stats a coach, will want to record I created the StatsType and StatsCategory Tables, both of these tables are list tables. The Category table breaks down the Stat categories: Pitching, Batting, Fielding, and Catching. The Types Table is a list of about 30 or so stats for each of those categories, At Bat, HR, Single, Double, Errors, etc, and as you can see it has a relation to categories. I want give the coach to be ability to go through the list of categories and/or individual stats and select what they want to record, which will set the Show in List value to True. Then when they go to enter in the stats after a game, only those values that are True will show up to be recorded. I also want to make sure if they change their mind at any point and want to record less or more stats they is available as well.

    Thanks again for your help!
    Bob

  14. #14
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    A surrogate key is typically something like an autonumber field, where the database assigns a primary key value, rather than using a natural key which relies on unique data input by the user. The tables that are nothing more than a bunch of foreign keys are typically called Junction tables.

    The change of the Season relationship was intentional. You won't lose a thing, actually it makes it so the team only "exists" for that season. Should actually make looking things up a bit easier.

    I'm glad you saw the Jersey change and understood the reasoning for it.

    If I'm following correctly, everything you are looking for in your cumulative stats table would already be in your Periods table, using the PlayType to specify what each player did and then your queries would generate usable data for you. i.e.

    Code:
    A player has a single (Period: 3, PlayType: Single, Quantity: 1) and a strikeout (Period: 3, PlayType: StrikeOut, Quantity: 1) in the same inning, your AtBats would be 2.
    
    A player strikes out twice (Period: 6, PlayType: StrikeOut, Quantity: 2). 
    
    A player catches the ball for an out. (Period: 9, PlayType:  Catch:Out, Quantity: 1)
    It's very similar to what you had, it just doesn't store calculated data, it's designed to let your queries/reports do that for you, and you're only entering the data once, versus once in the Period table and again in the Cumulative
    Stats table.

    When it comes time to do your reports is when you can make a form allowing them to pick which stats they'd like to track, using the PlayType table as your lookup for the criteria.
    You can also very easily put the "ShowInList" boolean field in the PlayType table if you are running into things that the coaches don't want to track or want to bring back in. I almost always use that type of field in every table I make as I can't allow my users to ever physically delete things from my databases due to work restrictions, so I agree that's an important field to utilize.

    Sam, hopefully I explained it better this time, if you have any questions just shout
    Good, fast, cheap...Pick 2.

  15. #15
    Join Date
    Jun 2010
    Posts
    21
    The bulb is on dim, but its starting to turn up!

    We'll get back to the surrogate key in a bit, I'm still a bit confused on that one, plus I'll do some Googling..

    The change of the Season relationship was intentional. You won't lose a thing, actually it makes it so the team only "exists" for that season. Should actually make looking things up a bit easier.
    Sorry, but I'm a bit confused on "only exists for that season."

    Also thinking about it I'm not sure how when a new season is created that I will keep the same Teams in that new season. If I simply update the Team table with the new Season ID then I'd lose the ability to see Teams in a previous Season, right? Maybe I over analyzing this and missing something simple..

    Thanks for the code example, I believe now I know where some of the confusion is coming in from. With your method I'd be recording stats Per Player, Per Game, Per Period, ( or Inning in this case). I was actually trying to get away from doing that. I just wanted to record stats Per Player Per Game. I can see where some super stats person would want to know that information, but my thought process was/is, this is for High School, (not the best example since kids go on to college, but if I was to use this at a Middle School level, or even a local community little league the coach would have to spend to much time inputting all those stats per period. With 9 players, assuming there are no subs during the game, and they play 9 periods. That would be like 81 submits, right? Of course now that I think about it I guess it kind of depends on how I setup the submit form too..ummm

    Also the cumulative stats does not hold cumulative totals, I should have probably picked a better name. Oh, wait.. I think I see what you mean by cumulative totals, the cumulative totals for the combined periods in a game. Is that what your referring to? I guess in that case you'd be right, it would be cumulative totals. Which was sort of my thought process for that table, to record all the stats Per Player, Per Game. So if John Smith had 3 At Bats, 1 Single, 1 HR, and 1 Strike out for the entire game the coach would just have to select the player, the game and enter that information under the corresponding Stat Type. Then basically the same thing for Game 2, 3, etc. When I show the stats for the page view I would use a SP to calculate the totals from all the games for that season.

    Thanks again for you help..It's really help me get a better understanding..

    Bob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •