Leer un esquema desconocido

1. Qué es

Te sientan frente a una base de datos que no conoces. Cuarenta tablas, nombres en inglés, nadie que te explique nada. Esto pasa el primer día de casi cualquier trabajo con datos.

Este tema es el método para orientarte sin depender de que alguien te lo cuente.

2. Las cuatro preguntas

Ante cualquier tabla nueva, en este orden:

2.1 El grano es la más importante

Grano = qué representa una fila. Y se dice en una frase con sujeto:

• orders → "un pedido"
• order_details → "un producto dentro de un pedido"
• customers → "un cliente"

Si no puedes decirlo en una frase, todavía no entendiste la tabla.

Por qué importa tanto: el grano determina qué puedes sumar sin equivocarte. orders.freight es el flete del pedido. Si unes orders con order_details y sumas freight, lo estás sumando una vez por cada línea del pedido. El pedido 11077 tiene 25 líneas: su flete contaría 25 veces.

Ese error no da ningún mensaje. Solo da un número más grande.

3. El nombre de la tabla no dice el grano

Una tabla llamada ventas puede tener una fila por venta, por línea de venta o por venta y día. Se llaman igual, se parecen al abrirlas y ninguna te avisa. Estos son los tres casos, con los mismos datos de fondo: Ana compró café y azúcar el 1 de marzo, Luis compró café el 2.

3.1 Una fila por venta.

+-------+------------+---------+-------+
| venta | fecha      | cliente | total |
+-------+------------+---------+-------+
| V-1   | 2026-03-01 | Ana     |  150  |
| V-2   | 2026-03-02 | Luis    |   80  |
+-------+------------+---------+-------+

count(*)               = 2
count(DISTINCT venta)  = 2   ← iguales

Cuadran: una fila es una venta. El nombre no engañaba.

3.2 Una fila por línea de venta.

+-------+------------+---------+----------+----------+---------+
| venta | fecha      | cliente | producto | cantidad | importe |
+-------+------------+---------+----------+----------+---------+
| V-1   | 2026-03-01 | Ana     | Café     |    2     |   100   |
| V-1   | 2026-03-01 | Ana     | Azúcar   |    1     |    50   |
| V-2   | 2026-03-02 | Luis    | Café     |    1     |    80   |
+-------+------------+---------+----------+----------+---------+

count(*)                          = 3
count(DISTINCT venta)             = 2   ← no cuadran
count(DISTINCT (venta, producto)) = 3   ← ahora sí

venta se repite, así que la fila no es una venta: es una venta y un producto. Fíjate en la pista visual — fecha y cliente también se duplican línea a línea. Cuando ves columnas repitiéndose así, el grano es más fino de lo que dice el nombre.

Es exactamente order_details, con sus 2.155 filas para 830 pedidos.

3.3 Una fila por venta y día.

+-------+------------+---------+---------+
| venta | fecha_foto | cliente | estado  |
+-------+------------+---------+---------+
| V-1   | 2026-03-01 | Ana     | abierta |
| V-1   | 2026-03-02 | Ana     | pagada  |
| V-1   | 2026-03-03 | Ana     | enviada |
| V-2   | 2026-03-02 | Luis    | abierta |
+-------+------------+---------+---------+

count(*)                            = 4
count(DISTINCT venta)               = 2   ← no cuadran
count(DISTINCT (venta, fecha_foto)) = 4   ← ahora sí

Aquí la venta no se parte por producto sino por día: cada fila es una foto del estado de esa venta ese día. V-1 aparece tres veces porque se siguió durante tres días. Es la forma típica de un histórico o de una tabla que llena un proceso diario.

3.4 La misma pregunta, tres respuestas.

La respuesta correcta es 2 en los tres casos. count(*) solo acierta en el primero, y nada en el nombre de la tabla te dice en cuál estás.

Y fíjate en lo peor: 3 y 4 no son cifras absurdas que salten a la vista. Son números creíbles. Un informe con el grano equivocado no da ningún error — da un número más grande, y nadie lo nota.

3.5 El procedimiento.

SELECT
    count(*),
    count(DISTINCT <columna>)
FROM
    <tabla>;

• Iguales → esa columna identifica la fila. Ese es el grano.
• count(*) mayor → la columna se repite, así que el grano es más fino. Añade columnas al DISTINCT hasta que cuadren.

Las columnas que necesitaste para que cuadre son el grano. Y no es solo una comprobación: es la forma de encontrarlo cuando nadie te lo ha dicho.

4. Revisar tablas

4.1 Con DBeaver

Doble clic en la tabla y ahí están las pestañas: Columns, Constraints, Foreign Keys, References. La pestaña ER Diagram dibuja el esquema solo.

