使用高效的主键架构将2D映射存储在SQL数据库中

I am working on a small game in javascript which relies on a map rendered from a collection of coordinates (held in a database). I am at the point of designing the database and wanted to know which of two methods was considerable preferable.

我正在开发一个javascript的小游戏，它依赖于从坐标集合（在数据库中保存）渲染的地图。我正在设计数据库，并想知道哪两种方法更可取。

The world is mapped to 1000x1000 tiles and each coordinate pair (x,y) represents a unique tile with its own properties.

世界被映射到1000x1000个图块，每个坐标对（x，y）表示具有自己属性的唯一图块。

I've never constructed an object/database like this before, so my instinct was to use the unique coordinate pair as the primary key which would ensure no collision as well as give me an obvious way of allocating the primary keys. Thus tile x: 844, y:444 would be represented with the ID of 08440444. Would this make it more computationally difficult to grab an X coordinate from a given ID than the second option? Is there a better way of constructing an ID out of an [x,y] pair?

我之前从未构建过像这样的对象/数据库，所以我的直觉是使用唯一的坐标对作为主键，这将确保不会发生冲突，并为我提供一种分配主键的明显方法。因此，瓦片x：844，y：444将用ID 08440444表示。这是否会使得从给定ID中获取X坐标比第二个选项更难计算？是否有更好的方法从[x，y]对构造ID？

                                       ┌──────────────────┐                                    
                                       │   table: tiles   │                                    
                    ┌──────────────────┼──────────────────┼──────────────────┐                  
                    │      coord       │    treasures     │      people      │                  
                    │                  │                  │                  │                  
                    ├──────────────────┼──────────────────┼──────────────────┤                  
                    │     integer      │                  │                  │                  
                    │   primary key    │     hasMany      │     hasMany      │                  
                    │ unique x,y coord │   relationship   │   relationship   │                  
                    │                  │                  │                  │                  
                    └──────────────────┴──────────────────┴──────────────────┘

要么



                                      ┌──────────────────┐                                     
                                      │   table: tiles   │                                     
┌──────────────────┬──────────────────┼──────────────────┼──────────────────┬──────────────────┐
│        id        │        x         │        y         │    treasures     │      people      │
│                  │                  │                  │                  │                  │
├──────────────────┼──────────────────┼──────────────────┼──────────────────┼──────────────────┤
│     integer      │     integer      │     integer      │                  │                  │
│   primary key    │   represents x   │   represents y   │     hasMany      │     hasMany      │
│ autoincrementing │    coordinate    │    coordinate    │   relationship   │   relationship   │
│                  │                  │                  │                  │                  │
└──────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┘

1 个解决方案

#1

The key to making your game engine perform is to keep your database as compact as possible. Your tile "environment" is well-defined, so make a data structure that matches your design:

使您的游戏引擎运行的关键是让您的数据库尽可能紧凑。您的磁贴“环境”定义明确，因此请创建与您的设计相匹配的数据结构：

Tile IDs use the smallint data type, saves you 50% in storage and network traffic compared to regular integers.
Tile ID使用smallint数据类型，与常规整数相比，可节省50％的存储和网络流量。
If you really need a PK, create it from the tile IDs, do not use a serial data type or a separate column unless your data model is certain to benefit from it. You may get by just fine without a PK and use a non-unique index on the tile IDs instead.
如果您真的需要PK，请从tile ID创建它，不要使用串行数据类型或单独的列，除非您的数据模型肯定会从中受益。你可以在没有PK的情况下完成任务，而是在tile ID上使用非唯一索引。

In fact, you do not even need a "tiles" table with the information provided above. You can get by with a "treasures" and a "people" table that both have 'x' and 'y' columns. Only when you have specific tile properties through a "has-a" or "is-a" (1-1) relationship would you benefit from a "tiles" table. Also, do not define referential relationships between the tables unless you really need them, they incur a lot of overhead to be maintained.

事实上，您甚至不需要具有上面提供的信息的“瓷砖”表。你可以使用“宝藏”和“人物”表，它们都有'x'和'y'列。只有通过“has-a”或“is-a”（1-1）关系具有特定的tile属性时，才能从“tiles”表中受益。此外，除非您确实需要它们，否则不要定义表之间的引用关系，它们会产生大量的开销来维护。

In short, this may be all you need:

简而言之，这可能就是您所需要的：

CREATE TABLE tileproperties (
  x      smallint,
  y      smallint,
  prop1  integer,
  prop2  varchar,
  ...
);
CREATE INDEX idx_tileproperties ON tileproperties(x, y);

CREATE TABLE treasures (
  x      smallint,
  y      smallint,
  id     smallint, 
  t_type integer,  -- this may be a FK to a table defining a type of treasure
  prop1  integer,
  prop2  varchar,
  ...
);
CREATE INDEX idx_treasures ON treasures(x, y, id); -- may also be UNIQUE or PK

CREATE TABLE people(
  x      smallint,
  y      smallint,
  id     smallint, 
  p_type integer,  -- this may be a FK to a table defining a type of people
  prop1  integer,
  prop2  varchar,
  ...
);
CREATE INDEX idx_people ON people(x, y, id); -- may also be UNIQUE or PK

#1