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:
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".
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.