-1

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

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

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.

  • 11
    This is not a circular dependency, this is a redundant one. – Doc Brown May 9 '17 at 20:43
  • Agreed. This is not a circular reference. It doesn't even look circular in your diagram. – Robert Harvey May 9 '17 at 20:47
  • Do you mean I could delete the company_id reference, and let only the user_id reference on the Jobs table? So I could figure out every user that belongs to a company, and with every each user retrieve all the jobs? What are the problems with the redundant dependencies, anyway? – Karl May 9 '17 at 20:47
  • 3
    The problem with redundant dependencies is that you have two sources of truth, not one. You have to maintain both of them, and if they get out of sync, it can cause problems. If they differ, which one is correct? – Robert Harvey May 9 '17 at 20:49
  • 2
    You describe two scenarios, but the diagrams don't match the scenarios that you describe. For example the suggested FK is already in diagram 1 and said diagram doesn't reflect "Each user belongs to a company, and each job belongs to a company" .. – Tulains Córdova May 12 '17 at 22:00
1

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

enter image description here

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

0

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
  • Thank you for your feedback, but for the sake of simplicity, I ommited the roles table, since my major concern is the relationship between jobs, companies and users. In the complete system I already did what I suggested. I will update my post to say that the role's problem have been take care of. – Karl May 9 '17 at 23:42
  • Yea, this is definitely the right way to go for future expansion of role stuff. If you're going to have just users and admins, and nothing else, adding a line to the user table is enough, but as soon as you need more fine-grained permissions, that goes out the window. – Satanicpuppy May 10 '17 at 12:36
0

It's user data, it needs to be in the user table. Adding a whole row in the job table is taking a step down a dark path...What if they add a new kind of admin who can see every job started by a user whose user id starts with a 4...Then you're going to need another field in the job table.

Admin status can be added trivially to the user table.

SELECT * 
FROM jobs 
WHERE 
  (companies.id = jobs.company_id AND job.user_id = user.user_id) OR
  (companies.id = jobs.company_id AND user.admin = 1);
  • Thanks for your feedback, but do you mean about the roles table? of the column "user_id" on the table "Jobs"? If you concern about the roles table, I ommited it for the sake of simplicity, but if you concern about the "user_id" column, if I put a "job_id" column on the table "users", my users will only be able to have one job assigned. The spec says a User must have many jobs. Let me know what is your major concern, so I can update my main post with more information. – Karl May 9 '17 at 23:40
  • @karl Jobs belong to users, not users to jobs. User_id on the job table. But for admins, they see jobs based on the company, so if the user is an admin, select by the company, rather than the user. You already have the company_id in the job table...Might as well use it. – Satanicpuppy May 10 '17 at 12:39

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.