Describing relationships in IDSs¶

An array of objects in an IDS can be mapped to a table, where a single object in the array corresponds to a row of the table, and the fields in the objects are the columns. This mapping happens on the Tetra Data Platform to enable querying IDS data with SQL.

If an IDS contains multiple arrays of objects (corresponding to multiple tables) then it can be helpful to explicitly define relationships between the objects in these arrays. For example, for an IDS containing methods and results it can be useful to understand which element of results relates to which element of methods, or equivalently, the relationship between rows of the results and methods tables.

These relationships can be created in an IDS by using UUID primary keys and foreign keys, which is explained in this section.

UUID primary keys are a form of surrogate key: a key which is created during task script processing and is not taken from source data. This is in contrast with a natural key, which uses values from the source data as the keys for defining relationships. Those natural keys can still exist in the IDS data, but adding surrogate keys creates a single consistent approach for representing relationships across all IDS data, rather than using a different set of natural keys for each relationship. Natural keys are also prone to data integrity problems from source data, such as duplication or missing values, which is avoided by creating surrogate keys.

Define primary and foreign keys fields in an IDS¶

To create an IDS which has primary and foreign keys, see the UUIDForeignKey docs and UUIDPrimaryKey on the same page.

Generating UUIDs in a task script¶

The values assigned to primary and foreign key fields are UUID strings (such as "123e4567-e89b-12d3-a456-426614174000") which must be generated during task script parsing.

These UUIDs need to be deterministically generated by seeding a random generator with the content of the input file and the identity of the task script (namespace, slug and version). This achieves the following:

UUIDs are reproducible

Test code in the task script repo can rely on UUID values in tests, for example in snapshot testing, the snapshot files will not change between test executions.
Reprocessing a file on TDP using the same protocol version leads to the same UUIDs being generated, meaning the content of the IDS JSON output is stable between workflow executions.

UUIDs are unique across different files and across different task scripts

This means that there are no primary key clashes between different files or data generated by different task scripts, meaning these relationships can be used across multiple IDS documents, for example when aggregating multiple IDS JSON documents, or when querying data with SQL.

To generate UUID strings following these guidelines, use the UUID generator in ts-task-script-utils, documented here with examples of how to seed the random generator with the task script identity and file content, and then generate UUID strings.

Naming guidelines¶

A consistent approach to naming makes IDSs containing primary and foreign keys easier to understand when working with multiple IDSs:

Name primary key fields as pk. There only needs to be one primary key in any given array of objects, so there is no need for a more specific name.
Name foreign keys as fk_<object_path> where <object_path> is the path of the object in the JSON document, including the object name itself. For example: a reference to the object methods[*].cycles[*] would be named fk_methods_cycle (notice it’s cycle not cycles). The purpose of this is to make the target of the foreign key quickly and easily reconizeable to data users without having to refer back to documentation. Shorter names may be used if they are unambiguous and still make the target clear, such as fk_cycle, but this name would be unclear if both methods[*].cycles[*] and runs[*].cycles[*] existed in the IDS.
- Prefacing the key with fk makes it quick to recognize that this is a foreign key field.
- Including the path to the object makes it quick to recognize the target object.

Relational structure guidelines¶

There can be many foreign keys pointing to a single primary key. For example after defining a primary key in object_a.pk, a foreign key which links to it (fk_object_a) can appear in multiple places elsewhere in the schema.

To make queries simpler to write and understand (for example SQL queries), it can be beneficial to avoid primary/foreign key structures which lead to deeply nested JOINs in queries.

In the example below, users, methods and samples contain primary keys, and they are all tied together in one place with foreign keys in results, meaning that any of those three tables can be related to results with a single JOIN which doesn’t depend on other intermediate tables. This has similar benefits to the star schema design pattern.

Example structure and query¶

The following is a way to create linked users, methods, samples and results.

users
    pk: uuid1

methods
    pk: uuid2

samples
    pk: uuid3

results
    fk_user: uuid1
    fk_system: uuid2
    fk_sample: uuid3

If these fields are part of an IDS common/example:v1.0.0, then an Athena SQL query to join all of these tables into one could look like:

SELECT
  *
FROM
  "example_v1_results" results
  JOIN "example_v1_users" users ON results.fk_user = users.pk
  JOIN "example_v1_methods" systems ON results.fk_method = methods.pk
  JOIN "example_v1_samples" samples ON results.fk_sample = samples.pk
LIMIT
  100

Example approaches for modeling different cardinalities¶

The above example takes a star schema approach which is a concept within dimensional modeling. Here we will build upon this schema to demonstrate example approaches for modeling common cardinalities, one-to-many and many-to-many.

Using the example above, we can define users, methods, and samples as dimension tables and results as a fact table. Additionally, lets consider the grain of results to be one record per measurement of the source instrument.

One-to-many¶

This is perhaps the most commonly encountered cardinality. The configuration and execution of scientific instruments often follows a similar pattern, one method configuring the instrument to execute in a specified way, one execution of the instrument, and many measurements are made. In the case of our example model we will assume our source system follows the same pattern, one method produces many measurements (i.e. there is a one-to-many relationship between methods and results). Defining the relationship between methods and results is simple:

