La forma de una base de datos

Toda consulta que escribas en tu vida se apoya en un modelo mental. Una vez que lo tienes, SQL deja de ser un conjuro mágico copiado de una persona desarrolladora y pasa a ser algo sobre lo que puedes razonar. Esta lección construye ese modelo, y es la única del curso con más lectura que escritura.

Tablas, filas y columnas

Una base de datos relacional guarda datos en tablas. Una tabla es una cuadrícula:

• una columna es un campo, con nombre y tipo, iguales para todas las entradas
• una fila es una entrada, una cosa: un post, una categoría, una etiqueta

Nuestro blog tiene cuatro tablas. Mira categories, la más simple:

Tres filas, cuatro columnas. Una hoja de cálculo con reglas, y las reglas son la parte interesante.

La clave primaria

Mira la columna id. Es la clave primaria: el valor que identifica una fila de forma única y nunca se repite dentro de la tabla. Dos categorías jamás pueden compartir un id.

En este esquema las claves primarias son SERIAL, lo que significa que la propia base de datos asigna el siguiente número cada vez que se inserta una fila. Tú no lo eliges, y no puedes contar con que sea secuencial después de eliminaciones, pero dentro de una tabla está garantizado que es único.

👨‍🏫 Para quien prueba, la clave primaria es la huella digital (🫆) de la fila. Cuando encuentres una fila defectuosa, anota su id. Es el único dato que sigue siendo cierto sin importar cómo se edite el registro después, y es lo que convierte "uno de los posts está mal" en "el post 14 está mal", que es algo sobre lo que una persona desarrolladora puede actuar.

La clave foránea, y la forma uno a muchos

Ahora abre posts:

\d posts

Entre sus columnas está category_id, y al final de la salida ves esto:

Foreign-key constraints:
    "posts_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL

Una clave foránea es una columna que apunta a la clave primaria de otra tabla. posts.category_id guarda el id de una fila de categories. Así se conectan las dos tablas, y eso nos da una relación uno a muchos: una categoría tiene muchos posts, un post tiene como máximo una categoría.

La clave foránea también es una regla que la base de datos impone. No puedes guardar category_id = 999 si no existe la categoría 999; la base de datos rechaza la fila. Esa es una garantía sobre tus datos que ninguna cantidad de código de aplicación puede ofrecer con la misma confianza, y es la razón por la que quien prueba debería leer el esquema antes de escribir la primera prueba.

Dos detalles de esa restricción importan más adelante:

• category_id admite nulos. Un post puede no tener categoría en absoluto. Ese solo hecho está detrás de toda una familia de errores de prueba, y tiene su propia lección.
• ON DELETE SET NULL. Si se elimina una categoría, sus posts sobreviven, con category_id convertido en NULL. Quien decidió qué pasa fue la base de datos, no la aplicación.

La tabla de unión, y la forma muchos a muchos

Un post puede tener varias etiquetas, y una etiqueta puede estar en varios posts. Eso es muchos a muchos, y no hay forma de expresarlo con una columna en ninguno de los dos lados. ¿Qué columna guardaría tres valores?

Así que un esquema relacional usa una cuarta tabla, post_tags:

\d post_tags

Dos columnas, ambas claves foráneas, y juntas forman la clave primaria. Cada fila significa "este post tiene esta etiqueta". Un post con tres etiquetas produce tres filas aquí. Eso es todo lo que es una tabla de unión: una lista de pares.

Dos consecuencias que vale la pena llevarse:

• Como (post_id, tag_id) es la clave primaria, la misma etiqueta no puede adjuntarse dos veces al mismo post. La base de datos hace imposible el duplicado.
• Ambas claves foráneas son ON DELETE CASCADE, así que eliminar un post quita automáticamente sus filas aquí. Las etiquetas en sí sobreviven, porque otros posts pueden seguir usándolas.

👨‍🏫 Siempre que veas una tabla cuyo nombre parece dos nombres de tabla pegados (post_tags, order_items, user_roles), estás mirando una relación muchos a muchos. Nueve de cada diez veces también es donde están los errores interesantes, porque es la única parte del esquema que la aplicación tiene que mantener a mano.

Leyendo un esquema que no escribiste

Tienes tres formas de aprender un esquema, y quien prueba bien usa las tres.

