1

I was wondering what was the better approach for my database/table design. As show in the picture, i have players who play a match. One player plays multiple matches and one match is played by multiple players, so it is a n:m relation. This could result in thress tables player(id, firstname), player_to_match(playerid, matchid), match(id). In my case, the number of players never changes, it is always two (n=2). Which of the following designs is better?

(1)

player_to_match(matchid, playerid)

Having two rows for each map and one cell redundancy (matchid)

(2)

match(matchid, playerid1, playerid2)

As i said, the number of players per match can never change

Thank you

Lucas

[ERM-Diagram with two Entities: Player(ID, Firstname), Match(ID), n:m Assosiation from Player to Match titled "plays"] http://fs1.directupload.net/images/141210/rmeuutpg.png

Beginner
  • 23
  • 6
  • Your thinking that there is "cell redundancy" is misconceived. The mere appearance of a value multiple times in a column or table does not have anything to do with redundancy. See [this](http://stackoverflow.com/a/27809411/3404097). – philipxy Sep 05 '15 at 22:39

3 Answers3

1

I'd stick with option (1). It will make it easier to answer such simple questions as "how many matches has player X played?" With option (2), you'd have to query two columns for the value X to answer that question and that starts to get ugly.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Valid point. It really depends what your overall goals are. If you don't need this type of analysis, save the effort of managing an intermediary table. –  Dec 10 '14 at 22:34
  • @PhillipXT I've always been more of a "normalize until it hurts; denormalize until it works" kind of guy. Right now, I can't find any need or benefit to the denormalization of option (2). – Joe Stefanelli Dec 10 '14 at 22:39
  • "how many matches has player X played?" will definitely be a question to answer in my project – Beginner Dec 10 '14 at 22:44
0

Use the 2-table design. For something like this, you don't need the extra complexity because there is no chance chess is ever going to need 3 players. Unless you watch Big Bang Theory...

I prefer to start with the simpler form, and then modify it later if needed. As developers, we tend to try to come up with a solution that will handle any future possibility, but most of the time it never happens, and we've wasted a lot of time building an elegant solution for a problem that doesn't exist. Go simple first.

If you do need the 3-table option, you have some extra work to do to make sure there are always 2 related records to a match, no more, no less. Make sure you can't delete a user that is attached to an existing match, or you will have a match with only one player. A few things like that you'll have to watch out for.

0

I would do this:

matchid
black  (references PLAYER)
white  (references PLAYER)

The number of players in the game is finite (two), which eliminates the rationale for a 1-to-n child table; each player moreover has a defined "role" (white vs black) and you'd want to be able to distinguish them in that manner.

Tim
  • 8,669
  • 31
  • 105
  • 183
  • Why would you do this? Please clarify by editing your answer. (LQRQ) – Artjom B. Dec 10 '14 at 23:27
  • in my case i will never distinguish between the two players so there would be no additional information from the two player columns – Beginner Dec 11 '14 at 09:18
  • Not sure what you mean, @Beginner, that you would "never distinguish between the two players". Are you saying you don't care to know which player was black and which was white in a particular game? – Tim Dec 12 '14 at 12:26
  • yes. chess was just an example and differentiation between the players should not be an argument here. – Beginner Dec 14 '14 at 20:05