from typing import ClassVar, List, Literal

from ts_ids_core.annotations import Required, UUIDForeignKey, UUIDPrimaryKey
from ts_ids_core.base.ids_element import IdsElement, SchemaExtraMetadataType
from ts_ids_core.base.ids_field import IdsField
from ts_ids_core.schema import IdsSchema

class Result(IdsElement):
    fk_method: UUIDForeignKey = IdsField(
        primary_key="/properties/methods/items/properties/pk"
    )
    value: float

class Method(IdsElement):
    pk: UUIDPrimaryKey
    name: str

class StarSchema(IdsSchema):
    schema_extra_metadata: ClassVar[SchemaExtraMetadataType] = {
        "$id": "https://ids.tetrascience.com/common/example/v1.0.0/schema.json",
        "$schema": "http://json-schema.org/draft-07/schema#",
    }

    ids_namespace: Required[Literal["my_namespace"]] = IdsField(
        default="common", alias="@idsNamespace"
    )
    ids_type: Required[Literal["my_unique_ids_name"]] = IdsField(
        default="example", alias="@idsType"
    )
    ids_version: Required[Literal["v1.0.0"]] = IdsField(
        default="v1.0.0", alias="@idsVersion"
    )

    methods: List[Method]
    results: List[Result]

Show JSON schema

{
  "$id": "https://ids.tetrascience.com/common/example/v1.0.0/schema.json",
  "$schema": "http://json-schema.org/draft-07/schema#",
  "additionalProperties": false,
  "properties": {
    "@idsType": {
      "const": "my_unique_ids_name",
      "type": "string"
    },
    "@idsVersion": {
      "const": "v1.0.0",
      "type": "string"
    },
    "@idsNamespace": {
      "const": "my_namespace",
      "type": "string"
    },
    "methods": {
      "items": {
        "$ref": "#/definitions/Method"
      },
      "type": "array"
    },
    "results": {
      "items": {
        "$ref": "#/definitions/Result"
      },
      "type": "array"
    }
  },
  "required": [
    "@idsType",
    "@idsVersion",
    "@idsNamespace"
  ],
  "type": "object",
  "definitions": {
    "Method": {
      "additionalProperties": false,
      "properties": {
        "pk": {
          "@primary_key": true,
          "type": "string"
        },
        "name": {
          "type": "string"
        }
      },
      "required": [
        "pk"
      ],
      "type": "object"
    },
    "Result": {
      "additionalProperties": false,
      "properties": {
        "fk_method": {
          "@foreign_key": "/properties/methods/items/properties/pk",
          "type": "string"
        },
        "value": {
          "type": "number"
        }
      },
      "required": [
        "fk_method"
      ],
      "type": "object"
    }
  }
}

All we are doing is defining a primary key in the methods table and a foreign key referencing said primary key in the results table. The primary thing to ensure is that the relationship is truly one-to-many, so when results is joined to methods, no result rows are duplicated. If the join results in duplicated result rows, then any aggregations performed on the joined dataset will likely be invalid. If our assumption that one method produces many measurements is correct, then we can guarantee that any given measurement will only have one corresponding row in the methods table, thus no rows from the results table will be duplicated upon joining.

Many-to-many¶

Many-to-many relationships introduce a problem, if multiple records in a dimension correspond to many records in a fact table, then how can you assign multiple foreign keys to an individual record in the fact table that join to all related records in the dimension table? The answer is to use a bridge table. A bridge table is a referential intermediate table which contains only relationships to between entities. In the case of our example model, consider the possibility that there can be an indeterminate amount of users that produced a given set of measurements. In this case, there is a many-to-many relationship between users and results. Building upon our model above, we can create a bridge between results and users in the following way:

from typing import ClassVar, List, Literal

from ts_ids_core.annotations import Required, UUIDForeignKey, UUIDPrimaryKey
from ts_ids_core.base.ids_element import IdsElement, SchemaExtraMetadataType
from ts_ids_core.base.ids_field import IdsField
from ts_ids_core.schema import IdsSchema

class Result(IdsElement):
    fk_method: UUIDForeignKey = IdsField(
        primary_key="/properties/methods/items/properties/pk"
    )
    fk_user_group: UUIDForeignKey = IdsField(
        primary_key="/properties/user_groups/items/properties/pk"
    )
    value: float

class Method(IdsElement):
    pk: UUIDPrimaryKey
    name: str

class UserGroup(IdsElement):
    pk: UUIDPrimaryKey
    name: str

class UserBridge(IdsElement):
    fk_user_group: UUIDForeignKey = IdsField(
        primary_key="/properties/user_groups/items/properties/pk"
    )
    fk_user: UUIDForeignKey = IdsField(
        primary_key="/properties/users/items/properties/pk"
    )

class User(IdsElement):
    pk: UUIDPrimaryKey
    name: str