1. El archivo de esquema. En este proyecto, db/schema.sql tiene 40 líneas y lo cuenta todo: las tablas, los tipos, qué es NOT NULL, qué es UNIQUE, las claves foráneas y los índices. Cuando un proyecto tiene uno, léelo primero.

2. La base de datos misma. \dt lista las tablas, \d <tabla> describe una. Esto funciona en cualquier base de datos, incluida una de producción cuyo código fuente nunca has visto, que es exactamente cuando más lo necesitas.

3. El dibujo. Para cuatro tablas, un boceto le gana a las dos anteriores:

categories                posts                         tags
----------                -----                         ----
id         PK   <-------- category_id  FK               id         PK
name       UNIQUE         id           PK               name       UNIQUE
slug       UNIQUE         title                         slug       UNIQUE
created_at                body                          created_at
                          created_at
                                  \                    /
                                   \   post_tags      /
                                    -- post_id  FK --
                                       tag_id   FK

Las flechas apuntan de la clave foránea a la clave primaria que referencia. Cuando puedes dibujar eso para una aplicación, puedes consultarla.

Lo que el esquema le cuenta a quien prueba antes de ejecutar ninguna prueba

Leer un esquema es, en sí mismo, una actividad de prueba. Cada restricción responde una pregunta que de otro modo tendrías que hacerle a una persona desarrolladora, y cada restricción ausente es un caso de prueba:

👨‍🏫 Esa última fila es la que quiero que recuerdes. La mitad de las discusiones de "¿esto es un error?" se resuelven en treinta segundos leyendo el esquema.

Resumen de comandos 📖

\dt

Lista las tablas de la base de datos actual.

Sintaxis:

\dt

Ejemplo:

\dt

\d

Describe una tabla: columnas, tipos, nulabilidad, valores por defecto, índices y claves foráneas.

Sintaxis:

\d <nombre-de-la-tabla>

Ejemplo:

\d post_tags

Contenido sugerido 📚

• Data Definition - documentación oficial de PostgreSQL
• Constraints - documentación oficial de PostgreSQL
• Foreign keys - documentación oficial de PostgreSQL

Ejercicio 🎯

Sin ejecutar un solo SELECT, usa \d en las cuatro tablas y responde lo siguiente, anotando tus respuestas antes de mirar el spoiler:

1. ¿Qué columnas de todo el esquema son UNIQUE?
2. Si alguien elimina la categoría Databases, ¿qué pasa con sus posts?
3. Si alguien elimina un post que tiene tres etiquetas, ¿cuántas filas desaparecen de post_tags, y cuántas de tags?
4. ¿Pueden dos posts distintos tener exactamente el mismo título?

🙊 Aquí están las respuestas:

1. categories.name, categories.slug, tags.name y tags.slug. Más las claves primarias, que son únicas por definición: categories.id, posts.id, tags.id y el par (post_id, tag_id) en post_tags.
2. No se elimina nada. ON DELETE SET NULL significa que los posts sobreviven con category_id convertido en NULL. Entonces aparecen como posts sin categoría.
3. Tres filas desaparecen de post_tags, por el ON DELETE CASCADE. Cero filas desaparecen de tags: las etiquetas en sí pertenecen a todo el blog, no a ese post, y otros posts pueden seguir usándolas. Encontrar las etiquetas que ya no pertenecen a ningún post es un buen ejercicio, y hacemos exactamente eso dentro de dos lecciones.
4. Sí. No hay restricción UNIQUE en posts.title, así que los títulos duplicados están permitidos por diseño. Vamos a crear un par de ellos a propósito más adelante, y luego los encontraremos con una consulta.

Muestra al mundo lo que aprendiste 🌎

Para mostrarle a tu red profesional lo que aprendiste en esta lección, publica lo siguiente en LinkedIn.

Estoy tomando el curso "SQL para Testers" de @Walmyr Lima e Silva Filho en la @Talking About Testing School, donde aprendí a leer un esquema de base de datos como quien prueba: claves primarias, claves foráneas, relaciones uno a muchos y muchos a muchos, y qué cuenta cada restricción sobre la aplicación antes de ejecutar ninguna prueba. #TalkingAboutTesting #TATSchool #SQLForTesters #SQL #PostgreSQL #Testing

👨‍🏫 Recuerda etiquetarme en tu publicación. Aquí está mi perfil de LinkedIn.