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.

  • 1
    Why can't I have three companies? – Basilevs Jan 22 '16 at 7:08
  • These are rules from my client – Juliano Nunes Silva Oliveira Jan 22 '16 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. – alexanderbird Apr 20 '16 at 21:46

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, privacy policy and cookie policy

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