class StarSchema(IdsSchema):
    schema_extra_metadata: ClassVar[SchemaExtraMetadataType] = {
        "$id": "https://ids.tetrascience.com/common/example/v1.0.0/schema.json",
        "$schema": "http://json-schema.org/draft-07/schema#",
    }

    ids_namespace: Required[Literal["my_namespace"]] = IdsField(
        default="common", alias="@idsNamespace"
    )
    ids_type: Required[Literal["my_unique_ids_name"]] = IdsField(
        default="example", alias="@idsType"
    )
    ids_version: Required[Literal["v1.0.0"]] = IdsField(
        default="v1.0.0", alias="@idsVersion"
    )

    methods: List[Method]
    results: List[Result]
    user_groups: List[UserGroup]
    users_bridge: List[UserBridge]
    users: List[User]

Show JSON schema

{
  "$id": "https://ids.tetrascience.com/common/example/v1.0.0/schema.json",
  "$schema": "http://json-schema.org/draft-07/schema#",
  "additionalProperties": false,
  "properties": {
    "@idsType": {
      "const": "my_unique_ids_name",
      "type": "string"
    },
    "@idsVersion": {
      "const": "v1.0.0",
      "type": "string"
    },
    "@idsNamespace": {
      "const": "my_namespace",
      "type": "string"
    },
    "methods": {
      "items": {
        "$ref": "#/definitions/Method"
      },
      "type": "array"
    },
    "results": {
      "items": {
        "$ref": "#/definitions/Result"
      },
      "type": "array"
    },
    "user_groups": {
      "items": {
        "$ref": "#/definitions/UserGroup"
      },
      "type": "array"
    },
    "users_bridge": {
      "items": {
        "$ref": "#/definitions/UserBridge"
      },
      "type": "array"
    },
    "users": {
      "items": {
        "$ref": "#/definitions/User"
      },
      "type": "array"
    }
  },
  "required": [
    "@idsType",
    "@idsVersion",
    "@idsNamespace"
  ],
  "type": "object",
  "definitions": {
    "Method": {
      "additionalProperties": false,
      "properties": {
        "pk": {
          "@primary_key": true,
          "type": "string"
        },
        "name": {
          "type": "string"
        }
      },
      "required": [
        "pk"
      ],
      "type": "object"
    },
    "Result": {
      "additionalProperties": false,
      "properties": {
        "fk_method": {
          "@foreign_key": "/properties/methods/items/properties/pk",
          "type": "string"
        },
        "fk_user_group": {
          "@foreign_key": "/properties/user_groups/items/properties/pk",
          "type": "string"
        },
        "value": {
          "type": "number"
        }
      },
      "required": [
        "fk_method",
        "fk_user_group"
      ],
      "type": "object"
    },
    "User": {
      "additionalProperties": false,
      "properties": {
        "pk": {
          "@primary_key": true,
          "type": "string"
        },
        "name": {
          "type": "string"
        }
      },
      "required": [
        "pk"
      ],
      "type": "object"
    },
    "UserBridge": {
      "additionalProperties": false,
      "properties": {
        "fk_user_group": {
          "@foreign_key": "/properties/user_groups/items/properties/pk",
          "type": "string"
        },
        "fk_user": {
          "@foreign_key": "/properties/users/items/properties/pk",
          "type": "string"
        }
      },
      "required": [
        "fk_user_group",
        "fk_user"
      ],
      "type": "object"
    },
    "UserGroup": {
      "additionalProperties": false,
      "properties": {
        "pk": {
          "@primary_key": true,
          "type": "string"
        },
        "name": {
          "type": "string"
        }
      },
      "required": [
        "pk"
      ],
      "type": "object"
    }
  }
}

Notice the creation of two new tables: user_groups and users_bridge. user_groups exists as a way to bucket all relevant users to a single record; we can assign a single foreign key to a given result record which points to the primary key of user_groups. users_bridge then contains a foreign key pointing to the user_groups primary key and a foreign key pointing to the users primary key. Joining results to users can then be done in the following way:

SELECT results.value,
       users.name
FROM example_v1_results results
JOIN example_v1_user_groups groups
  ON results.fk_user_group = groups.pk
JOIN example_v1_users_brige bridge
  ON bridge.fk_user_group = groups.pk
JOIN example_v1_users users
  ON bridge.fk_user = users.pk

This query will then return the results values tied to the users associated with said value. However, note that we run into the same problem mentioned above, a given result row will be duplicated by the number of users associated with said result. To avoid the duplication, you can alter the query to aggregate the users before joining to results.

WITH aggregate_users AS (
  SELECT groups.pk,
         array_agg(users.name) AS user_group
  FROM example_v1_user_groups groups
  JOIN example_v1_users_brige bridge
    ON bridge.fk_user_group = groups.pk
  JOIN example_v1_users users
    ON bridge.fk_user = users.pk
  GROUP BY groups.pk
) SELECT results.value,
         agg.user_group
  FROM example_v1_results results
  JOIN aggregate_users agg
    ON results.fk_user_group = agg.pk