El modelo de datos de SIGA está diseñado para capturar la complejidad operativa del transporte mientras mantiene la flexibilidad para evolucionar. Utiliza PostgreSQL como base de datos principal con extensiones para datos geoespaciales y series temporales.
CREATE TABLE siga.vehicles (
id SERIAL PRIMARY KEY,
plate VARCHAR(20) UNIQUE NOT NULL,
vin VARCHAR(17) UNIQUE,
-- Características físicas
type VARCHAR(50) NOT NULL CHECK (type IN ('TRAILER', 'RIGID', 'VAN')),
capacity_kg INTEGER NOT NULL,
capacity_m3 DECIMAL(5,2),
max_length_m DECIMAL(4,2),
-- Estado actual
current_position GEOGRAPHY(POINT, 4326),
current_zone VARCHAR(50),
status VARCHAR(50) DEFAULT 'AVAILABLE',
next_available TIMESTAMP,
current_driver_id INTEGER REFERENCES siga.drivers(id),
-- Capacidades especiales
capabilities JSONB DEFAULT '{}',
/* Ejemplo:
{
"adr": true,
"refrigerated": true,
"tail_lift": true,
"crane": false
}
*/
-- Metadata
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
active BOOLEAN DEFAULT true,
-- Índices
INDEX idx_vehicles_status (status),
INDEX idx_vehicles_zone (current_zone),
INDEX idx_vehicles_position (current_position),
INDEX idx_vehicles_available (next_available)
);
-- Trigger para updated_at
CREATE TRIGGER update_vehicles_updated_at
BEFORE UPDATE ON siga.vehicles
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
CREATE TABLE siga.drivers (
id SERIAL PRIMARY KEY,
employee_code VARCHAR(50) UNIQUE NOT NULL,
-- Información personal
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
phone VARCHAR(20),
-- Información laboral
license_number VARCHAR(50) NOT NULL,
license_expiry DATE NOT NULL,
license_categories VARCHAR[] DEFAULT '{"B"}',
-- Certificaciones
certifications JSONB DEFAULT '{}',
/* Ejemplo:
{
"adr": {
"valid": true,
"expiry": "2025-06-15"
},
"cap": {
"valid": true,
"expiry": "2026-03-20"
}
}
*/
-- Estado
status VARCHAR(50) DEFAULT 'ACTIVE',
base_location VARCHAR(50),
current_vehicle_id INTEGER REFERENCES siga.vehicles(id),
-- Preferencias y restricciones
preferences JSONB DEFAULT '{}',
/* Ejemplo:
{
"max_daily_km": 500,
"preferred_zones": ["BCN", "VAL"],
"avoid_nights": true
}
*/
-- Metadata
hired_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
-- Índices
INDEX idx_drivers_status (status),
INDEX idx_drivers_base (base_location)
);
CREATE TABLE siga.customers (
id SERIAL PRIMARY KEY,
code VARCHAR(50) UNIQUE NOT NULL,
-- Información básica
legal_name VARCHAR(255) NOT NULL,
trade_name VARCHAR(255),
tax_id VARCHAR(50) UNIQUE NOT NULL,
-- Clasificación
type VARCHAR(50) DEFAULT 'REGULAR',
segment VARCHAR(50),
importance VARCHAR(20) DEFAULT 'NORMAL'
CHECK (importance IN ('VIP', 'HIGH', 'NORMAL', 'LOW')),
-- Contacto
primary_contact JSONB,
/* Ejemplo:
{
"name": "Juan Pérez",
"email": "juan@empresa.com",
"phone": "+34 666 777 888",
"role": "Logistics Manager"
}
*/
-- Configuración comercial
payment_terms INTEGER DEFAULT 30, -- días
credit_limit DECIMAL(12,2),
default_revenue_per_order DECIMAL(10,2),
-- Requisitos especiales
requirements JSONB DEFAULT '{}',
/* Ejemplo:
{
"pod_required": true,
"photo_delivery": true,
"real_time_tracking": true,
"custom_integration": "EDI"
}
*/
-- SLAs
sla_config JSONB DEFAULT '{}',
/* Ejemplo:
{
"on_time_target": 0.95,
"damage_rate_max": 0.01,
"communication_protocol": "API"
}
*/
-- Metadata
contract_start DATE,
contract_end DATE,
status VARCHAR(50) DEFAULT 'ACTIVE',
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
-- Índices
INDEX idx_customers_status (status),
INDEX idx_customers_importance (importance),
INDEX idx_customers_segment (segment)
);
CREATE TABLE siga.orders (
id SERIAL PRIMARY KEY,
reference VARCHAR(100) UNIQUE NOT NULL,
external_reference VARCHAR(100), -- Referencia del cliente
-- Relaciones
customer_id INTEGER NOT NULL REFERENCES siga.customers(id),
-- Ubicaciones
origin_address TEXT NOT NULL,
origin_point GEOGRAPHY(POINT, 4326) NOT NULL,
origin_zone VARCHAR(50),
destination_address TEXT NOT NULL,
destination_point GEOGRAPHY(POINT, 4326) NOT NULL,
destination_zone VARCHAR(50),
-- Ventanas temporales
pickup_window TSTZRANGE NOT NULL,
delivery_window TSTZRANGE NOT NULL,
-- Información de carga
cargo_weight_kg INTEGER NOT NULL,
cargo_volume_m3 DECIMAL(6,2),
cargo_type VARCHAR(50),
special_requirements JSONB DEFAULT '{}',
-- Información económica
agreed_revenue DECIMAL(10,2) NOT NULL,
estimated_cost DECIMAL(10,2),
actual_cost DECIMAL(10,2),
-- Estado
status VARCHAR(50) DEFAULT 'PENDING',
priority VARCHAR(20) DEFAULT 'NORMAL',
-- Tracking
assigned_vehicle_id INTEGER REFERENCES siga.vehicles(id),
assigned_driver_id INTEGER REFERENCES siga.drivers(id),
assigned_at TIMESTAMP,
-- Metadata
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
created_by VARCHAR(100),
-- Índices
INDEX idx_orders_status (status),
INDEX idx_orders_customer (customer_id),
INDEX idx_orders_pickup_window (pickup_window),
INDEX idx_orders_zones (origin_zone, destination_zone),
INDEX idx_orders_assigned_vehicle (assigned_vehicle_id)
);
-- Índice para búsquedas geoespaciales
CREATE INDEX idx_orders_origin_point ON siga.orders USING GIST (origin_point);
CREATE INDEX idx_orders_destination_point ON siga.orders USING GIST (destination_point);
CREATE TABLE siga.zones (
id SERIAL PRIMARY KEY,
code VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
-- Geometría
polygon GEOGRAPHY(POLYGON, 4326) NOT NULL,
center_point GEOGRAPHY(POINT, 4326) NOT NULL,
area_km2 DECIMAL(10,2),
-- Clasificación
type VARCHAR(50) NOT NULL
CHECK (type IN ('PORT', 'INDUSTRIAL', 'URBAN', 'LOGISTICS_HUB', 'RURAL')),
importance VARCHAR(20) DEFAULT 'NORMAL',
-- Características operativas
characteristics JSONB DEFAULT '{}',
/* Ejemplo:
{
"avg_loading_time_min": 45,
"parking_difficulty": "high",
"restricted_hours": ["22:00-06:00"],
"toll_cost_avg": 12.50
}
*/
-- Conectividad
connected_zones VARCHAR[] DEFAULT '{}',
distance_matrix JSONB DEFAULT '{}',
/* Ejemplo:
{
"MAD": {"distance_km": 350, "time_min": 210},
"VAL": {"distance_km": 100, "time_min": 75}
}
*/
-- Metadata
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
-- Índices
INDEX idx_zones_type (type),
INDEX idx_zones_importance (importance)
);
-- Índice espacial para búsquedas geográficas
CREATE INDEX idx_zones_polygon ON siga.zones USING GIST (polygon);
CREATE INDEX idx_zones_center ON siga.zones USING GIST (center_point);
-- Relación Vehículos-Conductores (muchos a muchos temporal)
CREATE TABLE siga.vehicle_driver_assignments (
id SERIAL PRIMARY KEY,
vehicle_id INTEGER NOT NULL REFERENCES siga.vehicles(id),
driver_id INTEGER NOT NULL REFERENCES siga.drivers(id),
assigned_from TIMESTAMP NOT NULL DEFAULT NOW(),
assigned_to TIMESTAMP,
-- Evitar asignaciones solapadas
EXCLUDE USING GIST (
vehicle_id WITH =,
tstzrange(assigned_from, assigned_to) WITH &&
),
-- Un conductor no puede estar en dos vehículos al mismo tiempo
EXCLUDE USING GIST (
driver_id WITH =,
tstzrange(assigned_from, assigned_to) WITH &&
)
);
-- Histórico de estados de órdenes
CREATE TABLE siga.order_status_history (
id SERIAL PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES siga.orders(id),
status VARCHAR(50) NOT NULL,
changed_at TIMESTAMP NOT NULL DEFAULT NOW(),
changed_by VARCHAR(100),
notes TEXT,
location GEOGRAPHY(POINT, 4326),
INDEX idx_order_status_history_order (order_id),
INDEX idx_order_status_history_time (changed_at)
);
-- La capacidad del vehículo debe ser respetada
ALTER TABLE siga.orders
ADD CONSTRAINT check_vehicle_capacity
CHECK (
assigned_vehicle_id IS NULL OR
cargo_weight_kg <= (
SELECT capacity_kg
FROM siga.vehicles
WHERE id = assigned_vehicle_id
)
);
-- Las ventanas temporales deben ser válidas
ALTER TABLE siga.orders
ADD CONSTRAINT check_time_windows
CHECK (
lower(pickup_window) < upper(pickup_window) AND
lower(delivery_window) < upper(delivery_window) AND
upper(pickup_window) <= lower(delivery_window)
);
-- Un vehículo no puede estar en dos lugares al mismo tiempo
CREATE OR REPLACE FUNCTION check_vehicle_availability()
RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (
SELECT 1 FROM siga.orders
WHERE assigned_vehicle_id = NEW.assigned_vehicle_id
AND id != NEW.id
AND status IN ('IN_TRANSIT', 'LOADING', 'UNLOADING')
AND (pickup_window && NEW.pickup_window OR delivery_window && NEW.delivery_window)
) THEN
RAISE EXCEPTION 'Vehicle is not available for this time window';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER enforce_vehicle_availability
BEFORE INSERT OR UPDATE ON siga.orders
FOR EACH ROW
EXECUTE FUNCTION check_vehicle_availability();
CREATE OR REPLACE FUNCTION siga.calculate_distance(
point1 GEOGRAPHY(POINT),
point2 GEOGRAPHY(POINT)
) RETURNS DECIMAL AS $$
BEGIN
RETURN ST_Distance(point1, point2) / 1000.0; -- Retorna km
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- Uso
SELECT siga.calculate_distance(
origin_point,
destination_point
) as distance_km
FROM siga.orders;
CREATE OR REPLACE FUNCTION siga.find_nearby_vehicles(
location GEOGRAPHY(POINT),
radius_km INTEGER DEFAULT 100,
limit_results INTEGER DEFAULT 10
) RETURNS TABLE (
vehicle_id INTEGER,
plate VARCHAR,
distance_km DECIMAL,
status VARCHAR,
next_available TIMESTAMP
) AS $$
BEGIN
RETURN QUERY
SELECT
v.id,
v.plate,
ROUND(ST_Distance(v.current_position, location) / 1000.0, 2) as distance_km,
v.status,
v.next_available
FROM siga.vehicles v
WHERE v.active = true
AND ST_DWithin(v.current_position, location, radius_km * 1000)
ORDER BY ST_Distance(v.current_position, location)
LIMIT limit_results;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION siga.get_zone_for_point(
point GEOGRAPHY(POINT)
) RETURNS VARCHAR AS $$
DECLARE
zone_code VARCHAR;
BEGIN
SELECT z.code INTO zone_code
FROM siga.zones z
WHERE ST_Contains(z.polygon::geometry, point::geometry)
LIMIT 1;
RETURN zone_code;
END;
$$ LANGUAGE plpgsql;
-- Trigger para auto-asignar zonas
CREATE OR REPLACE FUNCTION assign_zones()
RETURNS TRIGGER AS $$
BEGIN
NEW.origin_zone = siga.get_zone_for_point(NEW.origin_point);
NEW.destination_zone = siga.get_zone_for_point(NEW.destination_point);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER auto_assign_zones
BEFORE INSERT OR UPDATE ON siga.orders
FOR EACH ROW
EXECUTE FUNCTION assign_zones();
⬅️ Volver a Modelo de Datos | ➡️ Siguiente: Tablas de Decisiones