Modelo de Datos de UnoSportClub

Este documento describe el modelo de datos completo de UnoSportClub, basado en PostgreSQL. El modelo está centrado en la entidad RESERVATION, que representa el núcleo del negocio: las reservas de canchas deportivas.

1. Visión General

1.1 Arquitectura de Datos

La base de datos PostgreSQL almacena todas las entidades del sistema y se accede exclusivamente a través de una API REST implementada en api/index.js.

Características principales: * PostgreSQL 12+ con extensión btree_gist para constraints EXCLUDE * Zona horaria: America/Bogota * Transacciones ACID para garantizar consistencia * Índices optimizados para consultas frecuentes * Funciones almacenadas para lógica de negocio compleja * Triggers para validaciones y actualizaciones automáticas

1.2 Entidad Central: RESERVATION

La entidad RESERVATION es el corazón del sistema y conecta todas las demás entidades:

  • Cada reserva puede tener un OPERATOR (quien la gestiona, opcional)

  • Cada reserva puede tener un CLIENT (quien la utiliza, opcional)

  • Cada reserva está asociada a una COURT (cancha reservada, obligatorio)

  • Cada reserva tiene un RESERVATION_TYPE (tipo de reserva, obligatorio)

  • Cada reserva puede tener un RESERVATION_STATUS (estado de la reserva, opcional)

  • Cada reserva puede tener múltiples PAYMENT (pagos)

  • Cada reserva puede tener múltiples DISCOUNT (descuentos aplicados)

  • El precio final de una reserva se calcula aplicando TARIFF al precio base de la COURT

2. Tablas del Sistema

2.1 USER

Tabla que actúa como espejo de Firebase Authentication. Almacena información de usuarios sincronizada desde Firebase Auth.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único (auto-incremental)

firebase_id

VARCHAR(255)

NOT NULL, UNIQUE

ID de Firebase Authentication

email

VARCHAR(255)

NULL

Email del usuario (único si no es NULL)

email_verified

BOOLEAN

NOT NULL, DEFAULT FALSE

Indica si el email está verificado

display_name

VARCHAR(255)

NULL

Nombre para mostrar

phone_number

VARCHAR(20)

NULL

Número de teléfono

disabled

BOOLEAN

NOT NULL, DEFAULT FALSE

Indica si el usuario está deshabilitado

creation_time

TIMESTAMP

NULL

Fecha de creación en Firebase

last_sign_in_time

TIMESTAMP

NULL

Última vez que inició sesión

last_refresh_time

TIMESTAMP

NULL

Última vez que se refrescó el token

tokens_valid_after_time

TIMESTAMP

NULL

Tiempo después del cual los tokens son válidos

Índices: * idx_user_firebase_id en firebase_id * idx_user_phone_number en phone_number * idx_user_disabled en disabled * idx_user_creation_time en creation_time * user_email_unique (único parcial) en email WHERE email IS NOT NULL

Relaciones: * Referenciada por: client.user_id, reservation.operator_id, class.trainer_id

2.2 CLIENT

Tabla que almacena información de clientes del sistema. Cada cliente está asociado a un usuario de Firebase.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

user_id

INTEGER

FK → USER.id, NOT NULL, UNIQUE

Usuario asociado (Firebase Auth)

first_name

VARCHAR(100)

NOT NULL

Nombre del cliente

last_name

VARCHAR(100)

NOT NULL

Apellido del cliente

document

VARCHAR(50)

NOT NULL

Número de documento

document_type_id

INTEGER

FK → DOCUMENT_TYPE.id, NOT NULL

Tipo de documento

address

VARCHAR(200)

NULL

Dirección del cliente

Índices: * idx_client_user en user_id * idx_client_document_type en document_type_id * idx_client_document en document

Relaciones: * user_id → user.id (ON DELETE CASCADE) * document_type_id → document_type.id * Referenciada por: reservation.client_id, enrollment.client_id

2.3 DOCUMENT_TYPE

Tabla que almacena los tipos de documento de identidad disponibles.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

