Tips for creating a database in a web application

Question

Does anyone have any tips / advice on designing a database for a web application? Such material that can save me a lot of time and effort in the future when / if the application I'm working on takes off and starts to use a lot.

To be more specific, the application is a strategic game (browser-based, text only), which will mainly include players issuing “orders” that will be stored in the database and processed later, and the results will also be saved there (history "orders" and corresponding results are likely to be quite large). A.

Edited to add additional information (on request):

platform: Django

: I was thinking about using MySQL (unless there was a big advantage in using another)

scheme: all I have now are some Django models, and there are too many details to publish. And if I start to publish schemes, it becomes too specific, and I was looking for general advice. For example, think that I am issuing “orders” that will be processed later and return a result that I must save in order to display some kind of “history”. In this case, is it better to have a separate table for the "history" or only one that combines the "orders" and the result? I suppose I could cache the history table, but that would require more space in the database, as well as more database operations, because I would have to constantly create new rows, rather than just changing them in the aggregate table.

Answer 1

You probably raised a much more serious design problem for high scalability and overall performance.

Essentially, for your database project, I would follow such good practices as adding foreign keys and indexes to the data that you expect to be used often, normalize your data by dividing it into smaller tables and determine what data you need read often and which should be written often and optimized.

More important than your database design for high-performance web applications is the efficient use of caching both at the client level by caching HTML pages and at the server level using cached data or serving static files instead of dynamic files.

The great thing about caching is that it can be added as needed, so that when your application is canceled, you will evolve accordingly.

As for your historical data, this is a great thing to cache, as you do not expect it to change often. If you want to receive regular and fairly intense reports from your data, then it is good practice to put this data in another database so as not to stop your web application during its launch.

Of course, such optimization is really not needed if you do not think that your application will guarantee it.

Answer 2

Database normalization and a good idea for indexes are two things you just can't miss. Especially if you consider a game where SELECTS occur much more often than UPDATE.

In the long run, you should also take a look at memcached , as database queries can be a bottleneck when you have more than a few users.

Answer 3

Why don't you post the scheme you are currently using? This is too broad a question for a useful answer without any details of what platform and database you are going to use, and the table structure that you propose ...

Answer 4

You should denormalize your tables if you find yourself joining 6 + tables in one query to get data for a web page like a report that will hit frequently. Also, if you use ORM libraries like Hibernate or ActiveRecord, be sure to spend some time on the default mappings that they generate and on the sql that ends. They are usually very chatty with the database, when you could achieve the same results with one call to the database.