Database with oxmysql · Lesson 3/3 · 9 min

Designing your resource's table

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

Before saving anything, think about the table. A well-designed table is fast and doesn't corrupt; a poorly designed one will give you weird bugs months later.

Example: a properties table

sql

CREATE TABLE IF NOT EXISTS player_houses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  owner VARCHAR(60) NOT NULL,        -- owner's identifier
  label VARCHAR(80) NOT NULL,
  price INT NOT NULL DEFAULT 0,
  coords JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_owner (owner)            -- looking up by owner is fast
);

Table DDL

Primary key (id AUTO_INCREMENT) always: identifies each row unambiguously.
Correct types: INT for numbers, VARCHAR(n) for short text, JSON for structures, TIMESTAMP for dates.
Index (INDEX) on the columns you filter by a lot (owner, identifier) → fast queries.
NOT NULL and DEFAULT so you don't end up with half-baked data.

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's a single structure.

Practice what you learned

0/3

Rellena los huecos

Completa el CREATE TABLE de propiedades: clave primaria, texto corto, estructura y un índice.

1CREATE TABLE IF NOT EXISTS player_houses (

2 id INT AUTO_INCREMENT KEY,

3 owner (60) NOT NULL,

4 coords NOT NULL,

5 idx_owner (owner)

6);

Pista

PRIMARY KEY identifica la fila; VARCHAR(n) para texto corto; JSON para estructuras; INDEX para buscar rápido.

Test

Para guardar unas coordenadas con heading (una estructura), ¿qué tipo de columna es el más adecuado?

Pista

¿Estructura completa en una sola columna?

Ordena el código

Ordena el CREATE TABLE de un sistema de multas (id, importe e índice por ciudadano).

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

CREATE TABLE IF NOT EXISTS fines (

INDEX idx_citizen (citizen)

);

citizen VARCHAR(60) NOT NULL,

amount INT NOT NULL DEFAULT 0,

id INT AUTO_INCREMENT PRIMARY KEY,

Pista

Empieza por CREATE TABLE, luego las columnas (id primero) y cierra con el índice y el paréntesis.

Challenge: code it yourself

Design a table to store police fines: who issues it, to whom, amount, reason 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 the door to SQL injection End of the module, back to the Academy

How was this lesson?