Years after recovering from weekly trips to dingy, artificially and poorly lit shops full of cardboard worth more than its weight in gold, I still think about analyzing and modeling Magic: the Gathering tournaments. The dance of choosing the right deck followed by the scramble to borrow, buy, or rent cards for a tournament only to lose your first two rounds eliminating any hope of recouping your expenses, still has some nostalgic pull on me. One of my last tournaments ended in a final round loss that put me that single loss out of a money finish. That night, instead of celebrating during the 3-hour drive home from Denton, I had to pick myself up off of a cold sidewalk, toss my midnight Wendy's into the trash, fill my car with gas and load 4 more not small college dudes into a sedan. I had to wake up the next morning for my 8am Intro to Computer Science class technically that same morning. Those were the lengths I would go to scratch my competitive itch. There's still enough positive nostalgia to inspire projects.
Being relatively new to database design, I want to work through, step by step, an idea I've been thinking about since before the midnight Wendy's in Denton. Given perfect information about a Magic tournament, how would you model that information? That includes players, their matches, the tournament information, player deck lists, winners, etc. Storing that information isn't enough though, I want the structure to be generalize-able to other Swiss tournaments.
Let's construct some sort of helpful simpler example that we can use to model out the initial database. Let's say we have Rock-Paper-Scissors players who want to compete in the Rock-Paper-Scissors World Championship. The RPS League is starting out fresh and they have no prior tournaments, but they have a structure of tournaments through which players can qualify by winning a World Championship Qualifier that will then qualify the winning player for the World Championship. This gives us a lot of RPS players who want to play in a lot of tournaments. At its most simple a tournament is a collection of matches played by players with a winner and a loser. This yields a many-to-many relationship between Tournaments and Players and a semantic name for the join table! As of right now this is our model:
This looks pretty good so far! It's simple, but with a lot of useful information. I've also added a few columns that will be helpful later when I use this structure in my MtG tournaments. Don't worry too much about the column structure besides the foreign keys. Those are the most generalized relationships. I've included a rating in the Players table so they can look up their Elo once the system is fully built. They need to know when they're improving! This looks perfect for our RPS League. We can find players who played in tournaments before, check who won, and, if we wanted, see the highest rating player is.
This is a great high-level very general look at a tournament model, but let's get more granular. Let's move back to MtG and its specific needs. Players can bring their own custom decks made up of cards to tournaments. Or a Deck has many Cards. That's a relationship we can add to out database model.
That relationship looks good too! I need to caveat this relationship by stating that Decks and Cards is actually a many-to-many, but that's a little too granular for right now. Skipping that, let's figure out how to connect Decks to Players and Tournaments. For simplicity, we're going to say that a deck is only used once by a player, who can use many decks in many tournaments, in a specific tournament, that can have many decks. That's a long string of relationships, but it boils down pretty nicely.
A simple and naive approach to achieve this is to create a join between Players, Tournaments, and Decks. If you've ever played in an MtG tournament they give us a name for this table called Registration, it's pretty semantic and the relationship fits right in my head so let's use it. In the process you sign up for a tournament, list the cards in your deck, and add your name to the list. This was the end point of my knowledge for database creation, and I ended up pretty happy with the result. HOWEVER, after talking to someone with more knowledge than myself there's one more step and this isn't the final product. Here's my original finish point:
If you work with databases you'll notice that, while this would probably work for a simple application, this database is not normalized. There are a lot of extra points and information can be gotten in strange round about relational loops. We can fix that though. We can make it more simple.
Here's the final product. It's normalized, most data can be accessed via a single relational route, and all data is available through a single route. An optimization that could be implemented would be to only have a winner and loser foreign key in Matches instead of RegistrationID1, RegistrationID2, and Winner. Besides that I believe this is a 3NF database. If not feel free to let me know. Feedback can only help me learn.
Learning and relearning what I now know about databases has been fun. I've been drawn to larger data sets for their statistical significance, and the information that you can glean from trends in meta-games such as deck building in MtG. It scratches an itch that is close to and more satisfying than the itch to compete, the itch to collaborate and succeed.
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)