Database design with different user roles connected by different relationships to one organizaiton table
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.