name

VARCHAR(255)

NOT NULL, UNIQUE

Nombre del tipo de documento

description

TEXT

NULL

Descripción del tipo

Relaciones: * Referenciada por: client.document_type_id

2.4 COURT_TYPE

Tabla que almacena los tipos de cancha disponibles (ej: Fútbol 5, Fútbol 7, Básquet, Tenis).

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

name

VARCHAR(255)

NOT NULL, UNIQUE

Nombre del tipo de cancha

description

TEXT

NULL

Descripción del tipo

created_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de creación

Relaciones: * Referenciada por: court.court_type_id, tariff_class.court_type_id

2.5 COURT

Tabla que almacena las canchas disponibles para reservar.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

court_type_id

INTEGER

FK → COURT_TYPE.id, NOT NULL

Tipo de cancha

name

VARCHAR(255)

NOT NULL, UNIQUE

Nombre de la cancha

cost

DECIMAL(10,2)

DEFAULT 0.00

Costo de mantenimiento de la cancha

price

DECIMAL(10,2)

DEFAULT 0.00

Precio base de alquiler de la cancha

Relaciones: * court_type_id → court_type.id * Referenciada por: reservation.court_id, tariff.court_id, class.reservation_id

2.6 RESERVATION_TYPE

Tabla que almacena los tipos de reserva disponibles (ej: Normal, Recurrente, Evento, Torneo).

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

name

VARCHAR(255)

NOT NULL, UNIQUE

Nombre del tipo de reserva

description

TEXT

NULL

Descripción del tipo

Relaciones: * Referenciada por: reservation.reservation_type_id, tariff.reservation_type_id, tariff_class.reservation_type_id

2.7 RESERVATION_STATUS

Tabla que almacena los estados posibles de una reserva.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

name

VARCHAR(255)

NOT NULL, UNIQUE

Nombre del estado

description

TEXT

NULL

Descripción del estado

color

VARCHAR(7)

NOT NULL, DEFAULT '#3B82F6'

Color hexadecimal para representación visual

Índices: * idx_reservation_status_name en name

Relaciones: * Referenciada por: reservation.reservation_status_id

2.8 RESERVATION

Tabla principal que almacena las reservas de canchas.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

operator_id

INTEGER

FK → USER.id, NULL

Operador que gestiona la reserva (opcional)

client_id

INTEGER

FK → CLIENT.id, NULL

Cliente que realiza la reserva (opcional)

court_id

INTEGER

FK → COURT.id, NOT NULL

Cancha reservada

reservation_type_id

INTEGER

FK → RESERVATION_TYPE.id, NOT NULL

Tipo de reserva

reservation_status_id

INTEGER

FK → RESERVATION_STATUS.id, NULL

Estado de la reserva (si es NULL, se asigna uno por defecto)

checking

TIMESTAMP WITH TIME ZONE

NULL

Fecha y hora de entrada (check-in)

checkout

TIMESTAMP WITH TIME ZONE

NULL

Fecha y hora de salida (check-out)

notes

TEXT

NULL

Notas adicionales sobre la reserva

updated_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de última actualización

created_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de creación

Índices: * idx_reservation_court_dates en (court_id, checking, checkout) * idx_reservation_client_created en (client_id, created_at) * idx_reservation_status en reservation_status_id

Constraints Especiales: * reservation_no_overlap: Constraint EXCLUDE que previene solapamientos de reservas para la misma cancha usando tstzrange(checking, checkout) WITH && (solo cuando checking y checkout no son NULL)

Triggers: * trigger_check_reservation_overlap: Verifica solapamientos antes de INSERT/UPDATE * update_reservation_updated_at: Actualiza updated_at automáticamente en UPDATE

Relaciones: * operator_id → user.id (ON DELETE RESTRICT) * client_id → client.id * court_id → court.id * reservation_type_id → reservation_type.id * reservation_status_id → reservation_status.id * Referenciada por: payment.reservation_id, discount.reservation_id, class.reservation_id

2.9 TARIFF

