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

StackExchange Database Administrators Q#196032, answer score: 2