Database with oxmysql · Lesson 3/3 · 9 min

Designing your resource's table (with citizenid)

A good table saves you pain. Correct types, primary key, citizenid and indexes where they belong.

Before saving anything, think about the table. In QBCore, most of your tables will be linked to the character by their citizenid. A well-designed table is fast and doesn't get corrupted.

Example: a properties table

sql

CREATE TABLE IF NOT EXISTS player_houses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  citizenid VARCHAR(50) NOT NULL,    -- owner (QBCore key)
  label VARCHAR(80) NOT NULL,
  price INT NOT NULL DEFAULT 0,
  coords JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_citizenid (citizenid)    -- searching by owner is fast
);

Table DDL

Primary key (id AUTO_INCREMENT) always: identifies each row unambiguously.
citizenid VARCHAR to link to the character (don't use the source, which changes every session).
Correct types: INT for numbers, VARCHAR(n) for short text, JSON for structures.
Index (INDEX) on citizenid → listing a player's properties is instant.

For complex data (an inventory, coords with heading) use a JSON column and save with json.encode / read with json.decode. Avoid creating 20 columns for something that is a structure.

Practice what you learned

0/3

Test

En QBCore, ¿qué columna usas para vincular una fila al personaje del jugador?

Ordena el código

Ordena el CREATE TABLE de una tabla propia que guarda un valor por personaje, con clave primaria e índice por citizenid.

Coloca las líneas en el orden correcto con las flechas.

CREATE TABLE IF NOT EXISTS mirecurso_datos (

);

valor INT NOT NULL DEFAULT 0,

id INT AUTO_INCREMENT PRIMARY KEY,

INDEX idx_citizenid (citizenid)

citizenid VARCHAR(50) NOT NULL,

Pista

Primero abres el CREATE TABLE, luego la clave primaria, después las columnas, el índice y cierras.

Rellena los huecos

Completa la línea del fxmanifest que carga oxmysql en tu recurso de servidor.

1server_scripts {

2 '',

3 'server.lua',

4}

Pista

Es el import de la librería de oxmysql; va en server_scripts, no en client.

Challenge: code it yourself

Design a table to store police fines in QBCore: officer, citizen (both by citizenid), amount, reason, whether it's paid and date.

Write it yourself in your editor (VS Code) and test it on your server. You learn here by doing it, not by copying.

See hint

PRIMARY KEY on id, INDEX on citizen to list their fines, TINYINT(1) for paid.

Escribe aquí tu solución:

← Parameterized queries: close off SQL injection End of the module, back to the Academy

How was this lesson?