Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    10

    Doubt designing a database

    I'm creating a system where users of it can create football teams and introduce players in these teams. One player can belong to more than one team. I would like to know if the solution I have thought is the best one, database is implemented with MySQL.

    Using three tables, one to store the players name,a second one which links these teams with the players. And the third one to store the football teams name.

    Names of the columns in table "players" are:

    Id(int) player_name(varchar) team_relation(int)


    Names of the columns in table "relations":

    team_relation(int) team_name01(boolean) team_name02(boolean)


    Names of the columns in table "teams" are:

    Id(int) team_name(varchar)

    If I create teams Arsenal and Celtic and introduce the player Bob in the two teams, tables "players", "relations" and "teams" would be:


    #Id(int) player_name(varchar) team_relation(int)
    01 Bob 01

    #team_relation(int) Arsenal(boolean) celtic(boolean)
    01 true true

    #Id(int) team_name(varchar)
    01 Arsenal
    02 Celtic

    So, is this the best solution? Each time I create a fotball team I have to add a colum in table "relations"

    Tahnk you very much,
    Frigo

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how can a player belong to two teams????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Posts
    10
    Because system is supposed to work like this. I have found a different solution.

    One table storing each team you create:

    Id Team_name
    0 Arsenal
    1 Celtic

    One table storing players
    Id Player_name
    0 Bob
    1 Peter

    as many table as teams you created, for example "table_arsenal" (In case: Bob belongs to Arsenal)

    Id Player_Id
    0 0

    "table_celtic" (Bob and Peter belong to Celtic).
    Id Player_Id
    0 0
    1 1

    What do you think?

    Thank you very much in advance,

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    a table per club is frankly a pants idea
    why
    each and every time some wants to add a new team you have to create a new table and almost certainly modify the application code.

    so the solution would be to have an entity (table) for teams, and then the associate a player with a team, look up intersection tables. if a player could only play for one team once you could have TeamID & PlayerID as the primary key, however your rules stop that. arguably you could add the dates the player played for that team to the above PK, however thats messy, So Id suggest retaining the autonumber ID column as your PK but havign 4 columns PlayerID (Foreign Key to players), TeamID (FK to teams), DateJoined, DateLeft.

    you will need to build in some code in the applciation to make certain the dates dont' overlap. you need to ensure
    1 dateleft isn't before datejoined
    2 the dates don't overlap ie registered a date left Team A as after the date joined Team B
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2011
    Posts
    10
    healdem ,thank you very much for you response. It's very useful, I understand it now. My two earlier solutions, creating a column for each club and creating a table for each club weren't ok.

    Players and teams are an analogy of my system. It was my mistake, because it has been confusing instead of making it clear. The point is player can be playing for different teams at the same time.

    Using your solution. What's up if I say that a player can play for all the teams and then create a new team? this new team won't have this player, player should join the new team automatically when team is created, so I have to add somewhere in the database that a player plays for all the teams. What do you think?

    Thank you very much in advance,
    Frigo

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    how can a player play for more than one team at any one time?
    if your analogy isn't a good representation of the real world then its not very useful. if it masks the real world problem then it becomes part of the problem itself

    if you want to enroll a player for a new team then do that as part of your new player registration. wheter you implement that as a stored procedure (ie adding a new player triggers an automatic registration in the playersteams entity) or do that as part of your application logic, IE after inserting a new row then insert a new row into each team.

    it does be the question of why you would need a player / team entity if a player is a member of both / all teams automatically.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2011
    Posts
    10
    I don't know if I explained to you right.

    Players can play for any team, several teams, all teams or even not playing for any team. My database is strctured like this now:

    entity players:
    Id Player_name
    01 Bob
    02 Peter
    03 Stephen
    04 Michael

    entity teams:
    Id team_name
    01 Arsenal
    02 Celtic
    03 Manchester
    04 Fulham


    entity relations:
    Id_player Id_team
    01 01
    01 02
    02 02
    04 01
    04 02
    04 03
    04 04

    Michael plays for all teams. If somebody adds a new team in the teams entity (05 Liverpool), I have to check somewhere which players are playing for all teams and now there is nowhere to do that.

    Solution would be, adding a second column in players entity storing a boolean value which says if they play for all teams or not. And when somebody adds a new team, check this values to add players in this new team. Is it a good solution?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    ...sorry you've lost me
    Your analogy is masking the problem
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2011
    Posts
    10
    Let me appologise.

    Without analogy:

    There are websites and advertisments. Websites contain advertisments. In my analogy websites are football teams and advertisments are players. Advertisments can be added to one, several or all websites, even not to be added to any website.

    entity advertisments:
    Id advertisment_name
    01 microsoft
    02 sony
    03 ericsson
    04 mercedes

    entity teams:
    Id website_name
    01 web01
    02 web02
    03 web03
    04 web04

    entity relations:
    Id_advertisment Id_website
    01 01
    01 02
    02 02
    04 01
    04 02
    04 03
    04 04

    Microsoft advertisment is in all websites. If somebody adds a new website in the websites entity (05 web05), I have to check somewhere which advertisments are in all websites and now there is nowhere to do that.

    Solution would be, adding a second column in advertisments entity storing a boolean value which says if they are published in all websites or not. And when somebody adds a new website, check this values to add advertisments in this new website. Is it a good solution?

    Sorry for the misunderstanding.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by frigopie View Post
    There are websites and advertisments.
    ahahahahahaha

    arsenal and celtic, indeed

    no wonder you got crappy answers, when you ask crappy questions

    please, don't waste people's time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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