Question: I have to design a database in a Rails application, where I have users (who can login), companies and jobs.

Each user belongs to a company, and each job belongs to a company, so when a user logs in, the application would search it's company and show him *every* job assigned to his company. The diagram is here:

[![First approach](https://i.stack.imgur.com/5Xx54.png)](https://i.stack.imgur.com/5Xx54.png)

However, there is another spec: in every company, there is an administrator and many workers. Only the administrator can see **every** job that is assigned to every user of his company, while the workers can only see the job that was assigned to him: a worker can not view the job assigned to his colleague.

Here is the big question: Can I add a new field to the Jobs table, a foreign key that references the user table, so I can keep track of every job that belongs to a company and also the user that is assigned to a specific job? I know that it is a circular reference, and it is not a good practice, but I can't see any other solution to this problem, and I'm not quite sure about the problems I might get if I follow this approach. Here is the diagram of this circular "solution".

[![Circular or redundant approach](https://i.stack.imgur.com/szig4.png)](https://i.stack.imgur.com/szig4.png)

**EDIT #1**

Many colleagues suggested very clever ways to gave roles (admin, user, manager, etc) to the users. I ommited because my major concern is the relationship between companies, jobs and users. Anyways, to be clearer, I updated the images to incorporate the "roles" table.

Answer: 
I think you need to alter the initial design to facilitate what you want to do. Please check this diagram:

[![enter image description here](https://i.stack.imgur.com/yfFAO.png)](https://i.stack.imgur.com/yfFAO.png)

When your user logs in, you check the UsersAndCompanies table. Then, you look at the UsersAndRoles to see what sort of permissions the user has (eg. admin, read-only, edit, etc.). Then, you move to move to Jobs and retrieve the jobs for a compnay. Finally, you move to UsersAndJobs to get the jobs for the logged in user based on the permissions. 

This design allows you to 1) assign more roles to a user (but I understand this is not your concern here), 2) allow more than one jobs to a user. If you have more jobs that can be shared among different companies you need another table (eg. JobsAndCompanies) but this is a different story
I agree with Satanicpuppy in principle.

However, as another option, I would add a column to the `user` table called `type` and then define several types, one being `admin`. This would add more flexibility should you (in the future) have other users with different permissions or privileges.

A reference table would also be nice -> to define your `types`. For example:

```
type (table)
id type_desc
1  user
2  admin
3  manager

```