4

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?

user1463028
  • 73
  • 2
  • 4
  • 1
    When you ask such questions you should specify, clearly, entities that your application have to deal with. – SaidbakR Nov 26 '12 at 12:37
  • 1
    I guess the question could be summed to: How do I create a database structure where users are able to create new "tables", in this case various tournaments where contestants, match results, standings etc. are tracked. Because my experience with databases is restricted to a couple of data types in very static tables, I'm pretty clueless on how to approach the database for a project like this. – user1463028 Nov 26 '12 at 13:01
  • 1
    there is too little information in this question for the complexity involved. You should provide types of tournaments and types of results, preferably exemplified in a table, spreadsheet like. People who know how to model don't have to know about tournaments, etc... – koriander Apr 15 '13 at 08:09

1 Answers1

1

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.

ryan1234
  • 7,237
  • 6
  • 25
  • 36
  • And for example, how to relate each league data with results, squads from that season, how to model the data for i.e. getting the best goal scorers without almost killing the database? Should I distribute goal results for many tables related to each league type? – Ivo Pereira Apr 16 '13 at 15:16