Proyecto: Analítica de Ventas

De preguntas de negocio a un modelo de datos

Antes de escribir una sola consulta analítica necesitas un buen modelo de datos. Si el esquema está mal diseñado, las consultas de tendencias, top-N y cohortes que construirás en los próximos hitos serán difíciles de escribir, lentas o —lo peor— incorrectas sin que lo notes.

En esta lección diseñamos el esquema completo del proyecto: cinco tablas (categories, products, customers, orders y order_items), sus claves primarias y foráneas, y la cardinalidad de cada relación. No vamos a crear todavía las tablas (eso es la próxima lección); aquí decidimos cómo deben verse y por qué. Un esquema bien pensado es el que hace que el resto del proyecto fluya.

Al terminar entenderás tres cosas que separan un esquema amateur de uno profesional:

1. Por qué un pedido se parte en dos tablas (orders y order_items).
2. Dónde debe vivir el precio y por qué.
3. Qué representa exactamente una fila de cada tabla —su granularidad— porque de eso depende que tus SUM, COUNT y JOIN den el número correcto.

Las cinco entidades del dominio

Estamos modelando una tienda que vende productos a clientes. Pensando en el negocio aparecen cinco conceptos naturales, y cada uno se convierte en una tabla:

La pista para encontrar entidades es buscar los sustantivos del dominio: "un cliente hace un pedido que contiene varios productos, cada uno de una categoría". Cada sustantivo importante que tiene identidad propia y atributos suele merecer su propia tabla.

El esquema completo (diagrama ERD)

Este es el modelo entidad-relación que vamos a construir. Las claves PK (primaria) identifican cada fila de forma única; las FK (foránea) apuntan a la PK de otra tabla y crean la relación. Lee los conectores como "uno a muchos": un cliente tiene muchos pedidos, un pedido tiene muchas líneas, y así.

Cómo leer la cardinalidad

