Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2008
    Posts
    12

    Question Setup of Schema for Bowling League Database

    I'm trying to create a database for my bowling league. I've found it quite difficult to try and come up with a design and still get the information I need. I think I've got something quite close to the mark and wondered if you could take a look and give me some suggestions.

    If you need more info to put the diagram in context then I'd be happy to provide.

    Cheers
    Rich

    Edit: I've realised this is useless without some supporting info, so I'll try to get some noted down and re-post this a bit later today.
    Attached Thumbnails Attached Thumbnails erd.gif  
    Last edited by huggiebear; 11-12-08 at 10:29.

  2. #2
    Join Date
    Nov 2008
    Posts
    12
    OK, here's the supporting info...

    A team consists of three bowlers.
    A match consists of six series' (one for each bowler of each team).
    A series consists of three games.

    Each bowler's score is logged in the score table (one row for each game) and is linked by the series_id to the series table. This allows me to tie up the scores with the bowler, team and match.

    I think this should work, but wondered if I need to split anything else out or if it's fairly well normalized.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    A bowler can only be on one team?
    You are storing "Bowler_ID" and "Opponent_ID" in a table? Does that mean you are going to duplicate the same record for the opponent, with the roles reversed?
    I'll warn you that modeling a game/sports type of database can be tricky, especially if you are tracking leagues and teams as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2008
    Posts
    12
    Quote Originally Posted by blindman
    A bowler can only be on one team?
    That's correct.
    Quote Originally Posted by blindman
    You are storing "Bowler_ID" and "Opponent_ID" in a table? Does that mean you are going to duplicate the same record for the opponent, with the roles reversed?
    Yes, that was the plan. It didn't seem right to me, but I couldn't think of another way to do it. Any ideas? That's why I added the score table, in order to remove any additional duplicate data in that table.
    Quote Originally Posted by blindman
    I'll warn you that modeling a game/sports type of database can be tricky, especially if you are tracking leagues and teams as well.
    I'm starting to realise that, but I'm sure I can handle it, just need to get the basics down.

    Cheers
    Rich

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Drop the Bowler_ID and Opponent_ID from your Series table.
    Drop the Score table. Replace it with a SeriesBowlers table where you store two records for each Series (one for each Bowler in the series). That record will include the Bowler_ID, their handicap, their score, etc.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2008
    Posts
    12
    OK, I think I've made the changes that you suggested.

    I've renamed the tables slightly so they reflect the data that's in them a little better.

    The series table contains the bowler, scores, and handicaps (the actual series date) and they're linked to the sub_match table which is in essence the match of two individuals, one from each team, and that in turn links to the match table which is the everall match of the two teams.

    See the revised attached document.
    Attached Thumbnails Attached Thumbnails bowling.GIF  

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If "Series" is the commonly used term, then I would stick with that rather than "sub-match".
    I would recommend you drop the "Team_Members" table if you are only going to allow bowlers to be in one team, and instead include Team_ID in the Bowlers table. Of course, this one-to-one relationship means that you will need to create a new database each year, or a new entry for each bowler if they switch teams.
    Having "Game_One_Score", "Game_Two_Score", "Game_Three_Score" in your Bowler_Series table is a little tenuous from the standpoint of normalization, but acceptable if there will ALWAYS be three games in each series (IE, that is one of the rules of bowling...is it?).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2008
    Posts
    12
    A series is the term for the total pin fall of three games. So that's why that table was renamed.

    There isn't really a term for when one player plays another, so I called it sub-match as it's part of the overall match of one team playing another.

    I was beginning to think that the team_members table seemed a little redundant and think I will remove it.

    Finally, there are always three games in a series and it's not possible to have less. If someone only bowls two (maybe they get injured half way through) then their average is used in the third game.

    Thanks for all this help.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by huggiebear
    There isn't really a term for when one player plays another, so I called it sub-match as it's part of the overall match of one team playing another.
    How about "Pairing".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2008
    Posts
    12
    Quote Originally Posted by blindman
    How about "Pairing".
    That'll do me

    Cheers
    Rich

  11. #11
    Join Date
    Nov 2008
    Posts
    12
    OK,

    Here's the re-jig and I think I might try sticking some data in it and running a few queries and see how it works out and how easy it is to get along with.

    Cheers
    Rich
    Attached Thumbnails Attached Thumbnails bowling.png  

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'm concerned about your Match<Pairing<Series tables.
    A match can have more than one pairing. OK.
    A pairing can have more than one series. OK.
    But will the individual bowlers within a single pairing change from on series to the next? What, exactly, is "Pairing" pairing up? I thought it was pairing two bowlers for a series.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2008
    Posts
    12
    Quote Originally Posted by blindman
    I'm concerned about your Match<Pairing<Series tables.
    A match can have more than one pairing.
    This is correct, a match will have three pairings.
    Quote Originally Posted by blindman
    A pairing can have more than one series.
    Partially correct, each bowler only bowls one series, but the pairing is to link the series' of two bowlers, so as far as the pairing table is concerened, it's a 1:m relationship.

Posting Permissions

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