---
title: Model Based Schema
slug: old-ajoco-feb26-lab-old/model-based-schema
docTags: 
createdAt: 2025-10-15T21:01:49.087Z
---

![](https://experienceleague.adobe.com/en/docs/journey-optimizer/using/campaigns/orchestrated-campaigns/data-configuration/schemas-datasets/media_1ac0bceb690e93404e27f371fe3ea1060cc2e662f.png?width=2000\&format=webply\&optimize=medium)

A **model-based schema** is the formal definition of the model-based data model.

It specifies:

- The set of tables
- The columns in each table
- The constraints
- The relationships across tables

Organizing schemas or tables in a model-based data model is about structuring your data into multiple tables. Ensure each table stores one type of entity/schemas.

There are 2 ways to create a **model-based schema. **We will only cover the fastest way which is using a DDL file. If you are interested on how to create manually, then visit this [page](https://experienceleague.adobe.com/en/docs/journey-optimizer/using/campaigns/orchestrated-campaigns/data-configuration/schemas-datasets/manual-schema). 

## Create Model Based Schema using DDL

### Upload Your DDL:

::::ExpandableHeading{isOpen="true"}
 ***DDL File For Lab***

Either download the file, or copy the code and paste in a Text Editor (Notepad ++ or textEdit or notes) save with the extension .ddl upload to AEP.

::File{src="https://api.archbee.com/api/presign/1Jp0iolWv35Z_mkI7oUiT/TKG1AoAYEw9y3v0tdOL3v-20251015-214450.ddl" label="uc1_bookings_u1.ddl"}

:::CodeblockTabs
DDL

```mysql
-- ============================================================================
-- 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
```
:::
::::

- Under **Data Management**, click **Schemas**.
- Click **Create Schema**
- Select the option **Model Based Schema**
- Select the option **Upload DDL File**
- Drag and Drop your DDL File, Click **Next**

![](https://api.archbee.com/api/optimize/1Jp0iolWv35Z_mkI7oUiT/jZBx1iZxK-Nad4YBXCbi8-20251015-220620.png)

:::Heading{depth="3" indent="2"}
Entities and Fields to Import:
:::

![](https://api.archbee.com/api/optimize/1Jp0iolWv35Z_mkI7oUiT/faM3FDHqqK92ndkGbxf_9-20251015-224856.png)

:::hint{type="danger"}
Notice that our version column of the SQL Type Number (38,0) got flagged by AEP as a Double. We know is a number and its our version column, let's fix that!
:::

- On the column called Type for Row "version" select Integer option. 
- Then on the column Version hit the toggle Versioning for the same row. 
- The row emailAddress, set its Identity as Email. 
- Lastly, Look at your top left corner for the field "Schema Name". Change it so it matches your Lab User Number (i,e uc1\_bookings\_u**99**)
- Click Done. Click Save. Open Jobs. 

### Relationships

:::hint{type="info"}
We ingested only one entity without relatable entities. We can add relationships by manually editing the schemas. Please follow your instructor on this part. 
:::

Congratulations, you finished this lab!