Tabla que almacena ajustes de tarifas aplicables a las reservas. Permite modificar el precio base de una cancha según el tipo de reserva y rangos de fechas/horas.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

court_id

INTEGER

FK → COURT.id, NULL

Cancha específica (NULL = todas las canchas)

reservation_type_id

INTEGER

FK → RESERVATION_TYPE.id, NULL

Tipo de reserva (NULL = todos los tipos)

rrule

VARCHAR(500)

NULL

Regla recurrente según RFC 5545 (RRULE)

start_date

DATE

NULL

Fecha de inicio del rango de aplicación

end_date

DATE

NULL

Fecha de fin del rango de aplicación

start_time

TIME

NULL

Hora de inicio del rango diario

end_time

TIME

NULL

Hora de fin del rango diario

adjustment

VARCHAR(50)

NOT NULL

Ajuste de tarifa (ej: "-10%", "5000", "-2000", "15%")

description

TEXT

NULL

Descripción del ajuste

is_active

BOOLEAN

NOT NULL, DEFAULT TRUE

Indica si el ajuste está activo

created_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de creación

updated_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de última actualización

Índices: * idx_tariff_court en court_id * idx_tariff_reservation_type en reservation_type_id * idx_tariff_dates en (start_date, end_date) * idx_tariff_active en is_active

Triggers: * update_tariff_updated_at: Actualiza updated_at automáticamente en UPDATE

Relaciones: * court_id → court.id (ON DELETE CASCADE) * reservation_type_id → reservation_type.id (ON DELETE CASCADE)

Formato de Ajuste (adjustment): * Descuento vs Incremento: Si el valor comienza con -, es un descuento; de lo contrario, es un incremento. * Porcentaje vs Valor Neto: Si el valor termina con %, el ajuste es un porcentaje sobre el precio base; de lo contrario, es un valor neto. * Ejemplos: * "-10%": Descuento del 10% sobre el precio base * "15%": Incremento del 15% sobre el precio base * "-2000": Descuento de $2000 sobre el precio base * "5000": Incremento de $5000 sobre el precio base

2.10 TARIFF_CLASS

Tabla que une court_type y reservation_type para definir tarifas base y capacidades máximas.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

court_type_id

INTEGER

FK → COURT_TYPE.id, NOT NULL

Tipo de cancha

reservation_type_id

INTEGER

FK → RESERVATION_TYPE.id, NOT NULL

Tipo de reserva

price

DECIMAL(10,2)

NOT NULL, DEFAULT 0.00, CHECK (price >= 0)

Precio base para esta combinación

max_capacity

INTEGER

NOT NULL, DEFAULT 1, CHECK (max_capacity > 0)

Capacidad máxima de personas

created_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de creación

updated_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de última actualización

Índices: * idx_tariff_class_court_type_id en court_type_id * idx_tariff_class_reservation_type_id en reservation_type_id * idx_tariff_class_court_reservation en (court_type_id, reservation_type_id)

Constraints: * unique_tariff_class_court_reservation: UNIQUE en (court_type_id, reservation_type_id) * check_tariff_class_price: CHECK price >= 0 * check_tariff_class_capacity: CHECK max_capacity > 0

Triggers: * update_tariff_class_updated_at: Actualiza updated_at automáticamente en UPDATE

Relaciones: * court_type_id → court_type.id (ON DELETE CASCADE) * reservation_type_id → reservation_type.id (ON DELETE CASCADE)

2.11 PAYMENT_TYPE

Tabla que almacena los tipos de pago disponibles (ej: Efectivo, Tarjeta, Transferencia).

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

name

VARCHAR(255)

NOT NULL, UNIQUE

Nombre del tipo de pago

description

TEXT

NULL

Descripción del tipo

Índices: * idx_payment_type_name en name

Relaciones: * Referenciada por: payment.payment_type_id

2.12 PAYMENT

Tabla que almacena los pagos asociados a reservas. Importante: Esta tabla puede contener registros sin reservation_id (pagos huérfanos).

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

reservation_id

INTEGER

