I often see database modeled so that foreign keys close a circular reference, for example I used a template with cidade , bairro , and cliente . Here's the ER model:
I understand that in this format, the data may be inconsistent.
The questions are:
Is there any documentation that defines this modeling as right or wrong?
Circular Reference is the correct term to define this situation?
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 normalization is ideal to prevent inconsistencies in the database from being generated due to data repetition. In other cases (e.g. Real-time responding applications), normalization can be harmful because it increases the number of tables consequently increasing the number of joins and thus making the queries slower. Sometimes it is acceptable to replicate some data in different tables to optimize queries on the system, but this increases the risk of inconsistencies, 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 the application requirements, this cyclic behavior is acceptable.
