Database design with different user roles connected by different relationships to one organizaiton table

Question

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.

1. What are the pros and cons of above schema?
2. What are other possible alternatives to design schema?
3. 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?
4. Database maintenance is also a concern that making a schema which should not make maintenance a problem

Answer 1

What are the pros and cons of above schema?

The major con that I see is that freelancer and employee have the same fields, but are different tables. The difference is what primary_key is composed of, but I wonder if you could normalize this as

emplpoyee
{
    user_id,
    employee_type, (could be "FREELANCER" or "FULLTIMEEMPLOYEE")
    organization_id,
    Primary_key
}

For primary_key, you might need some application logic to create a string based on user_id and employee_type, and optionally include tha value of organization_id if the employee is a freelancer.

You could even go a step further and have a table employee_type:

employee_type
{
    id,
    description
}

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 with user? This would simplify your schema even more, but you would lose the ability to have a user_id associated with multiple employees, which I think is important for this case.