CODEWITHSUNDEEP
sqlsql-servermany-to-many
BenSellars
BenSellars

Reputation: 79

SQL many-to-many design

OK, so I will probably get slaughtered for being lazy and not researching this myself - but I just can't get my head around it and it is making my brain hurt. And I am sure for some of you guys out there it is a pretty basic problem.

I have one Excel table with football (soccer) players and their attributes...

Player Table

and another with six-a-side football (soccer) teams, with six players in six positions.

Teams Table

I want to be able to link the two tables so that I can make aggregate team attributes based on the player attributes and then run queries on the qualities of the teams (i.e. best passing teams, most aggresive teams etc).

In Excel I would just do a vlookup from the player table into the teams table and then add up various combinations of the attributes of the players.

However in SQL it seems complicated - i.e. each team has many players, any one player can be in more than one team and any one player can play in a number of different positions. Which position the player plays in affects the weighting it contributes to the team attributes (i.e. a defender would contribute less to the teams creativity than an attacking midfielder, and more to the teams tackling ability than a forward). I think I need an intermediate join table or maybe several? Or maybe it is just simple after all?

Does anyone have any suggestions?

thankyou

Ben

Upvotes: 1

Views: 77

Answers (2)

BenSellars
BenSellars

Reputation: 79

The missing piece in the jigsaw in my mind was the composite index in the join table (provided by S Karras). I can now take this forward and see how it progresses.

Upvotes: -1

S.Karras
S.Karras

Reputation: 1493

If I get your question, the answer is the following. You will have a table for players (PlayerID, PlayerName, PlayerAtribbute1,...), a table for the teams (TeamId, TeamName, HomeTown,...) and an intermediate table for the connection of the two (PlayerId, TeamId, Position,...). The last intermediate table is used in SQL and database design to represent many-to-many relationships.

If you go with this approach, you should also index PlayerId in the Players table, TeamID in the Teams table and have a composite index on (PlayerId, TeamID) in the intermediate table and you should be golden.

Upvotes: 3

Related Questions