An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee.
One employee can only be a part of one organization and if he is an employee then he can't be a freelancer. Whereas a freelancer can be connected to multiple organization and he also can't be an employee.
In short, Freelancer and Employee both are users. Freelancer is connected to Organization via many to many relationship. Employee is connected to Organization via one to many relationship.
What I have thought:
user{
id,
name,
other_fields,
}
organization{
id,
other_fields
}
freelancer{
user_id,
organization_id,
Primary_Key(user_id, organization_id)
}
employee{
user_id,
organization_id,
Primary_key(user_id),
}
There is one catch here, though. The constraint of being either a freelancer or an employee but not both has to be implemented at the application level.
- What are the pros and cons of above schema?
- What are other possible alternatives to design schema?
- What if there are more category of users? Current schema requires addition of new table if a new role of user s created. Is it ok or bad?
- Database maintenance is also a concern that making a schema which should not make maintenance a problem
Best Answer
The major con that I see is that
freelancer
andemployee
have the same fields, but are different tables. The difference is whatprimary_key
is composed of, but I wonder if you could normalize this asFor
primary_key
, you might need some application logic to create a string based onuser_id
andemployee_type
, and optionally include tha value oforganization_id
if the employee is a freelancer.You could even go a step further and have a table
employee_type
:and then reference that ID in
employee.employee_type
. This would deal with your problem of adding new tables every time there is a new type of user.Also, Have you considered merging
employee
withuser
? This would simplify your schema even more, but you would lose the ability to have auser_id
associated with multiple employees, which I think is important for this case.