I'm trying to design a secured schema to support a feature in an existing multi-tenant database. The existing database holds customers and child branches (many branches to one customer). I'm trying to add the ability to assign contact persons to branches (many to many), as long as the contact person is not mapped to branches of different customers.
So far I came up with the following DB schema:
However, this schema doesn't enforce that a particular contact is not mapped to branches of different customers. Theoretically, BranchToContactAssociation can hold two records, each one of a different branch of a different customer, pointing to the same contact. While this situation shouldn't be allowed, two branches of the same customer can point to the same contact.
Even after digging into the different types of constraints supported in PostgreSQL, I didn't come up with a solution. My question is whether there is a mechanism in PostgreSQL which is able to enforce such constraint, and if not - how do you recommend enforcing it in an error-proof way.