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.
有人对Web应用程序的数据库设计有任何提示/建议吗?当我正在处理的应用程序起飞并开始大量使用时,那些可以为我节省大量时间/精力的东西。
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)
数据库引擎:我在想使用MySQL(除非使用另一个有很大的优势)
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.
架构:我现在拥有的只是一些Django模型,这里发布的细节太多了。如果我开始发布模式,这变得太具体了,我正在寻找一般提示。例如,考虑我发布稍后将处理的“订单”并返回我必须存储的结果以显示某种“历史”。在这种情况下,最好是为“历史”设置一个单独的表,还是只聚合“订单”和结果?我想我可以缓存“历史”表,但是这将占用数据库中更多的空间以及更多的数据库操作,因为我必须不断创建新行而不是仅仅在聚合表中更改它们。
4 个解决方案
#1
10
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.
比高性能Web应用程序的数据库设计更重要的是,您可以通过HTML页面缓存在客户端级别有效地使用缓存,在服务器级别通过缓存数据或提供静态文件来代替动态文件。
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.
就您的历史数据而言,这是一个很好的缓存,因为您不希望它经常更改。如果您希望根据数据生成定期且相当密集的报告,那么最好将此数据放入另一个数据库,以免在运行时停止Web应用程序。
Of course this kind of optimisation really isn't necessary unless you think your application will warrant it.
当然,除非您认为您的应用程序能够保证,否则这种优化确实没有必要。
#2
3
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.
数据库规范化,以及对索引的良好思考,是你不能错过的两件事。特别是如果你考虑一个游戏,SELECTS比UPDATE更频繁地发生。
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.
从长远来看,您还应该看一下memcached,因为只要您拥有多个用户,数据库查询就会成为瓶颈。
#3
1
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...
你为什么不发布现在的架构?如果没有关于您将要使用的平台和数据库的详细信息以及您提议的表格结构,这个问题的答案太广泛了......
#4
1
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.
如果您发现自己在一个查询中加入了6个以上的表来检索经常被命中的报告类型网页的数据,则应该对表进行非规范化。此外,如果你使用像Hibernate或ActiveRecord这样的ORM库,请确保花费一些时间在它们生成的默认映射和最终生成的sql上。当你通过往返数据库获得相同的结果时,他们往往对数据库非常讨厌。
#1
10
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.
比高性能Web应用程序的数据库设计更重要的是,您可以通过HTML页面缓存在客户端级别有效地使用缓存,在服务器级别通过缓存数据或提供静态文件来代替动态文件。
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.
就您的历史数据而言,这是一个很好的缓存,因为您不希望它经常更改。如果您希望根据数据生成定期且相当密集的报告,那么最好将此数据放入另一个数据库,以免在运行时停止Web应用程序。
Of course this kind of optimisation really isn't necessary unless you think your application will warrant it.
当然,除非您认为您的应用程序能够保证,否则这种优化确实没有必要。
#2
3
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.
数据库规范化,以及对索引的良好思考,是你不能错过的两件事。特别是如果你考虑一个游戏,SELECTS比UPDATE更频繁地发生。
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.
从长远来看,您还应该看一下memcached,因为只要您拥有多个用户,数据库查询就会成为瓶颈。
#3
1
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...
你为什么不发布现在的架构?如果没有关于您将要使用的平台和数据库的详细信息以及您提议的表格结构,这个问题的答案太广泛了......
#4
1
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.
如果您发现自己在一个查询中加入了6个以上的表来检索经常被命中的报告类型网页的数据,则应该对表进行非规范化。此外,如果你使用像Hibernate或ActiveRecord这样的ORM库,请确保花费一些时间在它们生成的默认映射和最终生成的sql上。当你通过往返数据库获得相同的结果时,他们往往对数据库非常讨厌。