FK → RESERVATION.id, NULL

Reservación asociada (puede ser NULL)

payment_type_id

INTEGER

FK → PAYMENT_TYPE.id, NULL

Tipo de pago

amount

DECIMAL(10,2)

NOT NULL

Monto del pago

transaction_id

VARCHAR(255)

NOT NULL

ID de transacción del gateway (puede repetirse)

gateway_response

TEXT

NULL

Respuesta completa del gateway de pago

status

BOOLEAN

NOT NULL, DEFAULT FALSE

Estado del pago: false = pending, true = completed

date

TIMESTAMP

NOT NULL

Fecha del pago

description

TEXT

NULL

Descripción del pago

Índices: * idx_payment_transaction en transaction_id * idx_payment_reservation en reservation_id * idx_payment_payment_type en payment_type_id

Relaciones: * reservation_id → reservation.id (ON DELETE SET NULL) * payment_type_id → payment_type.id (ON DELETE SET NULL)

Pagos Huérfanos: Los pagos pueden existir sin reservación asignada (reservation_id = NULL). Esto ocurre cuando: 1. El webservice recibe un pago del gateway de pago sin conocer la reservación asociada 2. El sistema registra el pago con todos los campos disponibles pero sin reservation_id 3. Cuando el usuario está completando el proceso de pago, el sistema busca pagos huérfanos que coincidan (por transaction_id) 4. Si encuentra un pago huérfano coincidente, lo alinea actualizando reservation_id

2.13 DISCOUNT

Tabla que almacena descuentos aplicados a reservas.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

reservation_id

INTEGER

FK → RESERVATION.id, NOT NULL

Reservación asociada

amount

DECIMAL(10,2)

NOT NULL

Monto del descuento

Relaciones: * reservation_id → reservation.id (ON DELETE CASCADE)

2.14 CLASS

Tabla que almacena las clases de entrenamiento. Parte del módulo de entrenador.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

trainer_id

VARCHAR(255)

FK → USER.firebase_id, NOT NULL

ID del entrenador (Firebase UID)

reservation_id

INTEGER

FK → RESERVATION.id, NULL

Reservación asociada (opcional)

title

VARCHAR(255)

NOT NULL

Título de la clase

description

TEXT

NULL

Descripción de la clase

start_time

TIMESTAMP

NOT NULL

Fecha y hora de inicio

end_time

TIMESTAMP

NOT NULL

Fecha y hora de fin

status

BOOLEAN

DEFAULT TRUE

Estado activo/inactivo de la clase

start_date

DATE

NOT NULL

Fecha de inicio del período de clases

end_date

DATE

NOT NULL

Fecha de fin del período de clases

class_start_time

TIME

NOT NULL

Hora de inicio de cada clase

recurrence_days

TEXT[]

NOT NULL, DEFAULT ARRAY['MO']

Días de la semana en que se repite (MO, TU, WE, TH, FR, SA, SU)

created_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de creación

updated_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de última actualización

Índices: * idx_class_trainer_id en trainer_id * idx_class_reservation_id en reservation_id * idx_class_start_time en start_time * idx_class_status en status * idx_class_trainer_status en (trainer_id, status)

Constraints: * check_class_times: CHECK end_time > start_time * check_class_date_range: CHECK end_date >= start_date * check_class_recurrence_days: CHECK array_length(recurrence_days, 1) > 0 AND recurrence_days <@ ARRAY['MO','TU','WE','TH','FR','SA','SU']

Triggers: * trigger_update_class_updated_at: Actualiza updated_at automáticamente en UPDATE

Relaciones: * trainer_id → user.firebase_id (ON DELETE CASCADE) * reservation_id → reservation.id (ON DELETE SET NULL) * Referenciada por: enrollment.class_id

2.15 ENROLLMENT

Tabla que almacena las inscripciones de clientes a clases de entrenamiento.

Campo Tipo Restricciones Descripción

id

SERIAL

PK, NOT NULL

Identificador único

class_id

INTEGER

FK → CLASS.id, NOT NULL

