Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    My first Database --- Basketball Championship

    Hi! I'm doing my first database project! It's a database about a basketball championship. In this thread I'll tell you my thougths about how to build it, step by step, but I'll need your help, because I'm a beginner!

    Firstly let's create the tables...

    Tables
    Team: team_id,name,foundation_year,colours,logo
    Staff: staff_id,first_name,middle_name,last_name,birthdate,nation ality,photo
    Player: palyer_id,number,position,height,games,points,rebounds,assi sts
    Rest_staff: rest_staff_id,position
    Arena: arena_id,name,address,city,capacity,photo

    Relationships between the tables
    Team - Staff: 1-N
    Staff - Player: 1-1 (descendent obj.)
    Staff - Rest_staff: 1-1 (descendent obj.)
    Arena - Team: 1-N

    What do you think so far?

    PS: Sorry for my english.....
    Last edited by George Raptis; 05-22-08 at 11:39.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your english is fine

    your table design is pretty good too

    what is rest_staff for? coaches? towel boys?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    Thanks!
    The rest_staff table is for coaches,managers,physiotherapists etc

    I've added a new table, Arena. (see the updated first post)

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Avoid the 1-1 relationships.

    Try a schema with the following entities:
    Teams
    Participants
    Roles
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not a fan of sub and super types blindman?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dang, had to go out today and missed my opportunity

    my reply was going to be "blindman, do a search for supertype/subtype"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump
    Not a fan of sub and super types blindman?
    Nope. Nope.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I thought it would fit well with your 3NF and OOP analogies.

  9. #9
    Join Date
    Apr 2008
    Posts
    5
    I'm back! I've been confused a little bit! What do you suggest? I did these 1-1 relationships because I was thinking that if I have to add a coach, then the <points>, <games> ... fields are going to be filled with NULL value. That's the way I was thinking! What do you say?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I like this approach. Blindman will prefer the nulls I suspect. I would say, however, that some of the attributes you have against players might well be derived from tables you use to model the games that are played.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by George Raptis
    I did these 1-1 relationships because I was thinking that if I have to add a coach, then the <points>, <games> ... fields are going to be filled with NULL value.
    Horrors!
    I was down at the data station this morning and was dismayed to see that data fields are topping $4 per gallon. Perhaps it is good that you do not want to waste them on NULLs.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Apr 2008
    Posts
    5
    Hi! The image I've uploaded shows what I've done so far. I want to add the points/rebounds/steals etc but I've stuck!!! I don't know where to add them? I was thinking to add a N:M relationship (players-game), so I'd add them in the table that will be created. So let's say that I want the total points for a team, I'd select the sum of the points that every player has scored. But if one player of the team move to another team, I'll have problem! Another thing that I was thinking is to add the points in the "Team_has_Game" table, but I will not know the individual stats for every player. What do you suggest?
    Attached Thumbnails Attached Thumbnails 22.png  

Posting Permissions

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