Convenciones de DB

NOVO DB, BUENAS PRÁCTICAS Y CONVENCIONES

BUENAS PRÁCTICAS GENERALES

Todos los nombres de tablas, campos, FK's, etc, y los mensajes de error deben estar escritos en inglés.
Priorizar el uso de comillas dobles excepto cuando la sintaxis de SQL requiera comillas simples.
Todas las palabras reservadas de SQL deben ir en mayúsculas
Cualquier variable, nombre de tabla, alias, pseudónimo, nombre de campo y nombre de FK debe ir con la convención.
Cuidar la redacción de la sintaxis de SQL. Cada palabra reservada implica un nuevo salto de línea.

SELECT captain_id INTO @captain 
FROM captains 
WHERE boat_id = @boat 
AND logical_deleted = 0;

Cuando se inserta o seleccionan campos, estos se declaran en nuevas líneas identadas.

UPDATE captains SET
    boat_id = @boat,
    name = _name,
    phone = _phone,
    email = _email,
    payment_permission = _payment_permission,
    aceptation_permission = _aceptation_permission
WHERE captain_id = @captain;

...

INSERT INTO captains (
    boat_id, 
    name, 
    phone, 
    email, 
    payment_permission, 
    aceptation_permission
)
VALUES (
    @boat, 
    _name, 
    _phone, 
    _email, 
    _payment_permission, 
    _aceptation_permission
);

Evitar abreviaciones. El código debe ser legible y las abreviaciones reducen legibilidad.

...
SELECT 1 FROM clients WHERE client_id = _client_id;
...

Para los alias se usa un nombre no abreviado precedido por un underscore.

...
LEFT OUTER JOIN Captains AS _captains
ON (_captains.boat_id = _boats.boat_id AND _captains.logical_deleted = 0)
LEFT OUTER JOIN Responsible AS _responsible
...

La lógica de los WHERE o SELECT se debe desarrollar entre paréntesis, y de preferencia cada sentencia lógica de un WHERE dentro de un único paréntesis.

ON (_responsible.boat_id = _boats.boat_id  AND _responsible.logical_deleted = 0) 
WHERE (_boats.client_id = _client_id)
AND (_boats.logical_deleted = 0);

Cualquier conjunto de código SQL que realice una operación específica, o tenga lógica específica (como joins, wheres, etc) debe llevar algún comentario breve explicando su propósito. El comentario debe tener buena ortografía y estar en español.

/* Obtiene el id del capitán para modificarlo */
SELECT captain_id INTO @captain 
FROM captains 
WHERE boat_id = @boat 
AND logical_deleted = 0;

MANEJO DE ERRORES

Snippet de "Manejo de Errores":
- Los mensajes de error se redactan en inglés por convención.
- deben encontrarse dentro de if's cerrados o en su defecto entrar al código SQL en su "THEN".
- tratar de hacer sentencias if secuenciales y simples, no anidadas.
- se desconoce si al hacer throw de un error finaliza por completo el SP o si continua ejecutando el SP. Contemplar que siempre continua el código y mantener una estructura lógica entorno a ese comportamiento.

 /* verifica que exista el cliente. de lo contrario tira una excepción. */
IF NOT EXISTS (SELECT 1 FROM clients WHERE client_id = _client_id) THEN
    /* Arroja un error customizado */
    SIGNAL SQLSTATE "45000"
    SET MESSAGE_TEXT = "Client was not found. Can't read boats without a client id.";
END IF;

Todos los scripts que hagan DROP deben usar "IF EXISTS".

DROP PROCEDURE IF EXISTS SP_READ_BOATS;

Todos los SP's deben hacer su propio manejo de errores en los siguientes casos:

SP's DELETE: si no se encuentra el elemento a eliminar debe tirar un error de "no se encuentra el elemento a borrar".

/* verifica que exista el capitan. si no existe truena. */
IF NOT EXISTS (SELECT 1 FROM captains WHERE boat_id = @boat AND logical_deleted = 0) THEN
    /* Arroja un error customizado */
    SIGNAL SQLSTATE "45000"
    SET MESSAGE_TEXT = "Captain doesn't exist. Can't delete captain.";
END IF;
...

SP's READ: No se debe poder hacer lectura sin las condiciones necesarias (como el id o nombre de un bote. si no existen los datos de selección debe retornar un error).

/* verifica que exista el cliente. de lo contrario tira una excepción. */
IF NOT EXISTS (SELECT 1 FROM clients WHERE client_id = _client_id) THEN
    /* Arroja un error customizado */
    SIGNAL SQLSTATE "45000"
    SET MESSAGE_TEXT = "Client was not found. Can't read boats without a client id.";
END IF;

SP's UPDATE: no debe actualizar si los datos de selección no existen (como id o nombres).

/* verifica que exista el cliente. de lo contrario tira una excepción. */
IF NOT EXISTS (SELECT 1 FROM clients WHERE client_id = _client_id) THEN
    /* Arroja un error customizado */
    SIGNAL SQLSTATE "45000"
        SET MESSAGE_TEXT = "Client was not found. Can't update a boat without a client id.";
