3

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.

2

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

0

In most schemas, roles are defined in a separate table, and there is a table userroles which contains information about the roles (note the plural) a user has. You should consider adopting this scheme, because it will be more flexible for future developments.


However, when you're set on sticking to the tables you already have, you basically have two options (which are not exclusive):

  1. adding a boss_id column to the companies table, what you already mentioned. To check if a user is a boss, you need to join the users table with the companies table, on companies.boss_id = users.id. Depending on the number of users, it might be worthwhile to make an index for the boss_id column.
  2. adding an is_boss boolean column to the users table. To check whether a user can be assigned to the boss role in a company, just check if there's another user with a matching company_id and is_boss = true. Again, depending on the number of users, it might be worthwhile to make an index on company_id and is_boss.

Without knowing further details about your application, it's hard to say which is the best of the two. Implementing both will be more work, but better from a performance perspective.

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.