Record Names, Relationships, and Alternate Keys

Tendo is transitioning toward a more efficient, simplified object architecture that includes implementing:

Reference by Record ID - ID as the basis for a reference field
Alternate Keys - Definition of one or more unique key fields for an object
Parent-Child relationships - A new tightly coupled relationship configuration

These foundational structures mean that Tendo is:

Transitioning identifiers to object fields, each with a unique alternate key.
Standardizing the use of internal Record ID as the primary key.
Transitioning away from detail objects
Improving performance and increasing understanding of the system

These architectural shifts impact Tendo’s integrations and applications.

Reference by ID

To get to full reference by ID, Tendo is guaranteeing references at time of creations so that reference stores the record ID and functions relying on the reference can execute properly.

All applications and platform components need to rely on references that are formed with the record ID while still allowing integrations to use identifiers. Internal reference based on Record ID will be used in guaranteeing referential integrity and will be the basis for Lookup and Parent Reference field types that are internal references.

All objects are being moved from reference by identifier to reference by ID so that all references will use record Id, integrations can still use identifiers, and apps and platform components don’t need to use identifiers except for interactions with integration.

This helps solve problems of out-of-sequence ingestion of data, and integration changing contracts with applications.

Apps services pass the Tendo record ID instead, and the workflow looks up the proper alternate key to use based on the ID.

Each object will have a set of unique identifiers that will become fields on the object.

Singular references store the ID of the referenced object record so that identifiers are no longer needed once records are ingested.

Array references that form many-to-many relationships continue to use a reference table. But references store the IDs so that identifiers are not needed once records are injected.

Identifiers still exist only for use by integrations. As data is ingested, references are resolved to ID so that identifiers are no longer needed in creating and maintaining references.

Record Name Defaulting

Tendo supports default expressions on Name fields to simplify updates of record names on objects. If an expression is provided for an object, it defaults in name value for existing and new records. It is supported for both standard and custom objects.

Once set, the default formula displays on the Name field accordion on the Name field.

Record Name Uptake

The Name fields are standardized as a system field that all objects have. The field is a human-readable identity that can be used in UIs. It is required and indexed. If no other value has been given when the object was created, the Record ID populates the field.

This field’s standardization allows for a search on any object by Name and allows UIs to systematically replace the record ID on the UI and link to the referenced object record.

Records are named so that Tendo also has a human-readable name for a record (rather than an ID or a blank space in the absence of a name).

This means that name fields are displayed in the Patient Care Journey app. For example, the name field that will display in the app’s Care Journey will be the title of a visit summary rather than the name of the provider with whom the visit was scheduled.

All objects have default names if not otherwise provided.

Record names are displayed where applicable, and are a consistent naming pattern.

Internal Reference Standardization

References are the foundation for linking data across objects. Tendo is moving toward separate internal and external references.

Based on Record ID, Tendo’s internal reference will be used in guaranteeing referential integrity and will be the basis for Lookup and Parent Reference field types that are internal references.

External references will now use the new Alternate Key field type, of which any object could have one or more.

This transition has impacted:

Integrations
Applications
Platform components such as Data Loader and Workflows that have been modified for the new model

Tendo's new model moves identifiers to explicit fields on the object, which are easier to understand and maintain referential integrity.

Singular reference are foreign keys that hold the record ID of the target object record.

Array reference fields continue to use a reference table which holds the record ID of the object record instead of the identifier ID.

Array references should only be used when necessary (e.g. back reference for walking the graph). If a many-to-many relationship is needed in the model, then a specific joiner object should be created. This will allow the relationship to be uploaded and modified through data loader and standardized APIs.
Identifiers are being moved to fields on the object and the tables removed. Alternate Keys are defined for the new fields.

Alternate Keys

Alternate Keys are being phased in and can be used to establish a relationship. An Alternate Key is a set of one or more object fields that is unique to the record. The main unique key on any object is the record ID (ID field). Each object can have up to 10 Alternate Key definitions. These keys are implemented in the database as unique keys and guarantee uniqueness of records within the object. Text or number field types can be used as part of the alternate key. Setting up an Alternate process is a two-step process:

Database unique key constraint
A reference can be established using an alternative key value.
- An administrator creates a key by first creating the alternate key definition with one or more fields. Once the definition is created, the admin activates it to create the database unique key restraint.
- Keys can use only text and number fields. They are either required or not required. When not required, nulls in the field are allowed and aren’t considered equal for uniqueness.
- Alternate keys are a data matching option for Data loader options - CREATE, UPSERT, UPDATE, and DELETE.
- Custom Objects only support alternate keys, not identifiers. Custom reference fields to existing standard objects will support identifiers until the referenced object is switched to reference by ID.
- Object managers can create and edit alternate keys in Tools > Data > Object through the Object Editor under a new Alternate Keys subtab on an Object.

To access Alternate Keys, the user must have permissions for Object Definitions: Manage or Object Definitions Read, as well as Application Data page definition.

Data Loader

Data loader load processes need to support three methods of matching:

Identifiers - The existing method
Record ID - Matching on our primary Id key
Alternate Key - Matching on one of the alternate key definitions.

Depending on the selected Operation and Object selected, a Matching Object dropdown will display. One or more of these options will appear, depending on the Object selected:

ID
Identifier
Alternate Key

When Alternate Key is selected for the matching method, a second field will display as a dropdown so that the user can specify which alternate key on the object to use.

All custom objects will only support ID and Alternate Keys.

The method relates to the main object only, not reference fields.

