Why is it important to give unique names to database relationships?

In this article, I am talking about the importance of giving unique names to relationships among tables in a relational database. I am explaining this by using my recent experience of dealing with a rather cryptic error occurring due to using the same name for describing relationships among a set of tables. I am also explaining this with Prisma and Postgres, but the concept applies to all database management systems.

What is Prisma?

Fig. 1. What Prisma does (in general)?

Prisma is an ORM (Object Relational Mapping) system, which maps the relations(tables) in your (relational) database to objects which can be used in your programs just like other objects(instances). Actually, it does much more than that, but for the purpose of this article this much of understanding is sufficient.

Scenario

What happens when you name two relationships with the same name in a Prisma Schema Object. Let’s help that with a scenario. As shown in the fig. 2. below, a car is under an owner. A car is also under an insurer. Now what happens when we use the same name to describe the relationship of a car with its owner and insurer?

Fig. 2. Scenario for describing the purpose of using unique names

what’s the issue with using the same for describing relationships among tables?

Fig.3. Cryptic error description resulting due to confusion

Prisma provides a feature called as include, which allows you to include related records when fetching a record. So, with respect to the above scenario, Prisma can include related Owner and Insurer records when fetching a car record. However, when you try to include the Owner and the Insurer records when fetching a Car record, Prisma will throw a rather cryptic error with the description which doesn’t provide any help. The description will tell you to create a crash report using the link provided as in the description. An example of this type of cryptic error description is shown in fig 3.

Let’s understand the root cause

If you take a deeper look at the issue, you will find that Prisma gets confused about what to include when you use same name for describing different relationships of a table with other tables. This is the same problem as shown in fig 4. below. The manager asks Jack to do something, and both Jacks are looking at each other about who he addressed? Confusion.

Fig, 4. Confusion resulting due to same names

How to resolve this issue?

Fig. 5. How to resolve ambiguity resulting from using the same name for describing relationships

• In a given Prisma object, never use the same name for describing its relationship with other objects.
• Always, give specific and unique names to relationships.
• With respect to the example scenario used above, it could be:
• A Car is ownedBy an Owner.
• A Car is insuredBy an Insurer.

Thanks for reading 👏. Please feel free to share your thoughts and any alternative solutions.