Click to learn more about author Michael Blaha.

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.

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.

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.

Related_Contract

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.

Related_Contract

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.

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.