设计表格，用于存储多人游戏的各种要求和统计信息 [英] Designing tables for storing various requirements and stats for multiplayer game

问题描述

您好，

我正在创建非常简单的爱好项目 - 基于浏览器的多人游戏。我坚持设计用于存储有关任务/技能要求的信息的表格。

I am creating very simple hobby project - browser based multiplayer game. I am stuck at designing tables for storing information about quest / skill requirements.

现在，我以下列方式设计了我的表：

For now, I designed my tables in following way:

• table 用户（有关用户的基本信息）


• 表 stat （各种统计信息）


• user_stats （每个用户连接统计信息）

• table user (basic information about users)
• table stat (variety of stats)
• table user_stats (connecting each user with stats)

另一个例子：

• 表怪物（有关npc敌人的基本信息）


• 表 monster_stats 连接怪物与统计数据，使用与上述相同的统计表）

• table monsters (basic information about npc enemies)
• table monster_stats (connecting monsters with stats, using the same stat table from above)

这些是简单的例子。我必须承认，我在设计不同事物的需求时遇到困难，例如任务。示例任务A可能只具有最低字符级别要求（并且易于实现） - 但是另一个任务B具有众多其他需求（完成任务，获得技能，拥有特定项目等） - 什么是好的方法设计用于存储这种信息的表？

Those were the simple cases. I must admit, that I am stuck while designing requirements for different things, e.g quests. Sample quest A might have only minimum character level requirement (and that is easy to implement) - but another one, quest B has multitude of other reqs (finished quests, gained skills, possessing specific items, etc) - what is a good way of designing tables for storing this kind of information?

以类似的方式 - 存储有关技能要求的信息的有效方式是什么？ （特定字符类，最小级别等）。

In a similar manner - what is an efficient way of storing information about skill requirements? (specific character class, min level, etc).

我将不胜感激创建数据库驱动游戏的任何帮助或信息。

I would be grateful for any help or information about creating database driven games.

感谢您的答案，但我想收到更多。由于我在设计一个相当复杂的可编程项目的数据库布局时遇到了一些问题，所以我为这个问题开始了最大的收获。

Thank You for the answers, yet I would like to receive more. As I am having some problems designing an rather complicated database layout for craftable items, I am starting a max bounty for this question.

我想收到链接到文章/代码片段/与设计数据库的最佳实践相关的任何东西，用于存储游戏数据（这种信息的一个很好的例子是availibe在 buildingbrowsergames.com ）。

I would like to receive links to articles / code snippets / anything connected with best practices of designing databases for storing game data (an good example of this kind of information is availibe on buildingbrowsergames.com).

我会感谢任何帮助。

推荐答案

尽管我希望OP能解决我的意见，我也会编辑这个来添加尽可能多的其他相关问题以上。我作为一名专业的网络游戏开发者，多年来一直是一名业余爱好者的网络游戏开发者，值得一提。

I'll edit this to add as many other pertinent issues as I can, although I wish the OP would address my comment above. I speak from several years as a professional online game developer and many more years as a hobbyist online game developer, for what it's worth.

在线游戏意味着某种持久性，这意味着你有两种类型的数据 - 一种是由你设计的，另一种是由玩家在游戏过程中创建的。很可能你要将数据库存储在数据库中。确保您有不同的表，并通过通常的数据库规范化规则正确地交叉引用它们。 （例如，如果您的玩家手工制作大写字母，则不会创建一个包含剑的所有属性的全部新行。您可以在player_items表中创建一个具有每个实例属性的新行，并且可以参考item_types表包含每个项目类型的属性。）如果您发现一行数据持有您设计的某些内容，以及播放器在播放过程中发生的某些变化，则需要将其归一化为两个表格。