Clase asociada

client_id

INTEGER

FK → CLIENT.id, NOT NULL

Cliente inscrito

status

VARCHAR(50)

NOT NULL, DEFAULT 'pending'

Estado de la inscripción: pending, accepted, rejected, cancelled

enrolled_at

TIMESTAMP

NOT NULL, DEFAULT CURRENT_TIMESTAMP

Fecha de inscripción

accepted_at

TIMESTAMP

NULL

Fecha de aceptación

rejected_at

TIMESTAMP

NULL

Fecha de rechazo

attendance_status

VARCHAR(50)

NULL

Estado de asistencia: present, absent

attendance_marked_at

TIMESTAMP

NULL

Fecha en que se marcó la asistencia

Índices: * idx_enrollment_class_id en class_id * idx_enrollment_client_id en client_id * idx_enrollment_status en status * idx_enrollment_class_status en (class_id, status)

Constraints: * unique_enrollment_class_client: UNIQUE en (class_id, client_id) * check_enrollment_status: CHECK status IN ('pending', 'accepted', 'rejected', 'cancelled') * check_enrollment_attendance_status: CHECK attendance_status IS NULL OR attendance_status IN ('present', 'absent')

Relaciones: * class_id → class.id (ON DELETE CASCADE) * client_id → client.id (ON DELETE CASCADE)

3. Funciones Almacenadas

3.1 update_updated_at_column()

Función genérica que actualiza el campo updated_at automáticamente.

Parámetros: Ninguno (función de trigger)

Retorna: TRIGGER

Descripción: Función utilizada por triggers para actualizar automáticamente el campo updated_at de una tabla cuando se modifica un registro.

Uso:

CREATE TRIGGER update_reservation_updated_at
  BEFORE UPDATE ON reservation
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

3.2 update_class_updated_at()

Función específica para actualizar updated_at en la tabla class.

Parámetros: Ninguno (función de trigger)

Retorna: TRIGGER

Descripción: Similar a update_updated_at_column() pero específica para la tabla class.

3.3 check_reservation_overlap()

Función que previene que una cancha tenga reservas solapadas en el tiempo.

Parámetros: Ninguno (función de trigger)

Retorna: TRIGGER

Descripción: * Se ejecuta antes de INSERT o UPDATE en la tabla reservation * Verifica que checkout sea posterior a checking (si ambos están definidos) * Previene que dos reservas para la misma cancha tengan rangos de tiempo solapados * Solo aplica cuando checking y checkout no son NULL

Lógica de Solapamiento: Dos rangos de tiempo se solapan si:

(checking1 < checkout2) AND (checkout1 > checking2)

Mensajes de Error: * Si checkout ⇐ checking: "La fecha de checkout debe ser posterior a la fecha de checking" * Si hay solapamiento: "La cancha {court_id} ya está reservada en el rango de tiempo especificado ({checking} - {checkout})"

Nota: Esta función complementa el constraint EXCLUDE reservation_no_overlap, que es la garantía principal a nivel de base de datos.

3.4 check_court_availability()

Función que verifica si una cancha está disponible en un rango de tiempo específico.

Parámetros: * p_court_id (INTEGER): ID de la cancha a verificar * p_checking (TEXT): Fecha y hora de inicio en formato texto * p_checkout (TEXT): Fecha y hora de fin en formato texto * p_reservation_id (INTEGER, opcional): ID de reserva a excluir de la verificación (útil para UPDATE)

Retorna: JSON con la siguiente estructura:

{
  "available": true/false,
  "overlap_count": 0,
  "overlapping_reservations": [
    {
      "id": 1,
      "checking": "2024-01-01 10:00:00+00",
      "checkout": "2024-01-01 12:00:00+00"
    }
  ]
}

Descripción: * Convierte los timestamps de texto a TIMESTAMP WITH TIME ZONE usando la zona horaria America/Bogota * Verifica solapamientos con reservas existentes * Retorna información detallada sobre disponibilidad y reservas solapadas

Ejemplo de Uso:

