Efficiently organizing a 2D map in a SQL database using an optimized primary key structure

Question

Efficiently organizing a 2D map in a SQL database using an optimized primary key structure

Currently, I am developing a small JavaScript game that relies on a map rendered from a collection of coordinates stored in a database. As I work on designing the database, I am contemplating between two methods to determine which one would be more preferable.

The world in my game is mapped to 1000x1000 tiles, with each coordinate pair (x,y) representing a unique tile with its own set of properties.

Given that this is my first time creating an object/database like this, my initial thought was to use the unique coordinate pair as the primary key. This method not only guarantees no collision but also provides a clear way of allocating the primary keys. For example, tile x: 844, y:444 would have an ID of 08440444. Would using this approach make it more computationally challenging to retrieve an X coordinate from a given ID compared to the alternative? Are there other ways to construct an ID from an [x,y] pair?

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

or



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

javascript sql database postgresql database-design

Answer 1

Answer №1

If you want your game engine to run smoothly, optimizing your database is key. Define a data structure that aligns with your clear tile "environment":

Opt for using smallint for Tile IDs instead of regular integer, saving 50% in storage and reducing network traffic.
Create a Primary Key from the tile IDs rather than using a serial data type or an extra column, unless it significantly benefits your data model. Consider utilizing a non-unique index on the tile IDs instead of a PK.

In many cases, having just "treasures" and "people" tables with 'x' and 'y' coordinates should be sufficient. Only introduce a "tiles" table if you require specific tile properties through a relationship like "has-a" or "is-a" (1-1). Avoid defining referential relationships between tables unless absolutely necessary, as they can be resource-intensive to maintain.

Here's a simplified setup you might consider:

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,  
  prop1  integer,
  prop2  varchar,
  ...
);
CREATE INDEX idx_treasures ON treasures(x, y, id);

CREATE TABLE people(
  x      smallint,
  y      smallint,
  id     smallint, 
  p_type integer,  
  prop1  integer,
  prop2  varchar,
  ...
);
CREATE INDEX idx_people ON people(x, y, id);

Answer 2

If you want your game engine to run smoothly, optimizing your database is key. Define a data structure that aligns with your clear tile "environment":

Opt for using smallint for Tile IDs instead of regular integer, saving 50% in storage and reducing network traffic.
Create a Primary Key from the tile IDs rather than using a serial data type or an extra column, unless it significantly benefits your data model. Consider utilizing a non-unique index on the tile IDs instead of a PK.

In many cases, having just "treasures" and "people" tables with 'x' and 'y' coordinates should be sufficient. Only introduce a "tiles" table if you require specific tile properties through a relationship like "has-a" or "is-a" (1-1). Avoid defining referential relationships between tables unless absolutely necessary, as they can be resource-intensive to maintain.

Here's a simplified setup you might consider:

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,  
  prop1  integer,
  prop2  varchar,
  ...
);
CREATE INDEX idx_treasures ON treasures(x, y, id);

CREATE TABLE people(
  x      smallint,
  y      smallint,
  id     smallint, 
  p_type integer,  
  prop1  integer,
  prop2  varchar,
  ...
);
CREATE INDEX idx_people ON people(x, y, id);

Efficiently organizing a 2D map in a SQL database using an optimized primary key structure

Answer №1

Similar questions

Breaking Down a Lengthy AngularJS Controller into Modular Components

Transform an array into an object utilizing only underscore

Can data be fetched from a local port using either Javascript or ASP.NET?

How can I retrieve the height of a hidden image in Internet Explorer?

Transform the varchar data type into a date format with the pattern yyyy-mm-dd

Error: Invalid JSON Web Token structure

Why is MySQLi not retrieving all the rows?

Enhance your website visuals with jQuery Sparklines and CSS Transforms

Inject props into a Component nested within a Higher-Order-Component (HOC)

Identify and track colored dots within an image using tracking.js

Customize the field type in Kendo UI Grid based on specific row criteria

Defining the NgRx root state key within the application state interface

Plotting only the initial and final point on Google Maps using API

Tips for displaying bar chart labels effectively with ChartJS

The functionality to redirect in Wordpress Contact Form 7 based on the value of a radio button selection does

Running a stored procedure using a string that mimics an array, with the values separated

Using npm to install packages with multiple package.json files

Learn the process of utilizing Javascript to substitute additional texts with (...) in your content

When I set a date for one variable, it causes another variable to be automatically updated as

Merge JSON objects into an array