Online games imply some sort of persistence, which means that you have broadly two types of data - one is designed by you, the other is created by the players in the course of play. Most likely you are going to store both in your database. Make sure you have different tables for these and cross-reference them properly via the usual database normalisation rules. (eg. If your player crafts a broadsword, you don't create an entire new row with all the properties of a sword. You create a new row in the player_items table with the per-instance properties, and refer to the broadsword row in the item_types table which holds the per-itemtype properties.) If you find a row of data is holding some things that you designed and some things that the player is changing during play, you need to normalise it out into two tables.

这是真正的典型的类/实例分离问题，适用于这样的游戏中的许多事情：一个地精实例不需要存储所有的细节，一个地精（例如绿色的皮肤），只有与那个实例有关的东西（例如位置，当前的健康状况）。有时候，施工行为有微妙之处，在这种情况下，需要根据类数据创建数据。 （例如，根据地精类型的最大运行状况设置地精实例的起始运行状况。）我的建议是将这些代码硬编码到创建实例的代码中并为其插入行。这种信息很少变化，因为在实践中几乎没有这样的价值。 （健康，体力，法术等等）等等。）

This is really the typical class/instance separation issue, and applies to many things in such games: a goblin instance doesn't need to store all the details of what it means to be a goblin (eg. green skin), only things pertinent to that instance (eg. location, current health). Some times there is a subtlety to the act of construction, in that instance data needs to be created based on class data. (Eg. setting a goblin instance's starting health based upon a goblin type's max health.) My advice is to hard-code these into your code that creates the instances and inserts the row for it. This information only changes rarely since there are few such values in practice. (Initial scores of depletable resources like health, stamina, mana... that's about it.)

尝试找到一个一致的术语来从类型数据中分离出实例数据 - 这当您修补现场游戏，并尝试不通过编辑错误的表格来破坏玩家的辛勤工作时，将使生活更轻松。这也使得缓存变得容易一些 - 通常可以缓存您的类/数据，而不会受到惩罚，因为只有当设计者将新数据推送到那里时才会发生变化。您可以通过 memcached 运行它，或者考虑在启动时加载它，如果您的游戏持续过程（即不是PHP / ASP / CGI / etc）等。

Try and find a consistent terminology to separate instance data from type data - this will make life easier later when you're patching a live game and trying not to trash the hard work of your players by editing the wrong tables. This also makes caching a lot easier - you can typically cache your class/type data with impunity because it only ever changes when you, the designer, pushes new data up there. You can run it through memcached, or consider loading it all at start up time if your game has a continuous process (ie. is not PHP/ASP/CGI/etc), etc.

记住，从设计方面的数据删除任何东西都是冒险的，一旦你上线，播放器生成的数据可能会引用回来。在部署到实时服务器之前，先对本地进行全面测试，因为一旦出现，就很难把它关闭。考虑一些方法来标记这些数据的行，这些数据以安全的方式被删除 - 也许一个布尔的活列，如果设置为false，这意味着它不会显示在典型的查询中。如果您禁用了他们所赚取的物品（如果这些是他们所支付的物品），请考虑对玩家的影响。

Remember that deleting anything from your design-side data is risky once you go live, since player-generated data may refer back to it. Test everything thoroughly locally before deploying to the live server because once it's up there, it's hard to take it down. Consider ways to be able to mark rows of such data as removed in a safe fashion - maybe a boolean 'live' column which, if set to false, means it just won't show up in the typical query. Think about the impact on players if you disable items they earned (and doubly if these are items they paid for).

实际的制作方面无法真正回答没有知道你想如何设计你的游戏。数据库设计必须遵循游戏设计。但我会经历一个琐碎的想法。也许你会想要创建一个基本的对象，然后用符文或者晶体或其他东西来增加它。为此，您只需要在项目实例和增强实例之间的一对多关系。 （记住，你可能也有项目类型和增加类型表）每个增加可以指定项目的属性（例如，耐久性，在作战中的最大伤害，重量）和修饰符（通常作为乘数，例如1.1到添加10％的奖金）。您可以看到我的解释，了解如何实施这些修改效果此处和此处 - 相同的原则适用于临时技能和拼写效果对于数据库驱动游戏中的角色统计数据，我通常会建议坚持使用一列（整数或浮点数）的朴实方法，以便于永久性修改项目。

The actual crafting side can't really be answered without knowing how you want to design your game. The database design must follow the game design. But I'll run through a trivial idea. Maybe you will want to be able to create a basic object and then augment it with runes or crystals or whatever. For that, you just need a one-to-many relationship between item instance and augmentation instance. (Remember, you might have item type and augmentation type tables too.) Each augmentation can specify a property of an item (eg. durability, max damage done in combat, weight) and a modifier (typically as a multiplier, eg. 1.1 to add a 10% bonus). You can see my explanation for how to implement these modifying effects here and here - the same principles apply for temporary skill and spell effects as apply for permanent item modification.

