Designing Your Tables

Designing your tables (schema basics)

A beginner's guide to structuring your database so it stays fast and sane. No prior database design knowledge assumed.

Start with: "what do I need to remember?"

For each thing your server tracks (players, vehicles, houses…), make a table. For each fact about that thing, make a column.

A player account needs: a unique id, a name, a password hash, money, score, …

CREATE TABLE accounts (
    id     INT PRIMARY KEY AUTO_INCREMENT,   -- unique number, auto-filled
    name   VARCHAR(24) NOT NULL UNIQUE,       -- player name, no duplicates
    hash   VARCHAR(255) NOT NULL,             -- Argon2id password hash
    money  INT NOT NULL DEFAULT 0,
    score  INT NOT NULL DEFAULT 0,
    last_login DATETIME,
    created    DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4;

The pieces explained

• id INT PRIMARY KEY AUTO_INCREMENT — every table should have a unique id. AUTO_INCREMENT means MySQL fills it in (1, 2, 3…). The primary key is the column that uniquely identifies a row. Always refer to a player by id, never by name (names can change).
• VARCHAR(24) — text up to 24 characters. Pick a sensible max.
• NOT NULL — this column must always have a value.
• UNIQUE — no two rows can share this value (stops duplicate account names — also a free defense against double-registration).
• DEFAULT 0 — new rows start at 0 if you don't specify.
• CHARACTER SET utf8mb4 — stores text in any language safely (full Unicode). Always use this. (Note: this is about storage — SA:MP/open.mp chat uses the GTA bitmap font and can't display emoji/most non-Latin characters in-game.)

Pick the right column type

Store…	Use	Example
whole numbers	INT (or BIGINT for huge)	money, score
true/false	TINYINT (0/1)	is_banned
decimals/coords	FLOAT or DOUBLE	x, y, z position
short text	VARCHAR(n)	name, ip
long text	TEXT	a description
a moment in time	DATETIME / TIMESTAMP	last_login

Relationships: one player, many things

Don't cram everything into one table. If a player can own many vehicles, make a second table that points back to the account:

CREATE TABLE vehicles (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    account_id INT NOT NULL,            -- which player owns it
    model      INT NOT NULL,
    INDEX (account_id)                  -- makes "find this player's cars" fast
) CHARACTER SET utf8mb4;

Then "give me Bob's cars" is:

SELECT * FROM vehicles WHERE account_id = 5;

This is called a one-to-many relationship (one account → many vehicles). The mysql-admin demo uses exactly this for accounts + account_weapons.

Indexes: make lookups fast

An index is like a book's index — it lets MySQL find rows without scanning the whole table. Add one on any column you frequently search by:

-- you look players up by name on every login, so index it
CREATE INDEX idx_accounts_name ON accounts (name);

(PRIMARY KEY and UNIQUE are already indexed automatically.)

Create tables once, at startup

Run your CREATE TABLE IF NOT EXISTS ... in OnGameModeInit / OnFilterScriptInit with mysql_execute_sync — it's a one-time setup, so blocking briefly there is fine:

mysql_execute_sync(g_DB, "CREATE TABLE IF NOT EXISTS accounts ( ... )");

Don'ts (common beginner mistakes)

• ❌ Don't use the player name as the primary key — names change; use id.
• ❌ Don't store the plaintext password — store the Argon2id hash.
• ❌ Don't make one giant table with 100 columns — split related data into tables.
• ❌ Don't forget utf8mb4 — utf8 (without mb4) can't store all characters.

Next: Your first queries · Cookbook