Let's say I have a database with multi-level tables from one to many (an artificial 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
rates table but I want to make sure you are allowed to do it, that is, the
rate belongs to a
room that same belongs to a
property that the user owns.
What would be the best / efficient way to do it? Is there any way to add a "deducted"
user_id column to the
rates table that is automatically filled in as the rows are added to the table
rates table so you can then use it in a query like:
UPDATE rates SET price=100 WHERE (id=30) AND (user_id=1)`