1..1 and 1..n cardinalities on the same relationship I have two tables (companies and users), for which the following rules apply:


A User has a Role ('employee' and 'boss')
There is one and only one 'boss' in a Company.
A User can be in only one Company.


Before thinking about the Boss case, I created only two tables and added a foreign key column company_id on the users table. So every User has a Company and everything is alright.

But now, there is a new rule: there can be only one boss. How do I write this rule in my schema?


Should I create a boss_id column in companies table? If so, should I leave the company_id NULL for this kind of User? (but it seems weird) Or should I add company_id and boss_id in both tables? (but it's redundant).
Should I keep going with only company_id and keep the rule in my head ("hey, remember, the schema does not tell anything about this, but you can't create two bosses for the same Company").
Should I... Hum, I must be tired I forgot the other patterns?


I think I missed something really simple, but I can't figure it!



This is an over-simplifed example, one could say there can be multiple bosses.
In your requirement employee means not boss, so this should work fine.


-- Company (CompanyID) exists
--
Company {CompanyID}
     PK {CompanyID}

-- User (UserID) works for company (CompanyID)
--
User {UserID, CompanyID}
  PK {UserID}
  SK {UserID, CompanyID} -- superkey (unique), needed for FK ..

 FK1 {CompanyID} REFERENCES Company


-- User (UserID) is boss in company (CompanyID)
--
CompanyBoss {UserID, CompanyID}
         PK {CompanyID} 
        FK1 {UserID, CompanyID} REFERENCES User



Note: all attributes NOT NULL