Contract conceptual model

Beware of Symmetric Relationships

A symmetric relationship is a self-relationship with the same multiplicity and role name on each end. Symmetric relationships are acceptable for conceptual models. But they are problematic for logical and physical models – you should rework the model to eliminate them.

An Example

For an example, consider that a contract may relate to many other contracts. This leads to a many-to-many relationship among contracts.

Contract conceptual model

The vast majority of symmetric relationships are many-to-many but one-to-one symmetric relationships are also possible.

Problems with Symmetric Relationships

Symmetric relationships are common and acceptable for conceptual models. The problems arise with logical and physical models. Symmetric relationships can be troublesome for programming and are always troublesome for relational databases.

If we elaborate the contract model, the problems become apparent.

Contract logical model

Suppose that contract 333 relates to contract 777. Then is 333 contract1 and 777 contract2? Or is 777 contract1 and 333 contract2. This is the problem of breaking symmetry. With the logical model we have to specify which is first and which is second even though that has no bearing on the intended relationship.

We can make data storage predictable by storing the smaller ID as contract1 and the larger ID as contract2 as shown below. However, this workaround leads to a double search. To find all contract 333 relationships, we have to search contract1_ID and union the results to a search of contract2_ID. A double search is inefficient and complicates database queries.

contract1_IDcontract2_ID
111333
333777

Another workaround is to enter data twice. But this introduces a new set of problems. We increase the database size. More importantly, we add redundancy and the possibility for a missing copy.

contract1_IDcontract2_ID
111333
333111
333777
777333

The root problem is that a relational database is inherently asymmetric and cannot represent a symmetric contract model.

A Solution

A solution is to promote the relationship to an entity type. Then, for example, we have a contract 111 record, a contract 333 record, and a relationship record that binds them together.

Yet, even this solution is still flawed. Do we want one relationship that binds 111, 333, and 777? Or do we want two relationships, one between 111 and 333 and another between 333 and 777. Either is a reasonable interpretation of the data. Note that the revised model restricts a contract to one contract relationship so the two relationship interpretation is not possible.

ContractRelationship one to many

The Solution

Fortunately, we can resolve our dilemma by adding a many-to-many relationship. With the revised model each Contract_Relationship has a relationship_type. So we can represent either interpretation of the contract data. There can be one contract relationship among 111, 333, and 777. Or there can be two relationships of different types, one between 111 and 333 and the other between 333 and 777.

ContractRelationship many to many

Leave a Reply

Your email address will not be published. Required fields are marked *