What are types of relationships in Appian CDTs?

Types of Relationships in Appian CDTs

Custom Data Types (CDTs) in Appian support four types of relationships: one-to-one, many-to-one, one-to-many, and many-to-many.

Each one describes how records in one CDT associate with records in another.

1. One-to-one relationship

Each record in the parent CDT can be associated with only one record in the child CDT, and that child record cannot be shared with another parent.

Example: Employee and Address

1. Each employee has one unique address.
2. Each address belongs to only one employee.

Implementation in Appian:

For one-to-one relationships, Appian recommends a nested CDT design.

1. The parent CDT (Employee) contains a field of the child CDT type (Address).
2. The employee's address information can then be accessed directly through the parent CDT.
3. A flat design also works: the Employee CDT contains an addressId field that references the primary key of the Address CDT.

2. Many-to-one relationship

Multiple parent records can reference the same child record, but each parent record can reference only one child record.

Example: Order and Status

1. Multiple orders can have the same status, such as "Pending", "Shipped", or "Delivered".
2. Each order has only one status at a time.

Implementation in Appian:

Many-to-one relationships also use a nested CDT design.

1. The parent CDT (Order) can contain a field of the child CDT type (Status).
2. The order can then access its related status information directly.
3. A flat design also works: the Order CDT contains a statusId field that references the primary key of the Status CDT. This is useful when status values are maintained separately as lookup data.

3. One-to-many relationship

One parent record can have multiple child records, but each child record belongs to only one parent.

Example: Case and Comments

1. A single case can have multiple comments.
2. Each comment belongs to one specific case.

Implementation in Appian:

One-to-many relationships use a flat CDT design, which avoids the performance issues associated with nested lists.

1. The parent CDT (Case) contains the case information.
2. The child CDT (Comment) contains a caseId field that references the primary key of the Case CDT.
3. The parent CDT does not contain a list of Comment CDTs.
4. Comments for a particular case are retrieved by querying the Comment CDT using the caseId.

This gives you better control over database queries and avoids retrieving child records you don't need.

4. Many-to-many relationship

Multiple records in one CDT can be associated with multiple records in another CDT.

Example: Order and Products

1. A single order can contain multiple products.
2. The same product can be included in multiple orders.

Implementation in Appian:

Many-to-many relationships use a flat CDT design with a separate mapping (join) CDT. Three CDTs are involved:

1. Order contains order information, with orderId as its primary key.
2. Product contains product information, with productId as its primary key.
3. OrderProduct is the mapping CDT. It holds orderId and productId as foreign keys referencing their respective CDTs.

Each record in OrderProduct represents one association between an order and a product. When required, the mapping CDT can also store additional fields, such as quantity or unitPrice.

Summary

Reference: Appian Documentation — CDT Design Guidance