SELECT check_court_availability(1, '2024-01-01 10:00:00', '2024-01-01 12:00:00', NULL);

3.5 calculate_reservation_price()

Función que calcula el precio de una reserva aplicando tarifas y ajustes.

Parámetros: * p_court_id (INTEGER): ID de la cancha * p_checking (TEXT): Fecha y hora de inicio en formato texto * p_checkout (TEXT): Fecha y hora de fin en formato texto * p_reservation_type_id (INTEGER, opcional): ID del tipo de reserva

Retorna: JSON con la siguiente estructura:

{
  "total_price": 50000.00,
  "price_per_hour": 25000.00,
  "hours": 2.0,
  "applied_adjustments": []
}

Descripción: * Calcula el precio total de una reserva considerando: * Precio base de la cancha * Horas de duración (mínimo 1 hora) * Tarifas aplicables según: * Cancha específica o todas las canchas * Tipo de reserva * Rango de fechas (start_date, end_date) * Rango de horas (start_time, end_time) * Procesa cada hora individualmente para aplicar tarifas específicas * Aplica ajustes fijos primero, luego ajustes porcentuales * Retorna precio total, precio por hora promedio y horas calculadas

Lógica de Cálculo: 1. Obtiene el precio base de la cancha 2. Calcula las horas entre checking y checkout 3. Para cada hora: * Busca tarifas aplicables según intersección de rangos * Aplica ajustes fijos (suma/resta de valores) * Aplica ajustes porcentuales (multiplicación) * Calcula precio proporcional si es menos de 1 hora 4. Suma todos los precios de horas 5. Calcula precio promedio por hora

Ejemplo de Uso:

SELECT calculate_reservation_price(1, '2024-01-01 10:00:00', '2024-01-01 12:00:00', 1);

4. Triggers

4.1 update_reservation_updated_at

Tabla: reservation

Evento: BEFORE UPDATE

Función: update_updated_at_column()

Descripción: Actualiza automáticamente el campo updated_at cada vez que se modifica un registro de reserva.

4.2 trigger_check_reservation_overlap

Tabla: reservation

Evento: BEFORE INSERT OR UPDATE

Función: check_reservation_overlap()

Descripción: Verifica solapamientos de reservas antes de insertar o actualizar. Complementa el constraint EXCLUDE reservation_no_overlap.

4.3 update_tariff_updated_at

Tabla: tariff

Evento: BEFORE UPDATE

Función: update_updated_at_column()

Descripción: Actualiza automáticamente el campo updated_at en la tabla tariff.

4.4 update_tariff_class_updated_at

Tabla: tariff_class

Evento: BEFORE UPDATE

Función: update_updated_at_column()

Descripción: Actualiza automáticamente el campo updated_at en la tabla tariff_class.

4.5 trigger_update_class_updated_at

Tabla: class

Evento: BEFORE UPDATE

Función: update_class_updated_at()

Descripción: Actualiza automáticamente el campo updated_at en la tabla class.

5. Constraints Especiales

5.1 reservation_no_overlap

Tabla: reservation

Tipo: EXCLUDE

Definición:

EXCLUDE USING gist (
  court_id WITH =,
  tstzrange(checking, checkout) WITH &&
)
WHERE (checking IS NOT NULL AND checkout IS NOT NULL)

Descripción: Constraint EXCLUDE que previene solapamientos de reservas para la misma cancha. Usa tstzrange para crear rangos de tiempo y el operador && para detectar solapamientos. Es imposible crear reservas solapadas mientras este constraint exista.

Ventajas sobre el trigger: * Se ejecuta a nivel de base de datos, no se puede evitar * Es más eficiente que el trigger * Previene solapamientos incluso en operaciones concurrentes

Requisito: Requiere la extensión btree_gist de PostgreSQL.

6. Relaciones del Modelo

