2

A very basic design question here. I would like to build a scalable app and would want to know what's the best way forward. So in the database there are two tables : users and companies.

users has the following fields:

-id
-name
-company_id
-join_date
-leave_date

companies has the following fields:

-id
-admin_id
-address

I want a 1-to-M between users and companies as one company could have many users, but one user belongs to only one company. Also, I want to have a 1-to-1 on users and companies because one company has one admin and vice versa. I'm not sure if I am tackling this right. The admin role would basically have more privileges on the system. But I cannot understand if it should be a 1-to-1 or not. Who would the parent be, etc.

  • Usually there would be a role (or role_id) on users (what if a company is going to have 2 admins?). In more complex enviroments a user will have 1 or more role and a role will have 0 or more users (many to many). – vercelli Sep 1 '16 at 16:12
  • @vercelli, in this app's v1, there's only one admin. – omrakhur Sep 2 '16 at 8:31
4

To guarantee that a company, which can have many users, can ever have at most one admin, and further guarantee that the user defined as the admin is also a user defined for the same company, create a separate admin table. However, the foreign key definition has to be created in a specific way.

create table Companies(
  ID      int not null auto_increment,
  Name    varchar not null,
  Address varchar,
  primary key( ID )
);

create table Users(
  ID      int not null auto_increment,
  Name    varchar not null,
  primary key( ID )
);

create table CompanyUsers(
  UserID    int not null references Users( ID ),
  CompanyID int not null references Companies( ID ),
  JoinDate  date not null,
  LeaveDate date,
  primary key( UserID ), -- makes this 1-n
  unique( UserID, CompanyID )
);

create table Admins(
    UserID   int not null,
    CompanyID int not null,
    ..., -- other admin-related data
    primary key CompanyID,
    foreign key( UserID, CompanyID ) references CompanyUsers( UserID, CompanyID )
);

At first, the Admins table appears to define a 1-n relationship the same way as the CompanyUsers table. But if you follow the FK relationship through, you will see that it can only support 1-1 relationships.

Technically, you don't need the CompanyUsers table as you can define the company and dates in the Users table. This maintains the 1-n relationship but then it is no longer in 3nf. If you are going to have attributes (JoinDate, LeaveDate) that are dependent on more than the PK (that is, they describe the relationship rather than the user), they should go in a new table.

|improve this answer|||||
  • Nice use of declarative constraints to enforce the business rule. However, the unique index constraint on CompanyUsers (UserID,Company) is unnecessary because UserID is already unique. – Joel Brown Sep 2 '16 at 11:21
  • 1
    It is necessary because they are a referent of the FK in the Admins table. This FK is necessary to make the Admins table a 1-1 relationship. – TommCatt Sep 2 '16 at 19:23
0

users.company_id :: companies.id is 1:many; companies.admin_id :: users.id is 1:1. Sounds fine.

If you use FOREIGN KEYs, there may be a chicken-and-egg problem, but that can be worked around.

|improve this answer|||||

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.