Circular Foreign Key

Question

I often see database modeled so that foreign keys close a circular reference, to exemplify I used a model with cidade,bairro and cliente. Follows the ER model:

I understand that in this format, there may be inconsistency of the data.

Questions are:

Is there any documentation that defines this modeling as right or wrong?

Circular Reference is the correct term to define this situation?

Answer 1

Is there any documentation that defines this modeling as right or wrong?

What exists is not right or wrong, but the ideal model for each application. In some cases, the use of standardisation is ideal to prevent inconsistencies from being generated in the database due to repetition of data. In other cases (e.g. Applications that respond in real time), normalization can be harmful because it increases the number of tables consequently increasing the number of joins and thus slowing down consultations. Sometimes it is acceptable to replicate some data in different tables in order to optimize queries in the system, but this increases the risk of generating inconsistencies, and so these decisions need to be well thought out. At the end of the day it’s a trade-off.

Circular reference is the correct term to define this situation?

This term can be used to define this situation. The big risk is when applications that use JPA, for example, map objects without using lazy-loading. By correctly implementing the system and depending on application requirements, this cyclic behavior is acceptable.