2

I'm having issues designing a satisfactory solution for the following problem.

There are two main entities:

  • Customer
  • User

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:

data model

(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?

  • It is possible for a user to be admin of several customers? – Renzo Jun 11 '18 at 9:02
  • @Renzo Yes, that would be entirely possible. – Willem-Aart Jun 11 '18 at 11:08
  • Is it your preference that being an “admin user” is a property of a User, or alternatively that it is defined by a relationship (as in your example). If the latter, it means that one customer’s admin user could be another customer’s normal user. – John Rees Jun 13 '18 at 3:29
  • @JohnRees It's defined by the relationship. Indeed, one user might be a customer's admin and another customer's normal user at the same time. – Willem-Aart Jun 13 '18 at 14:02
3

I think that your problem could be solved by imposing a new Foreign Key constraints in your tables:

User (UserId, UserOther) PK(UserId)
Customer (CustomerId, CustomerOther, UserAdminId) PK(CustomerId)
Customer_User (CustomerId, UserId), PK(CustomerId, UserId)

In Customer_User:
  FK(CustomerId) REFERENCES Customer
  FK(UserID) REFERENCES User

In Customer:
  FK(UserAdminId) REFERENCES User
  FK(CustomerId, UseAdminId) REFERENCES PK(Customer_User)

In this way you are guaranteed that a Customer has a valid User as admin, and that there is a tuple in Customer_User that corresponds to a the admin of a certain Customer.

  • Hmm, can't get this to work. I don't understand the following line: FK(CustomerId, UseAdminId) REFERENCES PK(Customer_User). When I change it to FOREIGN KEY (primary_admin_user_id , id) REFERENCES customer_user (user_id, customer_id) I end up with a circular reference which makes it impossible for me to enter the data. Did I miss something? – Willem-Aart Jun 11 '18 at 20:16
  • 1
    In a case like this the insertion is possible by using the DBMS machinery to defer the checking of the constraints at the end of the transaction. For instance, in PostgreSQL this is done starting the transaction, then issuing a SET CONSTRAINTS ALL DEFERRED, insert the tuples in both Custumer_User and Custumer, and terminating the transaction. In other DBMS thare are similar mechanisms, since this is a classical situation in Relational Databases. – Renzo Jun 11 '18 at 20:27
  • That makes a lot of sense. Unfortunately I'm using MySQL (InnoDB) which doesn't have support for deferred constraints. Am I out of luck, or do I still have options (other than switching to Postgres ;-))? – Willem-Aart Jun 11 '18 at 20:48
  • 2
    I'm sorry, I wrongly supposed that every RDBMS had a mechanism of this type, since it is defined in SQL standard (I've checked also SO, but the answers to this question cleary state that it is impossible to defer constraints in MySQL). So, a part from switching to Postgres :) I think that the solution that you have presented in the question is the right way to go, with extra care in the application to avoid inserting inconsistent data. – Renzo Jun 11 '18 at 21:02
  • 1
    I checked also the last version of MySQL (8.0), and it is still impossible to do deferred checking (see this page). – Renzo Jun 11 '18 at 21:09
0

You can give the Customer_User table its own primary key Customer_User_id and replace your primary_admin_user_id by a primary_admin_customer_user_id

That way you are sure that your admin user exists in the Customer_User table.

The only risk that remains is that your admin user points to a Customer_user record that is actually for another customer.

  • Maybe I'm mistaken, but this sounds like a circular reference, right? A valid Customer_User record cannot exist without a Customer record. In your proposed solution, a valid Customer cannot exist without a Customer_User record, since there must be an admin. – Willem-Aart Jun 10 '18 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.