La notación de "pata de gallo" (crow's foot) en los extremos del conector indica cuántas filas de un lado se relacionan con el otro. En este modelo todas las relaciones son uno-a-muchos (||--o{):

Un detalle clave: order_items tiene dos claves foráneas (order_id y product_id). Esto es la marca de una tabla de unión (junction table) que resuelve una relación muchos-a-muchos: un pedido puede incluir muchos productos, y un producto puede estar en muchos pedidos. order_items es justamente la tabla que materializa esos cruces, una fila por cada combinación pedido-producto.

Decisión 1: por qué separar orders de order_items

La tentación del principiante es guardar todo el pedido en una sola tabla orders con columnas como product_1, product_2, product_3… Es un error clásico, porque un pedido puede tener cualquier número de productos. Ningún número fijo de columnas es suficiente, y consultar "¿cuántas unidades del producto X se vendieron?" se vuelve imposible.

La solución correcta es separar dos niveles de información:

• orders (la cabecera): los datos que son verdad una vez por pedido — quién compró (customer_id), cuándo (order_date) y en qué estado está (status). No importa si el pedido tiene 1 o 50 productos: esa fecha y ese cliente son únicos para el pedido entero.
• order_items (el detalle): los datos que se repiten por cada producto del pedido — qué producto, cuántas unidades y a qué precio.

Esta separación cabecera/detalle es el patrón estándar de cualquier sistema de pedidos (e-commerce, facturación, POS). Evita repetir la fecha y el cliente en cada línea, permite pedidos de tamaño variable, y deja cada hecho registrado en un solo lugar. Es la normalización mínima razonable: separar lo que se repite de lo que no.

Decisión 2: ¿dónde vive el precio?

Fíjate en que unit_price aparece dos veces en el modelo: en products y en order_items. No es un error: cada una responde a una pregunta distinta.

• products.unit_price es el precio de catálogo actual del producto. Cambia con el tiempo: subes precios, haces ofertas, ajustas por inflación.
• order_items.unit_price es el precio al que realmente se vendió esa línea, congelado en el momento de la compra.

¿Por qué duplicarlo? Porque el precio de catálogo cambia, pero un pedido del año pasado debe seguir reflejando lo que el cliente pagó entonces. Si para calcular ingresos históricos hicieras JOIN con products y usaras su precio actual, recalcularías el pasado cada vez que cambia una etiqueta de precio —y tus reportes de ingresos serían distintos cada semana sin que nadie venda nada nuevo.

Esto se llama valor histórico o price snapshot: en sistemas transaccionales se copia deliberadamente el precio (y a veces el nombre) en la línea de venta para preservar la verdad del momento. Es una excepción consciente a la regla de "no duplicar datos", y es la decisión correcta. En este proyecto, los ingresos siempre se calculan desde order_items:

$\text{ingreso de una línea} = \text{quantity} \times \text{unit\_price}$

y el ingreso de un pedido es la suma de sus líneas.

Decisión 3: normalización mínima razonable

Separamos categories de products en lugar de poner un texto category directo en cada producto. ¿Por qué? Porque la categoría es una entidad con identidad propia que se repite en muchos productos. Tenerla en su propia tabla aporta:

• Consistencia: "Electrónica" se escribe en un único sitio; nunca conviven "Electronica", "electrónica" y "Electrónics" que romperían tus GROUP BY.
• Integridad referencial: la FK products.category_id garantiza que ningún producto apunte a una categoría inexistente.
• Extensibilidad: mañana puedes añadir a categories columnas como parent_category_id o margin_target sin tocar millones de filas de productos.

A la vez, no sobre-normalizamos. Guardamos country como texto dentro de customers en vez de crear una tabla countries, y status como texto en orders en vez de una tabla statuses. Para un proyecto analítico esa normalización extra añadiría JOINs sin beneficio real. El criterio práctico: normaliza cuando una entidad tiene identidad y atributos propios o se reutiliza mucho; déjalo plano cuando es solo una etiqueta. Buscamos un punto sano —ni un único "mega-tablón" con todo repetido, ni una explosión de micro-tablas que obliga a diez JOINs para una pregunta simple.

El concepto más importante: la granularidad de cada tabla

La granularidad (o grain) de una tabla es la respuesta a la pregunta: "¿qué representa exactamente una fila?". Definirla con precisión es lo que hace que tus agregaciones sean correctas. Si no sabes qué cuenta una fila, no sabes qué cuenta tu COUNT(*).

Esta distinción es la fuente de los errores más comunes en analítica, así que internalízala desde ya:

• Para contar pedidos, cuentas filas de orders (o COUNT(DISTINCT order_id)).
• Para contar unidades vendidas, sumas quantity en order_items.
• Para calcular ingresos, sumas quantity * unit_price en order_items.

El error clásico: hacer JOIN de orders con order_items y luego contar order_id. Como un pedido de 3 líneas se convierte en 3 filas tras el JOIN, ese pedido se cuenta 3 veces. La regla de oro: un JOIN lleva tu resultado a la granularidad de la tabla más fina (más detallada) —aquí, order_items— y debes agregar con eso en mente (COUNT(DISTINCT …) o agrupando antes de unir).

Cómo se verá en SQL (vista previa del DDL)

En la próxima lección escribirás y ejecutarás el CREATE TABLE completo con los datos. A modo de anticipo, así se traduce el modelo a DDL de PostgreSQL —observa cómo REFERENCES materializa cada flecha del diagrama ERD y cómo order_items referencia a las dos tablas a la vez:

CREATE TABLE categories (
    category_id  INT PRIMARY KEY,
    name         TEXT NOT NULL
);

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    category_id  INT NOT NULL REFERENCES categories(category_id),
    name         TEXT NOT NULL,
    unit_price   NUMERIC(10,2) NOT NULL   -- precio de catalogo actual
);

