La panadería, etapa 1: la base de datos y las tablas
En resumen: La etapa 1 del proyecto va de la descripción de una panadería de barrio al plan de tablas y a cinco sentencias CREATE TABLE con las reglas que mantienen los datos correctos.
Problema
La panadería de la esquina
Una panadería de barrio quiere recibir pedidos por internet. Vende pan, dulces y bebidas. Los clientes crean un perfil con nombre, correo y ciudad. Un pedido tiene una fecha, un estado (nuevo, pagado, entregado, cancelado) y unas cuantas líneas: qué producto, cuántas unidades y a qué precio. La panadería también quiere saber cuánto queda de cada producto en existencias.
En este proyecto construyes tú mismo toda la base de datos: desde la base vacía, pasando por las tablas, los datos, las consultas y los cambios, hasta la copia de seguridad y el DROP del final. Cada etapa parte de lo que hiciste en las anteriores.
La idea
El plan
Antes del primer CREATE TABLE decides qué cosas hay y cómo se relacionan. Aquí son cinco:
| Tabla | Qué guarda | Apunta a |
|---|---|---|
categories |
las categorías: pan, dulces, bebidas | |
products |
los productos con precio y existencias | categories |
customers |
los clientes con correo y ciudad | |
orders |
los pedidos con fecha y estado | customers |
order_items |
las líneas de los pedidos | orders y products |
Todas las tablas tienen id INTEGER PRIMARY KEY, salvo order_items: ahí la clave es la pareja de pedido y producto, porque un producto es una línea del pedido. Las tablas a las que se apunta se crean primero.
La idea
La base vacía
En tu computadora la base se crea con sqlite3 bakery.db (en PostgreSQL con CREATE DATABASE bakery; y \c bakery). Los campos de este proyecto parten de una base vacía, sin la tienda de las otras lecciones:
Pruébalo
Las categorías
Crea la tabla categories: id es la clave primaria, y name es obligatorio y no puede repetirse (no puede haber dos categorías «Pan»).
Solución
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);name TEXT NOT NULL UNIQUE. La comprobación intenta escribir dos categorías «Pan» y una sin nombre, y cuenta cuántas entraron: tiene que ser una. Sin UNIQUE entran dos, y sin NOT NULL también la categoría sin nombre.
Míralo
Otra vez
Una tabla con el mismo nombre no se puede crear de nuevo:
Un script que se puede ejecutar más de una vez escribe IF NOT EXISTS. Entonces la tabla que ya existe no se toca, aunque tenga otras columnas:
Pruébalo
Los productos
Añade las reglas de products: name es obligatorio, category_id es obligatorio y apunta a categories, price es obligatorio y mayor que 0, y stock es obligatorio, vale 0 por defecto y no puede ser menor que 0.
Solución
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category_id INTEGER NOT NULL REFERENCES categories(id),
price REAL NOT NULL CHECK (price > 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);category_id INTEGER NOT NULL REFERENCES categories(id), price REAL NOT NULL CHECK (price > 0) y stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0). La comprobación prueba un producto con precio 0, uno con existencias -1 y uno sin nombre, y todos tienen que ser rechazados. Un producto sin stock debe quedar con 0.
Más precisamente, para curiosos
Los precios aquí son REAL, como en la tienda del curso. Un programa real que maneja dinero guarda los céntimos como número entero (price_cents INTEGER) o usa NUMERIC(10, 2) en PostgreSQL, para que no haya sumas como 0,1 + 0,2 = 0,30000000000000004.
Pruébalo
Los clientes
Añade las reglas de customers: name y email son obligatorios, email no puede repetirse, y city puede faltar.
Solución
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT
);email TEXT NOT NULL UNIQUE, y city TEXT sin NOT NULL. Con el correo el cliente inicia sesión, y dos personas con un mismo correo son un error. La ciudad no es obligatoria: un cliente sin ciudad también puede pedir.
Pruébalo
Los pedidos
Añade las reglas de orders: customer_id es obligatorio y apunta a customers, ordered_at es obligatorio, y status es obligatorio, vale 'new' por defecto y solo puede ser 'new', 'paid', 'delivered' o 'cancelled'.
Solución
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
ordered_at TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'new'
CHECK (status IN ('new', 'paid', 'delivered', 'cancelled'))
);status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'paid', 'delivered', 'cancelled')). La comprobación prueba el estado 'lost' y un pedido sin fecha, y un pedido sin estado debe quedar como 'new'.
Ejercicio
Las líneas del pedido
Añade las reglas de order_items: order_id apunta a orders, y cuando se borra el pedido se borran con él sus líneas; product_id apunta a products; quantity es obligatorio y mayor que 0; unit_price es obligatorio; la clave primaria es la pareja (order_id, product_id). Todas las columnas son obligatorias.
Solución
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price REAL NOT NULL,
PRIMARY KEY (order_id, product_id)
);REFERENCES orders(id) ON DELETE CASCADE y al final PRIMARY KEY (order_id, product_id). La comprobación escribe el mismo producto dos veces en el pedido (la segunda línea la tiene que rechazar la clave), una línea con 0 unidades, después borra el pedido y comprueba que sus líneas desaparecieron. Con un id propio para la línea, un producto podría ser dos líneas del mismo pedido.
La idea
Las cinco tablas
La base está lista:
Cada regla que añadiste es un error que ningún programa podrá escribir: un producto sin precio, dos clientes con un mismo correo, un pedido con un estado que no existe. La siguiente etapa llena las tablas.
Test