I'm having issues designing a satisfactory solution for the following problem.
There are two main entities:
A customer can have many users associated to it. A user can be associated with many customers. Definitely a many-to-many relation for which a junction table should exist.
A customer must have at least one admin user. This is absolutely critical.
For several reasons it would be nice to enforce that the combination of customer & corresponding admin account exists in the junction table. For example: when listing the customer's users, it would be nice to not have to manually include the admin user.
In the following design it would be possible for the combination of customer & corresponding admin to not exist in the junction table:
(ignore the erroneously placed crow's foot at 'User')
Is there a good way to solve this problem?
The solutions I came up with so far involved introducing circular references, which I think are undesirable. Another option might be to go with this design and use a VIEW for the user listing. Feels like a workaround.
Is there a flaw in my design or is this a limitation of relational databases?