How do I handle multiple types of users?

Question

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 1

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:

Roles
1      Teen
2      Individual
3      Member of an Organization

Answer 2

Use 2 tables. Table 1 is a User table which contains user information. It also contains a foreign key to the ID of the next table.

Table 2 is UserType. The id is an identity. This contains'Teen', 'Individual' and 'Organization'.

To do a lookup, simply do a join.

Answer 3

Seems to me that you have cornered yourself with this design. In general, it is better to keep your abstractions (models) in sync with the real world, as opposed to some very special case. For example, a teen is a person between ages 13 and 19 (depends on definition). A teen can be a member of a swimming club, which is an organization. A person of any age may or may not be a member of an organization. I would suggest the following: