Product Entity Relationships

Complete reference for how product entities relate to each other in the database.

Entity Relationship Diagram

erDiagram
    Market {
        int id PK
        string code "ES, DE, FR"
        string name
        json supported_locales
        json country_codes
        json currency_codes
    }

    ProductTemplate {
        int id PK
        string title
        string sku
        string source_locale
        json itinerary
        int duration
        json highlights
        json categories
    }

    ProductByMarket {
        int id PK
        int product_template_id FK
        int market_id FK
        string locale
        string sku
        string status
        date search_start_date
        date search_end_date
        json excluded_dates
    }

    ProductComponent {
        int id PK
        int product_template_id FK
        string component_name
        string component_type
        int sequence_order
        string inventory_type
        int inventory_id
    }

    ProductByMarketTranslation {
        int id PK
        int product_by_market_id FK
        string locale
        string title
        string url_slug
        json itinerary
    }

    ProductByMarketFlightConfig {
        int id PK
        int product_by_market_id FK
        int airport_id FK
        string type
        boolean is_active
    }

    ProductByMarketFlightLeg {
        int id PK
        int flight_config_id FK
        int leg_index
        int day_offset
        string flight_type
        int origin_airport_id FK
        int destination_airport_id FK
    }

    Market ||--o{ ProductByMarket : "has"
    ProductTemplate ||--o{ ProductByMarket : "has"
    ProductTemplate ||--o{ ProductComponent : "has"
    ProductByMarket ||--o{ ProductByMarketTranslation : "has"
    ProductByMarket ||--o{ ProductByMarketFlightConfig : "has"
    ProductByMarketFlightConfig ||--o{ ProductByMarketFlightLeg : "has"

Relationship Summary

Parent	Child	Cardinality	Foreign Key
ProductTemplate	ProductByMarket	1:N	product_template_id
ProductTemplate	ProductComponent	1:N	product_template_id
Market	ProductByMarket	1:N	market_id
ProductByMarket	ProductByMarketTranslation	1:N	product_by_market_id
ProductByMarket	ProductByMarketFlightConfig	1:N	product_by_market_id
ProductByMarketFlightConfig	ProductByMarketFlightLeg	1:N	flight_config_id
Airport	ProductByMarketFlightConfig	1:N	airport_id
Airport	ProductByMarketFlightLeg	1:N	origin_airport_id, destination_airport_id

Entity Purposes

ProductTemplate

Master definition of a travel package. Defines what a trip IS.

• Stores base content in source locale
• Contains itinerary (cities, nights)
• Auto-calculates duration
• Has no airport/flight data

ProductByMarket

Market-specific configuration for selling a template.

• Links template to a specific market + locale
• Configures flight search date ranges
• Unique per template + market + locale combination

ProductByMarketTranslation

Localized content for display.

• One per ProductByMarket (primary locale)
• Contains translated titles, descriptions, itinerary
• Holds SEO metadata (url_slug, meta_*)

ProductByMarketFlightConfig

Departure airport configuration.

• One per departure airport per ProductByMarket
• Defines flight search type (multi_city, separate)
• Contains all flight legs for that departure

ProductByMarketFlightLeg

Individual flight segments.

• Ordered by leg_index
• Stores origin/destination airports
• Tracks day_offset from trip start
• Classifies as international, domestic, or arnk

ProductComponent

Components that make up a product template.

• Links to inventory items (transfers, hotels)
• Defines sequence and day placement
• Supports mandatory/optional components

Unique Constraints

Table	Columns	Purpose
products_by_market	product_template_id, market_id, locale	One product per template/market/locale
products_by_market	sku	Unique market SKU
product_by_market_flight_configs	product_by_market_id, airport_id	One config per departure airport
product_by_market_translations	product_by_market_id, locale	One translation per locale
product_by_market_translations	locale, url_slug	Unique URL slugs per locale

Cascade Deletes

All child entities cascade delete from their parents:

flowchart TD
    PT[ProductTemplate deleted] --> PBM[All ProductByMarket deleted]
    PBM --> PBMT[All ProductByMarketTranslation deleted]
    PBM --> PBMFC[All ProductByMarketFlightConfig deleted]
    PBMFC --> PBMFL[All ProductByMarketFlightLeg deleted]

Query Examples

Get product with all relations

ProductByMarket::with([
    'productTemplate',
    'market',
    'translation',
    'flightConfigs.legs',
    'flightConfigs.airport',
])->find($id);

Find active products for a market

ProductByMarket::query()
    ->where('market_id', $marketId)
    ->where('status', 'active')
    ->with(['productTemplate', 'translation'])
    ->get();

Get all departure airports for a product

$product->flightConfigs()
    ->active()
    ->with('airport')
    ->get()
    ->pluck('airport');

Source Files

Model	Location
ProductTemplate	backend/app/Models/ProductTemplate.php
ProductByMarket	backend/app/Models/ProductByMarket.php
ProductByMarketTranslation	backend/app/Models/ProductByMarketTranslation.php
ProductByMarketFlightConfig	backend/app/Models/ProductByMarketFlightConfig.php
ProductByMarketFlightLeg	backend/app/Models/ProductByMarketFlightLeg.php
ProductComponent	backend/app/Models/ProductComponent.php
Market	backend/app/Models/Market.php

Related

• Products Overview - System architecture
• Product Templates - Template details
• Products by Market - Market configuration