Backend - Bases de Datos

WHERE user_id = ? -- Crear índice en user_id
WHERE status = 'active' -- Si hay muchos valores únicos

JOIN orders ON orders.user_id = users.id
-- Índice en orders.user_id

ORDER BY created_at DESC
-- Índice en created_at

GROUP BY category_id
-- Índice en category_id

-- Crear índice simple
CREATE INDEX idx_users_email ON users(email);

-- Consulta rápida (usa índice)
SELECT * FROM users WHERE email = 'user@example.com';

-- Índice compuesto
CREATE INDEX idx_orders_user_status 
ON orders(user_id, status, created_at);

-- Útil para: WHERE user_id = ? AND status = ? ORDER BY created_at

-- ✅ JOIN
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- ✅ SUBQUERY
SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE total > 1000
);

-- ✅ SUBQUERY con agregación
SELECT * FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

-- ✅ EXISTS (más eficiente que IN)
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

-- Obtener usuarios con sus órdenes
SELECT u.name, o.total, o.created_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- Menos eficiente
SELECT u.name,
  (SELECT total FROM orders WHERE user_id = u.id LIMIT 1) as total
FROM users u
WHERE u.created_at > '2024-01-01';

BEGIN TRANSACTION;

-- Transferencia de dinero
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Si todo OK
COMMIT;

-- Si hay error
ROLLBACK;

-- Transferencia bancaria (debe ser atómica)
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Si falla aquí, todo se revierte
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT; -- Confirma cambios
-- O ROLLBACK; -- Revierte todo

-- Tablas separadas
users (id, name, email)
orders (id, user_id, total, created_at)
order_items (id, order_id, product_id, quantity)
products (id, name, price)

-- Relaciones claras, sin redundancia

-- Agregar datos redundantes para evitar JOINs
orders (
  id,
  user_id,
  user_name,  -- Redundante, pero evita JOIN
  total,
  created_at
)

-- Tabla normalizada (fuente de verdad)
orders_normalized (id, user_id, total)
users_normalized (id, name)

-- Vista materializada desnormalizada (para lectura)
CREATE MATERIALIZED VIEW orders_denormalized AS
SELECT o.id, o.total, u.name as user_name
FROM orders_normalized o
JOIN users_normalized u ON o.user_id = u.id;

-- Refresh periódico
REFRESH MATERIALIZED VIEW orders_denormalized;

-- Consulta compleja con JOINs
SELECT u.name, COUNT(o.id) as order_count, SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5;

// Documento con datos anidados
{
  _id: ObjectId("..."),
  name: "Juan",
  orders: [
    { product: "Laptop", total: 1000 },
    { product: "Mouse", total: 20 }
  ]
}

// Consulta
db.users.find({
  "orders.total": { $gt: 500 }
});

// ✅ Embedding
{
  _id: ObjectId("..."),
  name: "Juan",
  address: {
    street: "123 Main St",
    city: "Madrid",
    zip: "28001"
  }
}

// ✅ Referencing
// Usuario
{
  _id: ObjectId("user123"),
  name: "Juan",
  orderIds: [ObjectId("order1"), ObjectId("order2")]
}

// Órdenes separadas
{
  _id: ObjectId("order1"),
  userId: ObjectId("user123"),
  total: 100
}

// Orden con pocos items
{
  _id: ObjectId("..."),
  userId: ObjectId("..."),
  items: [
    { product: "Laptop", quantity: 1, price: 1000 },
    { product: "Mouse", quantity: 2, price: 20 }
  ],
  total: 1040
}

// Orden
{
  _id: ObjectId("order123"),
  userId: ObjectId("user456"),
  itemIds: [ObjectId("item1"), ObjectId("item2"), ...],
  total: 1040
}

// Items separados
{
  _id: ObjectId("item1"),
  orderId: ObjectId("order123"),
  product: "Laptop",
  quantity: 1,
  price: 1000
}

[Escritura] → [Réplica A] ──┐
              [Réplica B] ──┼─→ [Eventualmente consistente]
              [Réplica C] ──┘

// Usuario actualiza perfil
UPDATE users SET name = 'Juan' WHERE id = 123;

// Réplica A: name = 'Juan' ✅
// Réplica B: name = 'Juan' ✅ (sincronizado)
// Réplica C: name = 'Juan Viejo' ⚠️ (aún no sincronizado)

// Después de unos segundos
// Réplica C: name = 'Juan' ✅ (ahora consistente)

// Usar versiones para detectar conflictos
{
  _id: "...",
  name: "Juan",
  version: 5
}

// Al actualizar, verificar versión
if (currentVersion !== expectedVersion) {
  // Conflict, resolver
}

// Usuario A sigue a Usuario B
// Puede tomar unos segundos en aparecer en todos los feeds
// Aceptable para este caso de uso

// Transferencia debe ser inmediata y consistente
// Eventual consistency NO es aceptable