0

I have a table that stores "Matches" these matches have the following columns: MatchId, TeamA, TeamB, Wager,Winner

I want to be able to make TeamA and TeamB return a list of players for each team. The number could be arbitrary for each one, is this possible or even practical? I cant think of any other way of designing this. Is there a better schema over all for this? The players are going to be UserId's and a user is not bound to a team, the team is only relative to the match.

EDIT: For instance, Player A, B, C, D, E, F

Match 1: 3v3 ABC vs DEF

Match2: 3v3 DAB vs FEC

The player should not be bound to any specific TeamId because it is only dependant on the match. A player can team up with anyone he or she desires in a match.

anthonypliu
  • 12,179
  • 28
  • 92
  • 154
  • Depending on how a player relates to either teams or matches, you likely need a Players table with a TeamID foreign key, or some form of mapping table that links players to matches in teams. – Adam Houldsworth Mar 15 '11 at 23:01
  • What exactly are you trying to achieve? You want a comma separated list of Team players for each team, in a single column? – RichardTheKiwi Mar 15 '11 at 23:02
  • Serialization is a process of converting memory objects into a stream of bytes suitable for writing on disk/transferring over network. Deserialization is the reverse. Given that information, how serialization is related to fetching data from SQL? – Roman Royter Mar 15 '11 at 23:12
  • I updated the question a bit so hopefully it makes sense now – anthonypliu Mar 15 '11 at 23:12
  • @OMG sure http://meta.stackexchange.com/questions/81753/ – RichardTheKiwi Mar 15 '11 at 23:39
  • @OMG Ponies (chest thunping again!) said: `You're not a mod, not even close, so don't waste your time telling me how things should be done.`, ha, ha, ha, I thought this was a community of peers? – RacerX Mar 17 '11 at 20:00

3 Answers3

2

A possible database schema could look like this: enter image description here

You can then select out of it like so:

SELECT M.*, P.*
FROM Matches M
JOIN MatchPlayers MP ON MP.MatchID = M.MatchID
JOIN Players P ON P.PlayerID = MP.PlayerID
WHERE M.TeamA_ID = MP.TeamID OR M.TeamB_ID = MP.TeamID

Excuse me if my SQL is rough around the edges or the schema is rubbish - it should likely, probably, hopefully, marginally work.

That particular SQL doesn't tell you the team the player was in, but the information is there to be had with some small changes.

For what you want, data that relates to other data, serialization is not the answer - or it's not what you think... I sense a terminology mismatch there. Read up on using databases from .NET - there are lots of great tutorials on the web. Also read up on relational database design, again web sources are great for this.

Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
0

Two approaches:

1: Use XML for the TeamA, TeamB columns - that way you can create the full team lists in a structured string thats easy to handle in your app

2: Use more tables: A more comprehensive schema might be: (This is not intended to be comprehensive and may even be over the top, but its an illustration about how you might want to be thinking)

Match MatchID Name Matches that take place

Team TeamID MatchID IsHomeTeam Name Teams that play in a match

Player PlayerID TeamID Name All players for each team

Edit: This is a simpler version after clarification of the requirements

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
  • Hey thanks so much for the quick response, but the thing is i dont know if its practical to have a Team table because the user will not be bound to a team.. so for instance every match is a new team. I could be playing with some players one match, and then another player in another match – anthonypliu Mar 15 '11 at 23:15
  • Regardless, every match has 2 teams. You could abstract this from the UI, but ultimately the data model fits. – Slappy Mar 15 '11 at 23:25
0

I am assuming you are using .NET to consume your data. You can create a team class with a Players property as a List. Place a [Serializable] attribute on the class.

Use a serialization method to convert the team to a string. Write the string the the database.

Use a deserialzation method to deserailize the team back into the in memory object.

See this article for a primer: http://msdn.microsoft.com/en-us/library/90c86ass(v=vs.71).aspx

Personally I would use more tables. Have a Team entity with a 1-* relationship to a Player entity. Have the Team FK in your Matches table.

Get the players for a match by:

SELECT P.PlayerName
FROM Matches M (NOLOCK)
JOIN Team T (NOLOCK)
  ON T.TeamID = M.TeamA_ID
JOIN Players P (NOLOCK)
  ON P.PlayerId = T.PlayerId

UNION ALL

SELECT P.PlayerName
FROM Matches M (NOLOCK)
JOIN Team T (NOLOCK)
  ON T.TeamID = M.TeamB_ID -- NOTE TEAM B
JOIN Players P (NOLOCK)
  ON P.PlayerId = T.PlayerId
Slappy
  • 4,042
  • 2
  • 29
  • 41