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?
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