I have a one to many relation with Postgres. The primary key entity LUT is dependent on its child entities. I am looking for a way to cascade changes in the foreign key entities to the primary entity and update its LUT.
Since there can be many changes a solution with some kind of debounce will be better.
Example structure: Table of primary entities:
Buildings
| id | address | name | updated_at |
|---|---|---|---|
| 1 | xxxxx | aaa | 2022-08-26 09:23:57.768 00 |
| 2 | qqqqq | bbb | 2022-08-27 10:20:57.768 00 |
| 3 | wwwww | ccc | 2022-08-27 09:20:57.768 00 |
Table of child entities - building_id is foreign to the buildings table:
Floors
| id | building_id | rooms_number | updated_at |
|---|---|---|---|
| 1 | 1 | 5 | 2022-08-26 08:24:54.668 00 |
| 2 | 2 | 4 | 2022-07-25 11:25:37.712 00 |
| 3 | 1 | 3 | 2022-08-23 07:07:21.432 00 |
| 4 | 1 | 5 | 2022-08-29 09:19:58.765 00 |
I need that every time a floor is being updated, the updated_at of its building (foreign key entity) will also be updated as well i.e. its updated_at will update as well.
Trying to achieve it with DB functionality and not in the code. one of the issues is that many floors can be updated simultaneously. This is why I asked about debouncing for not having many updates of the building.
Thoughts?
CodePudding user response：
Make sure that your foreign key relationships have ON UPDATE CASCADE specified, and the foreign key will automatically update to match the primary key.
CodePudding user response：
You basically have 2 options:
Keep
updated_atcolumn in building and develop triggers to synchronize theupdate_atcolumns betweenbuildingandfloortables. This is the process you currently have.
Advantage:Updated_atcolumn for building easily accessible through simple direct query.
Disadvantage: Requires additional code, perhaps quite complicated, to select the correctupdated_atfrom floor table. Consider, initial floor entry contained incorrect building_id. What does the trigger need to do when corrected. How about a delete from floor; might it contain the current buildingupdated_at? .Do not actually store the
updated_atcolumn on the building table. Instead create a view that derives the latestupdated_atcolumn fromfloorstable.
Advantage: No further action (code) required on DML operations.Updated_atcolumn forbuildingsalways shows latest floor updated value (if any).
Disadvantage: Cannot issue DML directly againstbuildingas it is a view (keeping same name) as the underlying table has a different name. This would require Instead of triggers to update actual table or each developer to have knowledge of the underlying table.
IMHO option 2 is by far the superior. It follows the basic relational model dictate of do not store what is derivable. Is it without issues? Clearly not, you will probably want to build instead of triggers. And there could be others. See here a short example containing each option.