END IF;

SP's CREATE: no debe crear el row si no existen los elementos que lo relacionan al mismo (FK's).

/* verifica que exista el cliente. de lo contrario tira una excepción. */
IF NOT EXISTS (SELECT 1 FROM clients WHERE client_id = _client_id) THEN
    /* Arroja un error customizado */
    SIGNAL SQLSTATE "45000"
    SET MESSAGE_TEXT = "Client was not found. Can't create a boat without a client id.";
END IF;

SP's CREATE: todos los SP's que tengan dependencias y no se cumplan deben arrojar errores (por ejemplo, un bote no puede ser actualizado o eliminado si no se cumple que el cliente existe, que el bote existe y que ambos están relacionados).

/* Verifica si el bote tiene un engine con ese id. de lo contrario tira una excepción */
IF NOT EXISTS (
    SELECT 1 FROM boats 
    WHERE client_id = _client_id 
    AND boat_id = @boat;
) 
THEN
    /* Arroja un error customizado */
    SIGNAL SQLSTATE "45000"
    SET MESSAGE_TEXT = "Doesn't exist that boat related with that client.";
ELSE
...

Scripts

Reglas de convención de nombre de FK:

FK_[nombre de la tabla que tiene dependencia de]_[nombre del PK de la tabla padre]

Foreign Keys: Modificar los alter tables de FK a constraints, y ponerles un comentario de la relación de la tabla (de que tabla hacia que tabla).

/* Boats -> Clients */
ALTER TABLE boats
ADD CONSTRAINT FK_boats_client_id
FOREIGN KEY (client_id) REFERENCES clients(client_id);

Scripts de CREATE TABLES

Separar cada script de Create Table en una archivo separado.
Debe ir todo minúsculas cada nombre de tabla en la sentencia CREATE.

Añadir NOT NULL a todos aquellos campos que se consideren obligatorios. Obliga desde la DB a solicitar esos campos.
Establecer con valores DEFAULT a los campos no obligatorios que puedan tener un valor default diferente de null (ejemplo, valores numéricos).
Cada script de create table debe llevar en comentarios en la parte superior una definición de que es cada campo a nivel lógica de negocio, y una explicación de la función de la tabla.

/* Table "Boat_Document_Types"  - TABLA DEBIL
Almacena la información de los tipos de documentos.

boat_document_type_id   -> ID natural
boat_document_type      -> Nombre del tipo de document
required                -> Si es requerido o no en el front
logic_deleted           -> Borrado lógico
logic_deleted_date      -> Fecha de borrado lógico

PK = document_type_id 
*/
CREATE TABLE IF NOT EXISTS boat_document_types(
...

Todos los campos de tipo "creation_date" deben llevar DEFAULT NOW() y al momento de crearlos no es necesario insertar el valor de creación (implica eliminar de cada CREATE TABLE el insert del campo creation_date).

Usar nomenclatura PascalCase con underscore para nombrar los archivos (ejemplo: Boat_Electricity.sql).
Las tablas débiles (incluidas tablas de estatus) deben nombrarse con el sufijo "types" ej: socket_types. Realizar los cambios pertinentes en los SP's y scripts respectivos. Contemplar afectación a la API al renombrar.
Todas las tablas deben llevar borrado lógico para asegurar la calidad de la información. Eso implica que todos los updates, creates y deletes deben recibir validaciones adicionales para evitar datos duplicados cuando son únicos, evitar actualizar datos eliminados, evitar leer datos eliminados, etc.
Todas las tablas deben tener dos campos para su borrado lógico:
- logical_deleted BOOLEAN NOT NULL DEFAULT 0,
- logical_deleted_date DATETIME DEFAULT NULL

Scripts de tipo Read Operation

Todos los SP's de tipo "READ" que realizan operaciones de select deben obligatoriamente devolver los campos de forma específicada (no con SELECT * FROM). Es importante retornar solo los campos que se desean consumir y no todos. Retornar siempre los ID's y FK's.

SELECT
    boat_document_id,
    boat_id,
    boat_document_type_id,
    url,
    last_update_date 
FROM boat_documents;

Al menos que por lógica de negocio se diga lo contrario, los SP's de Lectura siempre deben buscar todos aquellos rows excluyendo aquellos que estén borrados lógicamente.

SELECT * FROM responsible WHERE (boat_id = @boat AND logical_deleted = 0);

Cada SP debe tener un comentario en la parte superior explicando brevemente la función del SP. (ej: "Trae los tipos de sockets.").

/* SP SP_READ_BOATS_BY_CLIENT: Trae todos los barcos asociados a un cliente. */
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_READ_BOATS_BY_CLIENT`(

Store Procedure Convenciones

Para el nombrado de un SP se sigue la siguiente fórmula de convención:

[SP]_[Object]_[Action][Process]

ej:

SP_Boats_Get
SP_Boats_GetByClient

donde: SP = prefijo estándar. Table = Objeto o Tabla que es motivo de la manipulación. Action = Verbo que describe la acción del SP. Process = Descripciones adicionales del proceso.

Provide feedback

Saved searches