Reference fields are dependent on the object being referenced, and depend on the data being provided (ID, identifier, or alternate key). When multiples are provided, precedence will be ID, Alternate Key, then Identifier.

Under “Template Generation Options”, if Alternate Key is selected, the generated csv template will include columns for referencing fields by alternate key.

If the object referenced in the field has multiple alternate keys, columns will be generated for each one. Be sure to only provide the alternate key field values for one alternate key. If multiple are provided, the first alternate key values parsed from the csv will be used to establish the reference for that field.

The template will include all fields. When a combination of ID, Identifier, and Alternate Key are provided for a reference field, a note will display in the column: “Only provide ID, Identifier, or Alternate Key.”

Data export should be unaffected by Alternate Keys.

Parent-Child Relationships

Tendo is moving away from using detail objects in favor of using parent-child relationships instead.

A parent-child relationship is a tightly coupled relationship where the child has dependencies on the parent. Structurally, this is set up with a relationship field which is simply a field that holds the record iD of the parent record. The difference is the related behavior around the field.

There will be a new required attribute called Relationship Type, which has two options:

Lookup (default and optional), and Parent (required). This attribute cannot be Null.

Parent fields are required, and a child record can’t exist without a parent. Record access is controlled by the parent.

Objects will allow up to two parent fields for an intersection object forming many-to-many relationships.

In the hierarchy, up to 2 levels (child, grandchild) will be allowed. Circular references are not allowed.

References can be updated to another existing record, but not nulled.

Cascade Delete - When a parent record is deleted, all child records will be deleted.

A child can be deleted independently of the parent.

Data loading is sequenced - the parent must be uploaded before the child.

The object platform will return an error if an attempt to save (create/update/upsert) is made with an empty required field.

Move from Identifiers

Based on Record ID, the internal reference is used in guaranteeing referential integrity and is the basis for Lookup and Parent Reference field types that are internal references.

External references use the Alternate Key field type. Any object can have one or more. As an explicitly defined field, it is easier to guarantee that it is available and does not require extra joins.

All objects are moving from reference by identifier to reference by ID for each object:

New Fields and Alternate Keys will be defined and created for each identifier.
Identifiers will move to record fields. The object will have alternate keys defined for each unique identifier, system (type tuple).
Singular reference fields won’t have a reference table and the reference will store the Record ID.
Array reference fields will have reference tables, but will store the record ID in the target object record.

What needs to move for an object:

Object Identifiers to object fields with Alt Keys
Reference fields on other objects that point to this object need to be updated to store ID
Remove need for reference tables for singular references

Referential Integrity

Referential integrity is a critical data property that guarantees the accuracy of references, maintaining the relationship between two tables. It is enforced by ensuring that foreign key reference fields in one table stay current when changes occur to the primary keys they reference in another table. It ensures the consistency and accuracy of relationships between records in different tables.

By upholding referential integrity, systems prevent users from encountering invalid references when accessing records or databases bloating due to orphaned records. Ensuring that the system updates references when records are deleted or removes records appropriately is fundamental for maintaining data quality.

The main reasons referential integrity is violated are:

Non-unique primary keys leading to duplicate records
Failure to keep foreign keys up to date when records are deleted, resulting in invalid references
Records not being removed when parent records are deleted, causing orphaned records

Integration with Electronic Health Records (EHRs), which often own a substantial amount of the data, requires that Tendo be flexible when importing data, as it may not always arrive in order. However, Tendo’s system still needs to maintain referential integrity. Tendo needs to carefully determine when imported data is ready for user consumption and enforce referential integrity accordingly.

Tendo is:

Requiring that all records have a Tendo-generated record ID that is a record primary key.
All foreign key references use the primary key (record ID).
Two types of relationships are supported:
- Parent-Child Relationship - Tightly coupled. When the parent record is deleted, the children records are deleted.
- Lookup Relationship - Loosely coupled where the referenced records are not deleted.
  Updating all references when a record is deleted
- Considering integration needs, including:
  - External References that they need and maintain
  - How and when rules are relaxed for loading
  - How and when data is ready for use by the system so that referential integrity is enforced

Users and application developers rely on quality data for display and expect that when querying a record, accurate non-null foreign key reference will bring in data.

As more data is brought into Tendo from external sources, the possibility of orphaned records bloating the system grows. This happens with lazy loading of integrations, and the deletion of parent records while child records are not deleted. Tendo needs to know when child records should be deleted and enforce that.

Customers also can request that all data for a patient be removed. This requires that all parent-child foreign keys be up to date. If not, the risk is that some data will be missed.

The Record ID must be a field on all objects and the primary key for all records.
All foreign key references must use the Record ID of the referenced record.
External ID fields are maintained separately and not used for internal referential integrity.
The parent-child relationship is tightly coupled, which will enforce certain system behaviors
Lookup is a type of relationship that is loosely coupled and will enforce certain system behaviors.
When parent records are deleted, all children records through the Parent ID will be deleted.
When a record is deleted, all references to it will be updated.
The system won’t allow deletion of records with required references to the record, and will return an error.
Parent fields are required fields. A child record can’t insert without a valid parent ID.
Rules for how and when the data is relaxed for loading.
Rules for how and when data is ready for use by the system so that referential integrity is enforced.
The need for data model changes will be reverted. Data may need to be updated to confirm with the design.

There is no configuration associated with referential integrity.

Objects that don’t have integrations, such as Reports, Roles, and Domains, don’t need identifiers.

Upon moving to the new system, each identifier record will become a field on an object. Some will be customer-specific custom fields. An alternate key will be the definition of a field constraint.