I'm have an issue with circular reference in my DB design. As see the ER diagram below have 2 circular reference that I highlighted with Red and Orange arrow.
Anyone know how to resolve this issue?
Circular reference and multi-tenancy
database-designmulti-tenant
Related Solutions
There's no need for both relationships.
Generally speaking, your category table should have one row per category, not per fact table row.
It's OK if multiple EReport rows refer to the same category record, but having a category record per EReport record defeats the purpose of normalizing in the first place.
How about this structure then?
Related Question
- Mysql – Probable circular dependency, but query still runs
- Database Design – How to Avoid Circular Foreign Key References
- Sql-server – SQL Server Multi Tenancy Setup
- Avoid circular reference with configuration tables
- Multi-Tenancy and Shared Customers in PostgreSQL
- Postgresql – Circular references for tracking currently active entity
Best Answer
You do not have circular references. All those arrows point in the same direction, "up" to the tenant.
You can insert data to populate the tables without having a dependency on data not yet inserted.
This would only be a circular reference if, for example,
tenanthad auser_idreference that pointed back down to theusertable, which references back up to thetenant.The arrows in your diagram have multiple paths back up to the "top" of the hierarchy (tenant, which is drawn to the bottom left). There is a clear tail and head of every path.
But the arrows don't create a circular cycle. In a circular reference, there is no clear head or tail in the path of the arrows.
So great job on your schema design! You did better than you thought.