User account model with two or three possible profile

Question

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.

Answer 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).