4.2 Con psql

\dt              lista las tablas
\d orders        describe una tabla: columnas, tipos, PK, FK y quién le apunta
\d+ orders       lo mismo, más tamaño y comentarios

\d orders es el comando que más vas a usar en tu vida con PostgreSQL.

4.3 Con SQL puro

Cuando el programa (cliente) no ayuda, hay dos catálogos donde mirar. Las dos consultas de abajo responden exactamente a la misma pregunta y devuelven el mismo resultado — cambia quién puede ejecutarlas.

1. Opción A — pg_catalog (la que puedes ejecutar)

✅ Funciona con tu usuario del curso: pg_catalog no filtra por permisos. A cambio es específica de PostgreSQL — no existe en SQL Server ni en MySQL.

SELECT
    conrelid::regclass AS tabla,
    a.attname AS columna,
    confrelid::regclass AS apunta_a
FROM
    pg_constraint c
    JOIN pg_attribute a
        ON a.attrelid = c.conrelid
        AND a.attnum = ANY(c.conkey)
WHERE
    c.contype = 'f'
    AND c.connamespace = 'public'::regnamespace
ORDER BY
    conrelid::regclass::text, a.attname;

2. Opción B — information_schema (la portable)

⚠️ En el curso te devolverá vacío, y no es un error tuyo: exige permisos de escritura. Es SQL estándar y funciona en cualquier motor, por eso hay que conocerla. Con un usuario administrador da el mismo resultado que la opción A. Ver la sección 4.4.

SELECT
    tc.table_name AS tabla,
    kcu.column_name AS columna,
    ccu.table_name AS apunta_a
FROM
    information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
        ON kcu.constraint_name = tc.constraint_name
    JOIN information_schema.constraint_column_usage ccu
        ON ccu.constraint_name = tc.constraint_name
WHERE
    tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_schema = 'public'
ORDER BY
    1, 2;

El resultado, con cualquiera de las dos:

        tabla         |   columna    |  apunta_a
----------------------+--------------+-------------
 employee_territories | employee_id  | employees
 employee_territories | territory_id | territories
 employees            | reports_to   | employees      ← apunta a sí misma
 order_details        | order_id     | orders
 order_details        | product_id   | products
 orders               | customer_id  | customers
 orders               | employee_id  | employees
 orders               | ship_via     | shippers
 products             | category_id  | categories
 products             | supplier_id  | suppliers
 territories          | region_id    | region

Once claves foráneas y ahí está el mapa completo. En treinta segundos, sin que nadie te explique nada.

Y mira la tercera fila: employees.reports_to apunta a employees. Una tabla que se apunta a sí misma es siempre una jerarquía — aquí, quién le reporta a quién.

4.4 Por qué information_schema a veces devuelve vacío

Las vistas de information_schema no muestran todo el esquema: filtran por los permisos del usuario que consulta. Y el filtro se decide vista por vista, no es uniforme.

Mirando la definición de information_schema.table_constraints, los privilegios que acepta son INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES y TRIGGER. SELECT no está en la lista. Un usuario de solo lectura, como el del curso, obtiene cero filas — sin ningún mensaje de error, que es lo que hace perder la tarde: parece que la base no tuviera claves foráneas.

information_schema.columns, en cambio, sí acepta SELECT. Por eso las consultas de columnas de este mismo tema funcionan perfectamente con tu usuario.

pg_catalog no filtra por permisos en ninguna de sus tablas, y por eso la opción A funciona siempre.

Lo que hay que llevarse. Un resultado vacío no siempre significa que no haya datos. También puede significar que no tienes permiso para verlos. Ante un vacío sospechoso, comprueba la consulta con otro usuario antes de creerte el resultado.

5. Ejemplos sobre Northwind

5.1 Comprobar el grano. El procedimiento de la sección 3.5, ahora sobre datos de verdad. Si la clave primaria es order_id, cada pedido debe aparecer una sola vez:

SELECT
    count(*) AS filas,
    count(DISTINCT order_id) AS pedidos_distintos
FROM
    orders;

 filas | pedidos_distintos
-------+-------------------
   830 |               830

Iguales: el grano es "un pedido". Si no fueran iguales, tu idea del grano estaría mal — y toda suma que hagas después, también.

5.2 Ver qué puede faltar en una tabla. Las columnas que admiten NULL te dicen dónde el negocio permite ausencias:

SELECT
    column_name,
    is_nullable
FROM
    information_schema.columns
WHERE
    table_name = 'orders'
    AND is_nullable = 'YES'
ORDER BY
    ordinal_position;

   column_name    | is_nullable
