CODEWITHSUNDEEP
mysqldatabasedata-structurestournamentdatamodel
user1463028
user1463028

Reputation: 73

Data structure for various tournament/competition types (league, ladder, single/double elimination etc.)

I'm attempting to build a system where users can easily create various types of tournaments and participate in them with other users. It's for no specific game, but instead a general purpose tool for viewing and updating tournament results from any device as quickly as possible, with bare minimum interaction, so the actual application doesn't get in the way in parties and such.

The user profiles, tournaments and results are kept in database, but the changes in tournaments should instantly reflect in the client's view, animated and without page reloads (JavaScript), then be sent to the server via ajax, verified and saved to a database (PHP, MySQL). The clients constantly listen to the server and update the view for all clients when any updates have been made (anything from contestant renames, to match results and drop-outs etc.)

I found some data models for single or double elimination tournaments, but this one is supposed to support a wide variety of tournament types, like league, ladder, single/double elimination and round robin.

So what sort of data(base) model I should use for a project like this, that is basically a Google Docs spreadsheet, but with predefined look and controls for each tournament type?

Should I go for a model that fits all the tournaments, or create separate tables for all the different tournament types? Any resources that might be useful for a project like this?

Upvotes: 4

Views: 3025

Answers (1)

ryan1234
ryan1234

Reputation: 7275

There are several questions/issues here, so I'll try to address each one.

All tournament interactions should be real time/reflected to many users.

For small to medium traffic on your website, this might not be a problem. For heavier traffic, this will quickly start to be a major issue.

Consider as an example how often you want to poll the database with your AJAX calls. Every second? So if you have 100 people with a page open, you have 100 database calls every second? You'll find that will quickly kill your database.

Even though this is slightly off topic, I would strongly recommend investigating how to cache tournament results ahead of time. You can cache the stats, etc. and either let them expire or expire them pro-actively, but definitely spend some time researching it.

Real time stats/results

Keep in mind that joins take time in relational databases. If you normalize your tournament structure heavily, then getting stats might be painful. The hardest part of the system to make efficient is going to be the aggregates and statistics from each tournament.

When you're designing your database/tables/views/stored procedures, keep in mind the end goal - getting stats quickly. This might mean not normalizing the data too much (to avoid too many joins). It might also mean paying very close attention to your data types - for example using bits/shorts/etc. instead of integers.

How to model the different tournament types

I'm not familiar with tournament models, but I do have specific advice on how to model. =)

Some questions you should ask yourself:

  1. Do all tournaments have common fields? In other words, for a round robin tournament we store 10 fields. For a single elimination tournament we store 11 fields. If they share the same 10 fields, then I would recommend putting all tournament types into one table and then use a tournament_type field to determine the type of tournament for your application.

  2. Do all tournaments not have common fields? Make them separate tables - one per tournament type. You might make one table for shared data, but then have different tables for specific information.

  3. Will tournament fields grow apart over time? Over time you'll want to add fields to tournament types. If you predict the tournaments will become very unique and very specific over time, make them separate. Otherwise you end up with lots of fields that have tons of NULL values in them.

  4. Have you considered a NoSQL solution? The nice thing about a NoSQL store is that it denormalizes the data so you don't have joins. Also you can have heterogeneous (different types of data) in the same "table" or container. Just something to consider because it might make your life considerably easier. Check out MongoDB as an example.

Upvotes: 1

Related Questions