Defining Two Distinct Relationships Between User Table and Company Table in MySQL

database-designMySQL

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.

Best Answer

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.

Related Solutions

Constraint many to many table between two child tables

Why must the employees working on a project strictly belong to the same division that owns the project? That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI. You said you have a many-to-many table associating employees with projects. Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID) 
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID) 
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.

Mysql – User roles and permissions

The simplest structure would be a PERMISSIONS table like this:

create table PERMISSIONS
( Site_ID int
, User_ID int
, Role char(1)
, primary key (Site_ID, User_ID, Role) -- scenario 1
-- OR:
, primary key (Site_ID, User_ID) -- scenario 2
)

Use values for Role such as:

A = Administrator
P = Publisher
E = Editor

How many rows you need depends on how your code interprets the data. You could have very simple code which interprets each permission strictly at face value. To do an administrative task, the user would need an 'A' permission. To do a publishing task, they would need a 'P' permission, etc. In this case an administrator would need three records per site (one each of A, P, E). Similarly publishers would need two records per site and editors only one. There is a risk in this approach that data consistency errors could creep in, like someone could be a publisher, but not an editor (by virtue of the 'E' record being missing).

Alternatively, you could make your code more complex, in which case each user only needs one permission record per site. In this scenario, administrative tasks require an 'A' permission, publishing requires an 'A' or a 'P' and editing requires 'A', 'P', or 'E'. The advantage of this approach is that there is less data to maintain and you won't have inconsistencies.

Note too that you do not need to have the Users.Parent column. If an administrator has an 'A' permission for a site, that automatically gives them control of all of the users for that site.

Best Answer

Related Solutions

Constraint many to many table between two child tables

Mysql – User roles and permissions

Related Question