1

My application will have at least two types of users: clients and companies, these are the types of profiles. Because each type of profile requires different additional information, it will be required to create a table for each type of profile.

How should I create my relationships so a user has zero or one client profile, or zero or one company profile, but in a way that he must have one and only one of the two types of profiles?

E.g. John is a client, then he has a row on Users table and UsersProfiles. He can't have a row in CompaniesProfiles at the same time.

3
  • 1
    Why can't I have three companies?
    – Basilevs
    Jan 22, 2016 at 7:08
  • These are rules from my client Jan 22, 2016 at 16:22
  • It sounds like you're wanting multiple table inheritance, from an object-relational mapping point of view. That's not really an answer because you're not asking about how to set it up with an ORM, but sometimes thinking about the problem in a different way can help. You have one table that maps to the abstract base class, and then another table for each of client and company, which are concrete subclasses. From the database side you use either of @TMN's solutions. Apr 20, 2016 at 21:46

1 Answer 1

1

I think you have two choices: either have one profile reference and an indicator in the user record to identify the profile type, or have two profile references and enforce your requirement using insert and update triggers. In the first case, you'll have to forego referential integrity (since your foreign key can reference two different tables), but you save space by only using one field. In the second case, you maintain referential integrity at the cost of added complexity due to the use of triggers (and possibly more complexity if you need to do bulk inserts, since triggers are often disabled for performance reasons when large amounts of data are inserted).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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