Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2013
    Posts
    3

    Unanswered: Helping designing a database for a website about baseball

    I'm doing a project for learning programming in a python language with the django web framework. I don't know much about database and I don't really have to worry much because the django webframe handle almost everything. But, I do need to design the database and this is where I need help. I want a database for the baseball player bios like where they born, age, weight etc.. and for the stat, like how many hits, doubles, homer runs, etc... Base on my research I came with this design and I would like to know if it looks good, if not what should I change. This is what I came out :
    Code:
    Table Player: 
                      player_id 
                      mlb_id 
                      name 
                      last 
                      middle 
                      jersey_number 
                      weight 
                      height 
                      birth_date 
                      birth_city = foreignkey (Cityborn) 
                      birth_country= foreignKey(Countryborn) 
                      pro_debut_date 
                     Primary_position = foreignkey(Position) 
                      team_name = foreignKey (team) 
                      throws_arm = foreignKey(Throws) 
                      bats_arm = Foreying Key(Bats) 
    
    table Cityborn: 
                      name: 
    
    
    Table Countryborn: 
                      name: 
    
    table Position: 
                      name: 
    
    
    table team: 
                      name: 
                      shortname: 
                      city: 
                      state: 
    
    table Throws: 
                      arm: 
    
    table Bats: 
                      arm: 
    
    
    table seasonstat: 
                      player = foreignkey(player_id) 
                      year: 
                      hits: 
                      doubles: 
                      homeruns: 
                      rbi: 
                      average: 
    
    table seasontotal: 
                      player = foreignkey(player_id) 
                      year: 
                      hits: 
                      doubles: 
                      homeruns: 
                      rbi: 
                      average:

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    several of those tables are not necessary: cityborn, countryborn, position, throws, bats

    take for example cityborn... if you want the player's city to be a foreign key reference to the cityborn table, this means that you have to add the city to the cityborn table first, before you can add the player

    more pragmatic would be simply to have the player's city be a text attribute, and forego the data integrity check that the foreign key provides
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2013
    Posts
    3
    Quote Originally Posted by r937 View Post
    several of those tables are not necessary: cityborn, countryborn, position, throws, bats

    take for example cityborn... if you want the player's city to be a foreign key reference to the cityborn table, this means that you have to add the city to the cityborn table first, before you can add the player

    more pragmatic would be simply to have the player's city be a text attribute, and forego the data integrity check that the foreign key provides
    So basically instead of having let say a table with bats, just have a regular field under the player table? What about the seasonstat and seasontotal

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by amb1s1 View Post
    What about the seasonstat and seasontotal
    presumably these tables are populated from single game stats?

    while aggregates can always be re-computed from detailed records, it often helps performance to store aggregates separately

    btw, what's the difference between those two tables? they look identical
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2013
    Posts
    3
    Quote Originally Posted by r937 View Post
    presumably these tables are populated from single game stats?

    while aggregates can always be re-computed from detailed records, it often helps performance to store aggregates separately

    btw, what's the difference between those two tables? they look identical
    The stat are from a single season total. So for a player with a 12 years will have 12 rows with each stat total.

    The first is single season and the second one is the total of career. The second one is going to be just one row and it is the sum of all example 12 years.

Posting Permissions

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