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

how can a player belong to two teams????

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,

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

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

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 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?

...sorry you've lost me
Your analogy is masking the problem

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.

ahahahahahaha

arsenal and celtic, indeed

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

please, don't waste people's time