I have two tables (
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
companiestable? If so, should I leave the
company_idNULL for this kind of User? (but it seems weird) Or should I add
boss_idin both tables? (but it's redundant).
- Should I keep going with only
company_idand 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.