2

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 homes table.

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.

  • Is the relationship between a "resident" user and a home many-to-many or 1-to-many? (and the same question goes for the relationship between a "neigbourhood" user and a neighbourhood.) Please clarify. – ypercubeᵀᴹ May 26 '16 at 17:52
  • It would be one-to-many for both cases. Users belong to one home and neighborhood admins manage one neighborhood. – Vic May 26 '16 at 17:56
  • So a home can have 1 or many users and a neighbourhood is managed by 1 or many neighborhood admins, right? – ypercubeᵀᴹ May 26 '16 at 17:59
  • That is correct. – Vic May 26 '16 at 18:00
  • Shouldn't users and neighborhood_admins be temporal? Thinks tend to change over time. – Serg May 27 '16 at 7:02
0

In most RDBMSs you'll have the ability to set-up column conditionals, which are useful for cases like this. Say your user types are 1, 2 and 3 (res, admin, super respectively). You can set users this way:

CREATE TABLE users (
  id serial NOT NULL PRIMARY KEY,
  name varchar(100) NOT NULL,
  user_type_id int NOT NULL REFERENCES user_types,
  home_id int NULL REFERENCES homes,
  -- a resident must have a home, a non-resident cannot have a home
  CHECK ((user_type_id = 1 AND home_id IS NOT NULL) OR
         (user_type_id <> 1 AND home_id IS NULL))
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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