I have the following tables:
users: - id - name - user_type_id (references id on user_types) neighborhoods: - id - name homes: - id - house_number - neighborhood_id (references id on neighborhoods) user_types: - id - label
The available user_types are resident, neighborhood_admin, super_admin. Each type of user has access to different parts of the system. Only residents must have a relationship with the
I was thinking adding a pivot table like:
residents: - user_id (references id on users) - home_id (references id on home)
but I'm not sure if this approach is correct. I've used pivot tables for many-to-many relationships only. Also, using code I would have to check the user_type before assigning a home, but I could just as easily assign a home to a super_admin if I'm not careful.
How can I make the database enforce this restriction so that it doesn't have to be done through code?
UPDATE: I forgot to add, neighborhood_admins must be assigned to a neighborhood. I could add the following table:
neighborhood_admins: - user_id (references id on users) - neighborhood_id (references id on neighborhoods)
But this creates a problem. The
users table now has two different relationships to the
neighborhoods table; if the user is a neighborhood_admin, then the relationship is obtained from the pivot table
neighborhood_admin but if the user is a resident, then we must first obtain the
home relationship and then the home's neighborhood.
I'm thinking I might as well just add a
neighborhood_id column to the
users table and be done with it, but I don't like those relationship triangles. Also, that column would be null for super_admins so it couldn't be a foreign key.