统计。稍后添加列并不是一个困难的操作，并且由于您要读取这些值很多，所以您可能不想一直执行连接。但是，如果您确实需要灵活性，那么您的方法是正确的。这非常类似于我在下面提出的技能等级表：大量的游戏数据可以用这种方法建模 - 将一件事情的一个或一个实例映射到其他事物的类或实例，通常需要一些额外的数据来描述映射（在这种情况下，统计数据的价值）。

For character stats in a database driven game, I would generally advise to stick with the naïve approach of one column (integer or float) per statistic. Adding columns later is not a difficult operation and since you're going to be reading these values a lot, you might not want to be performing joins on them all the time. However, if you really do need the flexibility, then your method is fine. This strongly resembles the skill level table I suggest below: lots of game data can be modelled in this way - map a class or instance of one thing to a class or instance of other things, often with some additional data to describe the mapping (in this case, the value of the statistic).

一旦建立了这些基本连接，并且确实是由类/实例数据分离导致的任何其他复杂查询以一种可能对您的代码可能不方便的方式 - 考虑创建视图或存储过程以在幕后执行，以便您的应用程序代码不必再担心。

Once you have these basic joins set up - and indeed any other complex queries that result from the separation of class/instance data in a way that may not be convenient for your code - consider creating a view or a stored procedure to perform them behind the scenes so that your application code doesn't have to worry about it any more.

当然，当您需要确保多个操作以原子方式（例如交易）发生时，其他良好的数据库操作也适用，在您最常搜索的字段上放置索引，使用VACUUM / OPTIMIZE TABLE / whatever在安静的时期，以保持表现，等等。

Other good database practices apply, of course - use transactions when you need to ensure multiple actions happen atomically (eg. trading), put indices on the fields you search most often, use VACUUM/OPTIMIZE TABLE/whatever during quiet periods to keep performance up, etc.

（原始答案在这一点以下。）

(Original answer below this point.)

要诚然，我不会将任务需求信息存储在关系数据库中，而是以某种脚本存储。最终，您对需求的想法有几种不同的形式，可以利用不同类型的数据（例如，级别，类，先前的任务完成，项目拥有）和运算符（一级可能是最小或最大值，一些任务可能需要一个项目，而其他可能需要其缺席等），更不用说连接和分离的组合（一些任务要求满足所有要求，而其他任务只需要满足几个要求）。这种事情在命令式语言中更容易指定。这并不是说你没有DB中的任务表，只是你不尝试将有时任意的需求编码到模式中。我有一个require_script_id列来引用外部脚本。我想你可以将实际的脚本作为一个文本字段放在数据库中，如果它也适合。

To be honest I wouldn't store the quest requirement information in the relational database, but in some sort of script. Ultimately your idea of a 'requirement' takes on several varying forms which could draw on different sorts of data (eg. level, class, prior quests completed, item possession) and operators (a level might be a minimum or a maximum, some quests may require an item whereas others may require its absence, etc) not to mention a combination of conjunctions and disjunctions (some quests require all requirements to be met, whereas others may only require 1 of several to be met). This sort of thing is much more easily specified in an imperative language. That's not to say you don't have a quest table in the DB, just that you don't try and encode the sometimes arbitrary requirements into the schema. I'd have a requirement_script_id column to reference an external script. I suppose you could put the actual script into the DB as a text field if it suits, too.

技能要求适合于数据库，而且相当简单典型的学习技能的游戏系统，在您通过某个课程的级别进步时：

Skill requirements are suited to the DB though, and quite trivial given the typical game system of learning skills as you progress through levels in a certain class:

table skill_levels
{
    int skill_id FOREIGN KEY;
    int class_id FOREIGN KEY;
    int min_level;
}

myPotentialSkillList = SELECT * FROM skill_levels INNER JOIN
    skill ON skill_levels.skill_id = skill.id
    WHERE class_id = my_skill
    ORDER BY skill_levels.min_level ASC;

需要一个技能树？添加一个列 prerequisite_skill_id 。等等。

Need a skill tree? Add a column prerequisite_skill_id. And so on.

这篇关于设计表格，用于存储多人游戏的各种要求和统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！