CREATE TABLE customers (
    customer_id  INT PRIMARY KEY,
    name         TEXT NOT NULL,
    country      TEXT,
    signup_date  DATE NOT NULL
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customers(customer_id),
    order_date   DATE NOT NULL,
    status       TEXT NOT NULL            -- p.ej. 'completed', 'cancelled'
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id      INT NOT NULL REFERENCES orders(order_id),
    product_id    INT NOT NULL REFERENCES products(product_id),
    quantity      INT NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10,2) NOT NULL  -- precio congelado al comprar
);

Conclusiones clave

• El dominio de ventas se modela con cinco tablas: categories, products, customers, orders y order_items, todas en relaciones uno-a-muchos.
• Un pedido se parte en cabecera (orders) y detalle (order_items) para soportar un número variable de productos sin repetir datos del pedido.
• order_items es una tabla de unión con dos FK (order_id, product_id) que resuelve el muchos-a-muchos entre pedidos y productos.
• El precio vive en dos sitios a propósito: el de catálogo en products (cambia) y el congelado en order_items (verdad histórica). Los ingresos se calculan siempre desde order_items.
• Aplicamos una normalización mínima razonable: tabla aparte para entidades con identidad (categorías), texto plano para simples etiquetas (country, status).
• La granularidad —qué es una fila— es la brújula de toda consulta correcta: cuenta pedidos en orders, unidades e ingresos en order_items, y cuidado con que un JOIN lleve todo al grano más fino.

Con este esquema claro en la cabeza, en la siguiente lección crearás las tablas y cargarás el dataset para empezar a consultarlo.

Comprueba tu comprensión

Antes de seguir, asegúrate de que entiendes las decisiones de diseño que acabas de ver. Responde estas preguntas mentalmente (o en tu libreta) y luego revisa las respuestas más abajo.

Pregunta 1: Si quieres contar cuántos pedidos se hicieron en enero de 2024, ¿en qué tabla cuentas filas? ¿Por qué?

Pregunta 2: Supón que un pedido tiene 4 productos distintos. Después de hacer SELECT * FROM orders JOIN order_items USING (order_id) WHERE order_id = 123, ¿cuántas filas aparecerán en el resultado? ¿Por qué?

Pregunta 3: ¿Por qué order_items tiene una columna unit_price cuando ya existe products.unit_price? ¿Qué pasaría si calculáramos los ingresos de ventas históricas usando directamente el precio de products?

Pregunta 4: ¿Qué representa exactamente una fila de la tabla order_items?

---

Respuestas

Respuesta 1: Cuentas filas en orders (o COUNT(DISTINCT order_id) si ya hiciste un JOIN). Cada fila de orders representa un pedido completo, así que COUNT(*) de orders te da el número de pedidos. La granularidad de order_items es más fina (una línea por producto dentro del pedido), así que contar ahí te daría el número de líneas vendidas, no de pedidos.

Respuesta 2: Aparecerán 4 filas, una por cada línea del pedido (cada producto). El JOIN lleva el resultado a la granularidad de order_items, la tabla más fina. Ese pedido tiene una sola fila en orders pero cuatro en order_items, así que tras el JOIN se expande a cuatro filas.

Respuesta 3: order_items.unit_price congela el precio al momento de la venta (valor histórico), mientras que products.unit_price refleja el precio de catálogo actual y cambia con el tiempo. Si calcularas ingresos históricos con el precio de products, cada vez que subes o bajas un precio estarías recalculando el pasado —tus reportes de ingresos cambiarían sin que nadie haya comprado nada nuevo. Para preservar la verdad del momento de la transacción, copiamos el precio a order_items cuando se crea el pedido.

Respuesta 4: Una fila de order_items es una línea de un pedido: un producto específico, dentro de un pedido específico, con su cantidad y el precio al que se vendió. Es la granularidad más fina del esquema: un pedido se expande en tantas filas de order_items como productos tenga.