Relationship Types and Examples

1. One-to-One

In a one-to-one relationship, each parent has exactly one child, and children cannot be shared among parents.

1. Example: An Employee and their Address. Each employee has one unique address, and that address belongs only to that employee.
2. Implementation: Appian recommends a nested design for this, where the parent CDT (Employee) contains a field of the child CDT type (Address).

2. Many-to-One

Here, multiple parents can share a single child value, which is often used for lookup data.

1. Example: An Order and its Status (e.g., "Pending", "Shipped", "Delivered"). Many orders can have the "Pending" status, but each individual order only has one status at a time.
2. Implementation: Similar to one-to-one, a nested design is recommended. The parent CDT (Order) includes a field that references the primary key (PK) of the child CDT (StatusId of type Integer, for example), or the entire child CDT can be nested within the parent.

3. One-to-Many

In this relationship, one parent has multiple children, but each child is unique to that parent.

1. Example: A Case and its Comments. One case can have many comments, but each comment belongs to only one case.
2. Implementation: Appian recommends a flat design to avoid performance issues with lists of lists. This means the child CDT (Comment) will contain a field (caseId) that is a foreign key referencing the parent's PK. The parent CDT (Case) does not explicitly contain a list of Comment CDTs in its definition.

4. Many-to-Many

This relationship involves multiple parents being associated with multiple children, and vice versa.

1. Example: An Order and Products. An order can contain multiple products, and a single product can be part of many different orders.
2. Implementation: This requires a flat design using a third, separate "join" or "mapping" CDT (e.g., ProductsForOrder). This join CDT contains foreign keys referencing the primary keys of both the parent (orderId) and child (productId) CDTs.

For details visit here