Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
I also thought of the same schema which you have mentioned initially. It seems it is the best way. What I am thinking is make Primary_key(user_id, organization_id, employee_type_id). I have to keep a check in application code if an user is already fulltimeemployee then he cannot be connected to any other company. –  codecool Oct 23 '12 at 10:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.