Model Based Schema

-- ============================================================================
-- Hands-On Exercise: Create Your Bookings Table (User 1)
-- ============================================================================
-- Purpose: Practice creating a bookings table with proper relationships
-- Platform: Snowflake
-- Key Learning: Understanding 1:many relationships in Campaign Orchestration
-- ============================================================================

-- INSTRUCTIONS:
-- 1. This table will store individual booking transactions
-- 2. The emailAddress field is AEP Namespace
-- 3. Each booking is a separate record (customers can have multiple bookings)
-- 4. Primary Key: bookingNumber (unique identifier for each booking)
-- 5. Foreign Key: emailAddress (links to Recipients and AEP Profile)

-- ============================================================================
-- Create the uc1_bookings_u1 table
-- ============================================================================

CREATE TABLE uc1_bookings_u1 (
    -- Primary identifier for this booking
    bookingNumber STRING NOT NULL,
    
    -- Version field (required for AJO dataset versioning)
    version NUMBER(38,0) NOT NULL,
    
    -- RELATIONSHIP FIELD: Must match emailAddress in uc1_recipients table
    -- This is how we link bookings to customers (1:many relationship)
    emailAddress STRING NOT NULL,
    
    -- Customer information (duplicated from recipients for convenience)
    firstName STRING,
    lastName STRING,
    
    -- Cruise product details
    productLine STRING,          -- e.g., "River", "Ocean", "Expedition"
    productName STRING,           -- e.g., "Grand European Tour"
    
    -- Important dates
    bookingDate DATE,             -- When the booking was made
    departureDate DATE,           -- When the cruise departs
    
    -- Trip details
    fromPort STRING,              -- Departure port
    shipCode STRING,              -- Specific ship (e.g., "VIK001")
    cabinCategory STRING,         -- Cabin type (e.g., "Veranda", "French Balcony")
    sailingSeason STRING,         -- Season (e.g., "Spring", "Summer")
    
    -- Extensions (pre/post cruise experiences)
    preExtension STRING,          -- Pre-cruise extension indicator
    postExtension STRING,         -- Post-cruise extension indicator
    extPreName STRING,            -- Name of pre-cruise extension
    extPostName STRING,           -- Name of post-cruise extension
    
    -- Insurance and air travel
    insuranceIndicator STRING,    -- Has travel insurance (Y/N)
    airType STRING,               -- Type of air package
    airTicketed STRING,           -- Air tickets issued (Y/N)
    airIndicator STRING,          -- Air travel included (Y/N)
    
    -- Additional services
    numShoreExcursions NUMBER(38,0),  -- Count of shore excursions booked
    numOfDays NUMBER(38,0),           -- Length of cruise in days
    
    -- Administrative codes
    packageCode STRING,           -- Package identifier
    officeCode STRING,            -- Booking office
    marketCode STRING,            -- Market segment
    
    -- Define primary key
    PRIMARY KEY (bookingNumber)
);

-- ============================================================================
-- KEY CONCEPTS FOR CAMPAIGN ORCHESTRATION
-- ============================================================================

-- 1. PRIMARY ENTITY: uc1_recipients (profiles)
--    - This is who you TARGET in campaigns
--    - One record per customer
--
-- 2. RELATED ENTITY: uc1_bookings_u1 (bookings)
--    - This is what you REFERENCE for personalization
--    - Multiple records per customer (1:many)
--    - Linked via emailAddress foreign key