Create Entity Relationships

Relationships between Types are an essential element of any application's object model. Relationships define how different Types in your object model interact in the application.

In the previous tutorial, you created a simple Country Type that represents a single country in a global data tracking application.

In this tutorial, you expand your object model to contain the following Types and relationships:

A new Continent Type. A Continent represents a geographic region. A Continent may have multiple Country.
A new City Type. A City represents a specific municipality within a Country. A Country may have multiple City.
A CountryCapitalHistory Type. This Type facilitates the time-dependent relation between a Country and its capital City, tracking capital changes over time.

The result of these new Types and relationships is shown in the following Entity Relation Diagram (ERD):

erDiagram Continent ||--o{ Country : contains Country ||--o{ City : contains Country ||--|{ CountryCapitalHistory : capitalHistory City ||--|{ CountryCapitalHistory : wasCapitalOf Continent { string id string name collection countries } Country { string id string name string alpha3Code reference continent collection cities collection capitalHistory reference currentCapital } City { string id string name reference country collection capitalHistory reference currentCountry } CountryCapitalHistory { reference from reference to datetime start datetime end }

Continue this tutorial to create static and time-dependent relationships between Types in your object model.

Collection and reference fields

Use collection and reference fields to define relationships between Types in your object model.

Reference fields: Are used when there is only a single instance of another Type associated with the origin Type. To learn more about reference fields, see Fields.
- For example, the Country Type has a reference field to the Continent Type since each instance of a Country can only be associated with a single Continent.
- The following code block displays the syntax for reference fields:
Type
```
entity type MyType {
// A reference field on this Type
fieldName: !ReferenceType
}
```
Collection fields: Are used when there is one or more instances of another Type associated with the origin Type. To learn more about collection fields, see Collections.
- For example, the Continent Type has a collection field to the continent field of the Country Type since each instance of a Continent is associated with multiple instances of Country.
- The following code block displays the syntax for collection fields:

Type

entity type MyType {
// A collection field on this Type
fieldName: [ReferenceType](filterFieldName)
}

Simple relations

Create new Continent and City Types to represent geographic entities in a global data tracking application.

Since the relationship between a Country and Continent is many to one, the Continent Type contains a collection field of all Country in the Continent, and the Country Type contains a reference field for its parent Continent.

This relation is shown in the following ERD:

erDiagram Continent ||--o{ Country : contains Continent { string id string name collection countries } Country { string id string name string alpha3Code reference continent }

The following code block shows the Type declaration for the Continent Type with a collection field:

Type

/**
 * Continent.c3typ
 * A geographic region containing multiple countries.
 */
entity type Continent {
  // The name of this continent
  name: !string
  
  // The collection of countries in this continent
  countries: [Country](continent)
}

Pay special care to the casing style used in Type and Field names. Type names use the Pascal Case, while field names use Camel Case.

When you query the countries field for a given Continent, the application returns an array of all instances of the Country Type with the current Continent in its continent field.

The following code block shows the Type declaration for the Country Type with a reference field:

Type

/**
 * Country.c3typ
 * A single country in the global data model.
 */
entity type Country {
  // The name of this country
  name: !string

  // The three-letter country code defined in the ISO 3166-1 standard for this country
  alpha3Code: !string
  
  // The continent to which this country belongs
  continent: !Continent
}

When you query the continent field for a given Country, the application returns the parent Continent for the given Country.

Create a new City Type and add the necessary collection and reference fields to create a relationship between City and Country. A Country may be associated with multiple instances of City but a City may only be associated with a single Country.

The following code block shows the Type declaration for the City Type:

Type

/**
 * City.c3typ
 * A single city within a country.
 */
entity type City {
  // The name of this city
  name: !string
  
  // The country in which this city is located
  country: !Country
}

Update the Country Type to include a collection of cities:

Type

/**
 * Country.c3typ
 * A single country in the global data model.
 */
entity type Country {
  // The name of this country
  name: !string

  // The three-letter country code defined in the ISO 3166-1 standard for this country
  alpha3Code: !string
  
  // The continent to which this country belongs
  continent: !Continent
  
  // The collection of cities in this country
  cities: [City](country)
}

Time-dependent relations

In the previous section, you used collection and reference fields to create a many to one relationship between a Country and its Continent. The relationship between a Country and its parent Continent is unlikely to change over time.

Some relationships in an object model may be time-dependent.

For example, a country may change its capital city over time. Historical examples include:

Kazakhstan moved its capital from Almaty to Nur-Sultan (now Astana) in 1997
Myanmar moved its capital from Yangon to Naypyidaw in 2006
Brazil moved its capital from Rio de Janeiro to Brasília in 1960

A City can only be the capital of one Country at a time. However, a Country can change its capital, and a City might serve as capital for different countries throughout history. Therefore, the relationship between the Country Type and the capital City Type is a time-dependent relation.

Create a new CountryCapitalHistory Type to store the relationship information between a Country and its capital City over time.

Mix in TimedIntervalRelation

Mix in the TimedIntervalRelation Type to add the fields needed to define a time-dependent relationship.

The following code block displays the implementation of the CountryCapitalHistory:

Type

/**
 * CountryCapitalHistory.c3typ
 * Tracks which city served as capital for which country during a specific time period.
 */
@db(index=['to','from'])
entity type CountryCapitalHistory mixes TimedIntervalRelation<Country, City> schema name "COUNTRYCAPITALHISTORY" {
}

The declaration of CountryCapitalHistory consists of the following components:

@db: Denotes the database annotation. To learn more about Type annotations, see Annotations topic.
index=['to','from']: Creates database indexes on the to and from fields for faster queries.
TimedIntervalRelation<Country, City>: Defines the from and to Types participating in the relationship. In this case, Country is the from Type and City is the to Type.

The CountryCapitalHistory Type now stores the relationship history between the Country and its capital City Types, including:

from: Reference to the Country
to: Reference to the capital City
start: When the city became the capital
end: When the city ceased to be the capital (null if still current)

Reference the relationship Type

Create connections to the new CountryCapitalHistory Type you created in the previous section:

Create a capitalHistory collection field in the Country Type.
Create a capitalHistory collection field in the City Type.

These connections allow the Country and City Types to access the relationship history between the two Types.

Take care to ensure the correct fields in CountryCapitalHistory are referenced in the collection fields. Recall Country is assigned to the from field and City is assigned to the to field in CountryCapitalHistory.

The following code block displays the Country Type with a new capitalHistory field:

Type

/**
 * Country.c3typ
 * A single country in the global data model.
 */
entity type Country {
  // The name of this country
  name: !string

  // The three-letter country code defined in the ISO 3166-1 standard for this country
  alpha3Code: !string
  
  // The continent to which this country belongs
  continent: !Continent
  
  // The collection of cities in this country
  cities: [City](country)
  
  // The collection of CountryCapitalHistory for this country
  @db(order='descending(start)')
  capitalHistory: [CountryCapitalHistory](from)
}

The @db(order='descending(start)') annotation ensures that the collection is sorted by the start date in descending order, placing the most recent capital designation first in the array.

Follow the same process to implement a capitalHistory field in the City Type:

Type

/**
 * City.c3typ
 * A single city within a country.
 */
entity type City {
  // The name of this city
  name: !string
  
  // The country in which this city is located
  country: !Country
  
  // The collection of CountryCapitalHistory for this city
  @db(order='descending(start)')
  capitalHistory: [CountryCapitalHistory](to)
}

Notice that the City Type references the to field in the collection filter, while the Country Type references the from field. This is because City is the to side of the relationship.

Use the history fields to access the relationship history between the Country and its capital City Types.

In the next section, learn how to create calculated fields to retrieve details about specific relationships in the relationship history field.

Stored calculations

In the previous section, you created the capitalHistory collection fields to allow the Country and City Types to access the relationship history stored in the CountryCapitalHistory Type.

In this section, create a currentCapital stored calc field to add a reference to the current capital for a given Country. A stored calc field is a calculated field that automatically updates when other data is changed.

This field performs the following calculation when a change in CountryCapitalHistory occurs:

Queries the most recent record in the capitalHistory field.
Checks if the relationship is current.
Retrieves the City id from the relationship record.
Stores the value in currentCapital.

The following code block displays the completed Country Type:

Type

/**
 * Country.c3typ
 * A single country in the global data model.
 */
entity type Country {
    /**
     * The name of this country.
     */
    name: !string

    /**
     * The three-letter country code defined in the ISO 3166-1 standard for this country.
     */
    alpha3Code: !string
    
    /**
     * The continent to which this country belongs.
     */
    continent: !Continent
    
    /**
     * The collection of cities in this country.
     */
    cities: [City](country)
    
    /**
     * The collection of CountryCapitalHistory for this country.
     */
    @db(order='descending(start)')
    capitalHistory: [CountryCapitalHistory](from)
    
    /**
     * The current capital city of this country.
     */
    currentCapital: City stored calc capitalHistory[0].(end == null).to
}

The currentCapital stored calc consists of the following components:

City: The Type of the reference field being calculated.
stored calc: The keywords to perform a calculation and persist the results in this field.
capitalHistory[0]: The first entry in the history collection field. Since the collection field is sorted in descending order by the start field, this is the most recent entry.
.(end == null): A logical check to verify that the relationship has no end date. If the end field is null, then the relationship is current.
.to: The field from the relation Type to store. This retrieves the City from the relationship.

Repeat the above process to create a currentCountry field in the City Type:

Type

/**
 * City.c3typ
 * A single city within a country.
 */
entity type City {
    /**
     * The name of this city.
     */
    name: !string
    
    /**
     * The country in which this city is located.
     */
    country: !Country
    
    /**
     * The collection of CountryCapitalHistory for this city.
     */
    @db(order='descending(start)')
    capitalHistory: [CountryCapitalHistory](to)
    
    /**
     * The country for which this city currently serves as capital (if any).
     */
    currentCountry: Country stored calc capitalHistory[0].(end == null).from
}

Notice that the City Type's stored calc uses .from instead of .to, because we want to retrieve the Country from the relationship, not the City.

This stored calculation allows you to quickly access which country (if any) currently has this city as its capital, without needing to query the entire relationship history.

Copy link to this sectionCollection and reference fields

Copy link to this sectionSimple relations

Copy link to this sectionTime-dependent relations

Copy link to this sectionMix in TimedIntervalRelation

Copy link to this sectionReference the relationship Type

Copy link to this sectionStored calculations

Collection and reference fields

Simple relations

Time-dependent relations

Mix in TimedIntervalRelation

Reference the relationship Type

Stored calculations