6.1 Relaciones Principales de RESERVATION

  • RESERVATION → USER (operator_id): Muchas reservas pueden ser gestionadas por un operador (opcional)

  • RESERVATION → CLIENT (client_id): Muchas reservas pueden pertenecer a un cliente (opcional)

  • RESERVATION → COURT (court_id): Muchas reservas pertenecen a una cancha (obligatorio)

  • RESERVATION → RESERVATION_TYPE (reservation_type_id): Muchas reservas tienen un tipo (obligatorio)

  • RESERVATION → RESERVATION_STATUS (reservation_status_id): Muchas reservas tienen un estado (opcional)

  • RESERVATION → PAYMENT (reservation_id): Una reserva puede tener múltiples pagos (relación opcional)

  • RESERVATION → DISCOUNT (reservation_id): Una reserva puede tener múltiples descuentos

  • RESERVATION → CLASS (reservation_id): Una reserva puede estar asociada a una clase (opcional)

6.2 Relaciones de Usuario

  • CLIENT → USER (user_id): Un cliente es un usuario (1:1, UNIQUE)

  • CLASS → USER (trainer_id): Muchas clases pertenecen a un entrenador (usando firebase_id)

6.3 Relaciones de Cancha

  • COURT → COURT_TYPE (court_type_id): Una cancha tiene un tipo

  • TARIFF → COURT (court_id): Un ajuste puede aplicarse a una cancha específica (opcional, NULL = todas)

  • TARIFF → RESERVATION_TYPE (reservation_type_id): Un ajuste puede aplicarse a un tipo de reserva (opcional, NULL = todos)

  • TARIFF_CLASS → COURT_TYPE (court_type_id): Una tarifa base está asociada a un tipo de cancha

  • TARIFF_CLASS → RESERVATION_TYPE (reservation_type_id): Una tarifa base está asociada a un tipo de reserva

6.4 Relaciones de Cliente

  • CLIENT → DOCUMENT_TYPE (document_type_id): Un cliente tiene un tipo de documento

  • ENROLLMENT → CLIENT (client_id): Muchas inscripciones pertenecen a un cliente

6.5 Relaciones de Pago

  • PAYMENT → RESERVATION (reservation_id): Un pago puede estar asociado a una reserva (opcional, puede ser NULL)

  • PAYMENT → PAYMENT_TYPE (payment_type_id): Un pago tiene un tipo (opcional)

6.6 Relaciones de Clases

  • CLASS → RESERVATION (reservation_id): Una clase puede estar asociada a una reserva (opcional)

  • ENROLLMENT → CLASS (class_id): Muchas inscripciones pertenecen a una clase

7. Índices Recomendados

Todos los índices mencionados en las secciones de tablas están creados en la base de datos. Los índices más importantes para optimizar consultas frecuentes son:

7.1 Índices de RESERVATION

  • idx_reservation_court_dates: Búsqueda de disponibilidad por cancha y rango de fechas

  • idx_reservation_client_created: Reservas por cliente ordenadas por fecha de creación

  • idx_reservation_status: Filtrado por estado de reserva

7.2 Índices de PAYMENT

  • idx_payment_transaction: Búsqueda de pagos huérfanos por transaction_id

  • idx_payment_reservation: Pagos por reserva

  • idx_payment_payment_type: Filtrado por tipo de pago

7.3 Índices de CLIENT

  • idx_client_user: Búsqueda de cliente por usuario

  • idx_client_document: Búsqueda por número de documento

  • idx_client_document_type: Filtrado por tipo de documento

7.4 Índices de TARIFF

  • idx_tariff_court: Ajustes por cancha

  • idx_tariff_reservation_type: Ajustes por tipo de reserva

  • idx_tariff_dates: Búsqueda de ajustes por rango de fechas

  • idx_tariff_active: Filtrado de ajustes activos

7.5 Índices de CLASS y ENROLLMENT

  • idx_class_trainer_id: Clases por entrenador

  • idx_class_status: Filtrado de clases activas/inactivas

  • idx_enrollment_class_id: Inscripciones por clase

  • idx_enrollment_client_id: Inscripciones por cliente

  • idx_enrollment_status: Filtrado por estado de inscripción

8. Referencias