Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Tim
Starting Member
392 Posts |
 Posted - 2002-09-02 : 21:11:09
|
| Trying to make a good design for storing tennis match results. eg: graz df robvolk 6/0 5/6 6/3 on 1st Sep 2002merkin df graz 6/3 3/6 6/5 on 2nd Sep 2002Need to produce query of all matches played by certain person. I always run into a catch22 where I need to either query for player *OR* opponent, or else create two records for each match. Both ideas are less than pretty. Have a quick think about it and you'll see what I mean.I don't think these guys got it right either.Would appreciate any suggestions...----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-09-02 : 22:19:03
|
| I took a look at the link you provided and I agree with you - not a very compelling design.I don't follow you though on the query difficulty. I see four obvious entities involved: sets, matches, teams, and players. A team has more than one player if it is a doubles match. A match is represented by one row in the Matches table. What difficulties have you encountered?Jonathan{0}Edited by - setbasedisthetruepath on 09/02/2002 22:19:36 |
 |
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-09-03 : 00:17:50
|
| Let's assume only singles, since doubles is the same case, just using teams not individuals.If a match is represented by a single row, it needs two attributes which represent the two players - let's call them 'Player' and 'Opponent'.So if you want to retrieve all matches for a certain player you have to do something like:SELECT MatchPlayer = @player, m.MatchDate,FROM Matches mWHERE m.Player = @player OR m.Opponent = @player That's not so bad on it's own, but then if you want to know the selected player's opponent in the match it starts to get messy:SELECT MatchPlayer = @player, MatchOpponent = CASE WHEN m.Player = @player then m.Opponent ELSE m.Player ENDm.MatchDate,FROM Matches mWHERE m.Player = @player OR m.Opponent = @player It's getting harder to read, but still I can live with it.Now I want the score and the result as well. Did the player defeat the opponent or vice versa? This requires the table 'Sets'. It stores one row per set played in the match. The attributes are Match, Set, PlayerGames and OpponentGames.To determine the match result needs subqueries now - one to format the scores as a single string (eg: 4/6 6/4 6/4 6/7 10/8) and one to determine the overall result.Another alternative is to denormalise the design so that the score and result are stored on the match table. Easier to query, but now I have the problem of keeping them updated.Does that help explain? Basically I am looking for any better ideas that would make the queries simpler and still keep the database normalised.This is an example (I think) where if I take it to 3rd normal form or higher, I will violates "graz's law" .... "If it's hard to get the data out you are not storing it right"Edited by - tim on 09/03/2002 00:24:22 |
|
|
|
shalini
Starting Member
3 Posts |
Posted - 2002-09-03 : 07:29:43
|
| 1. you could just retrieve both Player and Opponent names in the first query instead of MatchPlayer = @Player obviously the player will be one of them.2. You can add 2 more field to match table viz., PlayerSets and OpponentSets and store the number of sets won by them respectively. If Playersets is greater than OpponentSets then winner is Player or vice versa.Lastly you should not be just thinking in terms of Queries. By using stored procedures, temporary tables you can achieve what you want. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-09-03 : 09:30:06
|
quote:
If a match is represented by a single row, it needs two attributes which represent the two players - let's call them 'Player' and 'Opponent'.
Calling the second attribute 'Opponent' complicates the issue, and I think that's the base source of your hangup.In tennis there's no stateful occurence of a 2 player match, i.e. x vs. y is not different from y vs. x - unlike football, for example, where there is a distinction, namely which team is the home team.So you might want to try a materialized view on the matches and sets tables that gives you that stateless look.select player1 as player, player2 as opponent,...unionselect player2 as player, player1 as opponent...Jonathan{0} |
|
|
|
|
|
|
|
|
Stumper (for me anyway)