------------------+-------------
 required_date    | YES
 shipped_date     | YES
 ship_via         | YES
 freight          | YES
 ship_name        | YES
 ...

shipped_date admite NULL. Eso no es un descuido: significa "pedido aún no despachado". El esquema te está contando una regla de negocio.

ship_via también admite nulos, pero es una clave foránea, y ahí hay una trampa.

Una FK promete que todo valor de esa columna existe en la otra tabla. Pero un NULL no es un valor: no hay nada que comprobar, así que la FK lo deja pasar. Una FK que admite nulos no garantiza al 100% — solo cubre las filas que tienen valor, y las que no lo tienen desaparecen en silencio al cruzar las tablas con un JOIN.

O sea que "aquí el negocio permite una ausencia" y "esta clave foránea garantiza la integridad" se contradicen en la misma columna. Por eso en una FK lo recomendable es NOT NULL, y quitarlo solo cuando puedas decir en voz alta la frase que lo justifica: "aún no se le ha asignado transportista" es una regla de negocio; "pues... podría pasar" es un descuido de diseño.

Y al leer un esquema conviene saber que NOT NULL es la restricción más fiable de todas: no se puede añadir a medias. Si el histórico tiene un solo NULL, PostgreSQL rechaza la restricción hasta que lo limpies. Así que una columna NOT NULL te dice algo fuerte —alguien la puso desde el principio, o limpió los datos para poder ponerla—, mientras que ver una clave foránea declarada no garantiza lo mismo sobre el pasado.

5.3 Ver qué puede faltar en todo el modelo. La misma pregunta, pero de una vez para las 12 tablas. Primero el resumen, para saber dónde mirar:

SELECT
    table_name AS tabla,
    count(*) AS columnas,
    count(*) FILTER (WHERE is_nullable = 'YES') AS nulables
FROM
    information_schema.columns
WHERE
    table_schema = 'public'
GROUP BY
    table_name
ORDER BY
    nulables DESC, table_name;

        tabla         | columnas | nulables
----------------------+----------+----------
 employees            |       16 |       13
 orders               |       14 |       10
 suppliers            |       12 |       10
 customers            |       11 |        9
 products             |       10 |        5
 us_states            |        4 |        3
 categories           |        3 |        1
 shippers             |        3 |        1
 employee_territories |        2 |        0
 order_details        |        5 |        0
 region               |        2 |        0
 territories          |        3 |        0

Y el detalle, cuando quieras la lista completa:

SELECT
    table_name AS tabla,
    column_name AS columna,
    data_type AS tipo
FROM
    information_schema.columns
WHERE
    table_schema = 'public'
    AND is_nullable = 'YES'
ORDER BY
    table_name, ordinal_position;

52 columnas nulables en todo el esquema. Tres lecturas:

Cuatro tablas con cero. order_details, employee_territories, region y territories no admiten un solo hueco. Son las tablas de relación y de catálogo: todo obligatorio. Son las más rígidas del modelo, y por tanto las más fiables.

employees con 13 de 16. Casi todo opcional —teléfono, dirección, notas, fecha de nacimiento—, que es lo razonable en datos de ficha que se rellenan cuando se tienen.

Y de las 52, solo dos son claves foráneas: employees.reports_to y orders.ship_via. Las otras cincuenta son atributos, donde un nulo significa nada más que "este dato falta". Esas dos son las únicas donde el nulo rompe una relación — que es exactamente lo que hay que ir a buscar.

6. Errores comunes

1. Empezar por SELECT * en la tabla más grande. Ver 2.155 filas no te dice nada del modelo. Empieza por las claves foráneas.
2. Asumir el grano por el nombre. Una tabla llamada ventas puede tener una fila por venta, por línea de venta o por venta y día — los tres casos de la sección 3. Compruébalo con el count(*) contra count(DISTINCT).
3. Ignorar los NULL. Cada columna nullable es una regla de negocio escrita en el esquema.
4. No mirar si una tabla se apunta a sí misma. Es la señal de una jerarquía, y cambia por completo cómo se consulta.

7. Ejercicios

1. Describe el grano de las 12 tablas de Northwind, en una frase cada una.
2. Comprueba con SQL que el grano de order_details es "un producto dentro de un pedido".
3. ¿Cuántas tablas apuntan a employees? ¿Qué significa cada una?

8. Pregunta de negocio

Te acaban de dar acceso a Northwind y tu jefe pregunta: "¿por qué canal llegan los pedidos más caros?". Antes de escribir una sola consulta, ¿qué tablas necesitas y cómo se conectan?

Responde con el camino de tablas, no con SQL.