The simplest way to store bilateral relationships is in a relationships table, with the subject (party_a) of the relationship in one column and the counterparty (party_b) of the relationship in another.
| Column | Type | Description |
|---|---|---|
id |
UID | A unique id for each relationship record |
party_a |
foreign key | The contact id of the relationship's subject |
party_b |
foreign key | The contact id of the relationship's counterparty |
type |
string | The relationship type, e.g. is a founder of |
detail |
string | More detail of this particular relationship, e.g. CEO |
| Operation | Description |
|---|---|
| Create | The UI must determine which contact is party_a and which is party_b. For each relationship type there may be business rules that constrain the contact type of party_a or party_b. |
| Retrieve | Enumerating the relationships for a contact is a two-pass operation: find the relationships where this contact is party_a and add them to the relationships where this contact is party_b. |
| Update | Updating is straightforward: there is only one relationship record for each relationship |
| Delete | Deleting is straightforward: there is only one relationship record for each relationship |
This is the design I used for Xendata.
A more sophisticated way to store relationships uses two tables: relationships and relationship_parties. This allows us to retrieve all a contact's relationships in one query and also to have relationships with more than two parties, if that is a requirement.
A relationship is defined by a master record for the relationship (in relationships) and an associated record for each party in the relationship (relationship_parties).
| Column | Type | Description |
|---|---|---|
id |
UID | A unique id for each relationship |
type |
string | The relationship type, e.g. is a founder of |
detail |
string | More detail of this particular relationship, e.g. CEO |
| Column | Type | Description |
|---|---|---|
relationship_id |
foreign key | The unique id for this relationship |
contact_id |
foreign key | The contact id of this party in the relationship |
subject |
boolean | True if this party is the subject of the relationship |
| Operation | Description |
|---|---|
| Create | The UI must determine which contact is party_a and which is party_b. For each relationship type there may be business rules that constrain the contact type of party_a or party_b. Additionally, three records must be created for a bilateral relationship: a master record and an associated record for each party. |
| Retrieve | Enumerating the relationships for a contact is a single-pass operation: find the relationship_parties for this contact. To display full relationship details requires that the associated relationships records are also retrieved, and the relationship_parties records for the other parties. |
| Update | Updating is complex: any or all of the three records might be affected |
| Delete | Deleting is complex: all three records must be deleted. |
All data-changing operations need to be performed atomically.
This is the design I used for the old Cirx Research app. On the whole the added efficiency of retrieval is outweighed by the complexity of the data-changing operations. Also it's usually necessary to join to the master relationships table anyway so the efficiency gains are in practice very small.
With this approach we store a single relationship as a pair of records in a relationships table. One record stores the relationship in an active sense (a has a role with respect to b) and the other stores the same relationship in a passive sense (b has a in a given role).
| Column | Type | Description |
|---|---|---|
id |
UID | A unique id for each relationship record |
party_a |
foreign key | The contact id of the relationship's subject |
party_b |
foreign key | The contact id of the relationship's counterparty |
type |
string | The relationship type, e.g. is a founder of (active) or has founder (passive) |
detail |
string | More detail of this particular relationship, e.g. CEO |
Each relationship is stored as a pair of records.
| Operation | Description |
|---|---|
| Create | The UI must determine which contact is party_a and which is party_b. For each relationship type there may be business rules that constrain the contact type of party_a or party_b. A pair of records is written for each relationship, with the position of the contacts reversed and the relationship type to the passive equivalent in the second record. |
| Retrieve | Enumerating the relationships for a contact is a single-pass operation: find the relationships where this contact is party_a. |
| Update | Updating is complex: both records must be updated to reflect the same state. |
| Delete | Deleting is complex: both records must be deleted. |
All data-changing operations need to be performed atomically.
This approach is workable, but only if the CRUD operations always treat the relationship records as a pair.