Tips for database design in a web application

Question

Does someone have any tips/advice on database design for a web application? The kind of stuff that can save me a lot of time/effort in the future when/if the application I'm working on takes off and starts having a lot of usage.

To be a bit more specific, the application is a strategy game (browser based, just text) that will mostly involve players issuing "orders" that will be stored in the database and processed later, with the results also being stored there (the history of "orders" and the corresponding results will probably get quite big).

Edited to add more details (as requested):

platform: Django

database engine: I was thinking of using MySQL (unless there's a big advantage in using another)

the schema: all I have now are some Django models, and that's far too much detail to post here. And if I start posting schemas this becomes too specific, and I was looking for general tips. For example, consider that I issue "orders" that will be later processed and return a result that I have to store to display some kind of "history". In this case is it better to have a separate table for the "history" or just one that aggregates both the "orders" and the result? I guess I could cache the "history" table, but this would take more space in the database and also more database operations because I would have to constantly create new rows instead of just altering them in the aggregate table.

Answer 1

Database Normalization, and a giving a good thought to indexes, are two things that you just can't miss. Especially if you consider a game, where SELECTS happen much more frequently than UPDATEs.

For the long run, you should also take a look at memcached, as database querys can be the bottleneck whenever you have more than a few users.

Answer 2

You have probably touched on a much larger issue of designing for high scalability and performance in general.

Essentially, for your database design I would follow good practices such as adding foreign keys and indexes to data you expect to be used frequently, normalise your data by splitting it into smaller tables and identify which data is to be read frequently and which is to be written frequently and optimise.

Much more important than your database design for high performance web applications, is your effective use of caching both at the client level through HTML page caching and at the server level through cached data or serving up static files in place of dynamic files.

The great thing about caching is that it can be added as it is needed, so that when your application does take off then you evolve accordingly.

As far as your historical data is concerned, this is a great thing to cache as you do not expect it to change frequently. If you wish to produce regular and fairly intensive reports from your data, then it is good practise to put this data into another database so as not to bring your web application to a halt whilst they run.

Of course this kind of optimisation really isn't necessary unless you think your application will warrant it.

Answer 3

Why don't you post the schema you have now? It's too broad a question to answer usefully without some detail of what platform and database you're going to use and the table structure you're proposing...

Answer 4

You should denormalize your tables if you find yourself joining 6+ tables in one query to retrieve data for a reporting type web page that will be hit often. Also, if you use ORM libraries like Hibernate or ActiveRecord make sure to spend some time on the default mappings they generate and the sql that ends up generating. They tend to be very chatty with the database when you could have achieve the same results with one round trip to the database.