Efficiently finding the ultimate parent in multi-level one-to-many tables Let's say I have a database with multi-level one-to-many tables (a contrived example):

CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

CREATE TABLE properties (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id)
);

CREATE TABLE rooms (
  id SERIAL PRIMARY KEY,
  property_id INTEGER NOT NULL REFERENCES properties(id)
)

CREATE TABLE rates (
  id SERIAL PRIMARY KEY,
  price INTEGER NOT NULL,
  room_id INTEGER NOT NULL REFERENCES rooms(id)
)


Let's say a user wants to update the price in rates table but I want to make sure that he is allowed to do so - i.e. the rate belongs to a room that itself belongs to a property that the user owns. 

What would be the best/efficient way to do so? Is there a way to add a "deduced" user_id column to the rates table that is automatically populated as the rows are added to the rates table so later I can use it in a query like:

UPDATE rates SET price=100 WHERE (id=30) AND (user_id=1)`
I simplified a bit by allowing only one price for a given room in the rates table.

CREATE TABLE users (
  user_id INTEGER NOT NULL
, CONSTRAINT pk_users PRIMARY KEY (user_id)
);

CREATE TABLE properties (
  property_id INTEGER NOT NULL
, user_id     INTEGER NOT NULL
, CONSTRAINT pk_prop PRIMARY KEY (property_id)
, CONSTRAINT fk1_prop FOREIGN KEY (user_id)
                 REFERENCES users (user_id)
);

CREATE TABLE rooms (
  room_id     INTEGER NOT NULL
, property_id INTEGER NOT NULL
, CONSTRAINT pk_rooms PRIMARY KEY (room_id)
, CONSTRAINT fk1_rooms FOREIGN KEY (property_id)
             REFERENCES properties (property_id)
);

CREATE TABLE rates (
  room_id INTEGER NOT NULL
, price   INTEGER NOT NULL
, CONSTRAINT pk_rates PRIMARY KEY (room_id)
, CONSTRAINT fk1_rates FOREIGN KEY (room_id)
                  REFERENCES rooms (room_id)
);


Some data to test

INSERT INTO users (user_id)
VALUES (1), (2), (3) ;

INSERT INTO properties (property_id, user_id)
VALUES
(11, 1), (12, 1), (13, 1),
(21, 2), (22, 2),
(31, 3) ;

INSERT INTO rooms (room_id, property_id)
VALUES
(1101,11), (1102,11),
(1201,12), (1202,12),
(1301,13), (1302,13),
(2101,21), (2102,21),
(2201,22), (2202,22), (2203,22),
(3101,31), (3102,31), (3103,31) ;

INSERT INTO rates (room_id, price)
VALUES
(1101,110), (1102,110),
(1201,120), (1202,120),
(1301,130), (1302,130),
(2101,210), (2102,210),
(2201,220), (2202,220), (2203,220),
(3101,310), (3102,310), (3103,310) ;


Update price for room_id = 2102 to 100, if and only if it belongs to user_id = 2.

UPDATE rates      AS t SET price = 100
  FROM rooms      AS r
  JOIN properties AS p ON p.property_id = r.property_id
  JOIN users      AS u ON u.user_id     = p.user_id
 WHERE t.room_id = r.room_id
   AND r.room_id = 2102
   AND u.user_id = 2
;






Note that all joins are on PKs, so this is fast. Also there are no multiple trips to the app and back.

Try it on dbfiddle or visualise the explain!