Designing a database for a FiveM server

A roleplay server’s database starts small — a players table and a vehicles table — and grows into dozens of tables written by dozens of scripts. Decisions made on day one about keys, indexes and where to store JSON decide whether queries stay instant at ten thousand characters or crawl. This guide covers the patterns that hold up.

When writing a script for one framework, reference its identifier rather than inventing your own. A table keyed by the same citizenid joins cleanly with the framework’s own data.

CREATE TABLE IF NOT EXISTS `fines` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `citizenid` VARCHAR(50) NOT NULL,
  `amount` INT UNSIGNED NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  `issued_by` VARCHAR(50) NOT NULL,
  `paid` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_fines_citizen` (`citizenid`),
  KEY `idx_fines_unpaid` (`citizenid`, `paid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• Auto-increment primary key for the row itself.
• The character identifier as a normal, indexed column.
• Money as integers (cents or whole dollars), never floats.
• A composite index for the query you run most (“unpaid fines for this character”).
• InnoDB for transactions and row locking.

Without an index, SELECT … WHERE owner = ? reads every row in the table. With a few hundred rows nobody notices; with a hundred thousand vehicles it is a slow query every time a garage opens. Add an index to every column in a WHERE, JOIN or ORDER BY you run often.

ALTER TABLE `player_vehicles` ADD INDEX `idx_vehicles_citizen` (`citizenid`);

oxmysql’s slow-query warning tells you which queries need one — see the oxmysql guide.

• Create tables with IF NOT EXISTS in an install SQL file shipped with the resource.
• Never store passwords, tokens or webhook URLs in tables players can reach through scripts.
• Clean up: delete old logs on a schedule instead of keeping everything forever.
• Back up nightly and test restoring — see the minimap studio’s server.cfg guide for where the database fits in the server.
• Use transactions for anything that moves money between rows.