birderic birderic - 1 year ago 74
MySQL Question

How do I handle multiple types of users?

In my application users can be one of three types: teens, individuals, or members of an organization. In my users table, I have a boolean field teen (not null) and a foreign key organization_id (null).

If teen is true and organization_id is null, the user is a teen. Teens can't belong to an organization.

If teen is false and organization_id is null, the user is an individual.

If teen is false and organization_id is not null the the users is a member of an organization.

There has to be a better way of doing this. I don't like that the case exists where teen is true and organization_id is not null.

Would it be better to add a new table, organizations_users, and remove the organization_id foreign key from the users table? The downside I see to that is that it will take me longer to determine whether or not a user is an individual or an organization, and fetch the data if appropriate.

Is there a more elegant way of handling this?

Answer Source

It would simplify your code if you had a role column that had either teen, individual, or member. You'd probably normalize these roles to their own table, and use a foreign key:

1      Teen
2      Individual
3      Member of an Organization
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download