Physical Model

The physical_model block defines the physical database schema, including tables, fields, data types, primary keys, foreign keys, and indexes.

Overview

The physical model is auto-generated from your logical model's entity-source mappings. It creates table definitions with:

Primary key fields for each entity
Foreign key fields based on logical relationships
Entity descriptions from the conceptual model
Source mappings from the logical model

You can then enhance these auto-generated tables by adding additional fields, indexes, and constraints in the Physical Model view.

File Location

Physical model MML is stored in:

modality/models/physical/schema.mml

Basic Structure

mml

# Physical Data Model
# Tables, fields, and database schema
# Auto-populated from logical model entity-source mappings

physical_model {

  table "customer" {
    entity_group = "platform"
    entity = "entity_customer"
    source = "source_crm"
    description = "Customer master data"

    field "entity_customer_id" {
      type = "uuid"
      nullable = false
      primary_key = true
      description = "Primary key for customer"
    }

    field "subscription_id" {
      type = "uuid"
      nullable = true
      description = "Foreign key to subscription"
      foreign_key {
        table = "subscription"
        field = "entity_subscription_id"
      }
    }
  }
}

Auto-Population

The physical model is automatically generated from your logical model when you export to Git. For each entity-source mapping in the logical model, Modality creates:

1. Table Definition

mml

table "customer" {
  entity_group = "platform"      # From logical mapping
  entity = "entity_customer"     # From logical mapping
  source = "source_crm"          # From logical mapping
  description = "..."            # From conceptual model
}

2. Primary Key Field

Every table gets an auto-generated primary key:

mml

field "entity_customer_id" {
  type = "uuid"
  nullable = false
  primary_key = true
  description = "Primary key for customer"
}

3. Foreign Key Fields

For each many-to-one or one-to-one relationship in the logical model, a foreign key field is created:

mml

field "parent_entity_id" {
  type = "uuid"
  nullable = true
  description = "Foreign key to parent_entity"
  foreign_key {
    table = "parent_entity_table"
    field = "entity_parent_entity_id"
  }
}

Note: For self-referential relationships (where an entity relates to itself), the foreign key is automatically prefixed with parent_ to avoid naming collisions with the primary key:

mml

field "parent_category_id" {
  type = "uuid"
  nullable = true
  description = "Foreign key to parent category"
  foreign_key {
    table = "category_table"
    field = "entity_category_id"
  }
}

Table Attributes

Attribute	Required	Description
`entity_group`	Yes	The conceptual entity group this table belongs to
`entity`	Yes	The conceptual entity this table represents
`source`	Yes	The logical source system for this table (auto-generated)
`description`	No	Human-readable description

Field Attributes

Attribute	Required	Description
`type`	Yes	Data type (e.g., "VARCHAR(255)", "INTEGER", "TIMESTAMP", "BIGINT")
`nullable`	Yes	Whether the field can be NULL
`primary_key`	Yes	Mark as primary key (default: false)
`description`	No	Human-readable description
`foreign_key`	No	Foreign key relationship (see Foreign Key Block below)

Foreign Key Block

Foreign key relationships are defined within field blocks:

mml

field "customer_id" {
  type = "uuid"
  nullable = false
  primary_key = false
  description = "Foreign key to customer"

  foreign_key {
    table = "customer"                    # Referenced table
    field = "entity_customer_id"          # Referenced field
  }
}

Data Types

Common data types used in physical models:

Type	Description	Example
`uuid`	Universally unique identifier	Used for primary/foreign keys
`BIGINT`	Large integer	Used for numeric primary keys
`VARCHAR(n)`	Variable-length string	`VARCHAR(255)` for text fields
`INTEGER`	Whole number	For counts and numeric values
`DECIMAL`	Decimal number	For monetary values
`BOOLEAN`	True/false	For flags
`DATE`	Date only	For date fields
`TIMESTAMP`	Date and time	For timestamps

Complete Example

mml

physical_model {

  table "customer" {
    entity_group = "platform"
    entity = "entity_customer"
    source = "source_crm"
    description = "Customer master data"

    field "entity_customer_id" {
      type = "uuid"
      nullable = false
      primary_key = true
      description = "Primary key for customer"
    }

    # Add additional fields in the Physical Model view
  }

  table "order" {
    entity_group = "sales"
    entity = "entity_order"
    source = "source_ecom"
    description = "Customer orders"

    field "entity_order_id" {
      type = "uuid"
      nullable = false
      primary_key = true
      description = "Primary key for order"
    }

    field "customer_id" {
      type = "uuid"
      nullable = true
      description = "Foreign key to customer"
      foreign_key {
        table = "customer"
        field = "entity_customer_id"
      }
    }

    # Add additional fields in the Physical Model view
  }
}

Best Practices

1. Let Auto-Generation Do the Heavy Lifting

The physical model is auto-generated from your logical model, so:

Define entity-source mappings in the logical model first
Define relationships in the logical model to get automatic foreign keys
Tables are created automatically with primary keys and foreign keys

2. Add Additional Fields in the UI

The auto-generated tables include only primary keys and foreign keys. Use the Physical Model view in the app to:

Add additional data fields specific to your implementation
Define field descriptions
Set nullable/non-nullable constraints
Create foreign key relationships

3. Use Appropriate Data Types

Choose the right data type for your database:

uuid for primary/foreign keys (default)
BIGINT for numeric primary keys
VARCHAR(n) for variable-length text
INTEGER for whole numbers
DECIMAL for monetary values
TIMESTAMP for date/time fields

4. Document Table Purpose

Add descriptions to tables and fields:

mml

table "customer" {
  entity_group = "platform"
  entity = "entity_customer"
  source = "source_crm"
  description = "Customer master data with contact information"

  field "entity_customer_id" {
    type = "uuid"
    nullable = false
    primary_key = true
    description = "Primary key for customer records"
  }
}

Relationship to Other Models

The physical model sits between the logical and data product layers:

Conceptual Model (entities & relationships)
         ↓
Logical Model (sources & mappings)
         ↓
Physical Model (tables & fields)    ← Auto-generated
         ↓
Data Products (consumption layer)

Conceptual → Physical: Entity descriptions flow to table descriptions
Logical → Physical: Entity-source mappings become tables, relationships become foreign keys
Physical → Products: Tables are consumed by data products for reports and metrics

Physical Model ​

Overview ​

File Location ​

Basic Structure ​

Auto-Population ​

1. Table Definition ​

2. Primary Key Field ​

3. Foreign Key Fields ​

Table Attributes ​

Field Attributes ​

Foreign Key Block ​

Data Types ​

Complete Example ​

Best Practices ​

1. Let Auto-Generation Do the Heavy Lifting ​

2. Add Additional Fields in the UI ​

3. Use Appropriate Data Types ​

4. Document Table Purpose ​

Relationship to Other Models ​

Next Steps ​