On 2/16/23 10:28, Dominique Devienne wrote:
On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
You have two tables with list in their name, so are rows deleted from
both. Just to be clear enity_list should actually be entity_list?
Also how are entity and enity_list related?
I have to confess that your questions surprise me a bit.
I thought the model was pretty clear from the SQL.
We have two entities, Foo (my entity table), and another Bar (my
entity_list table),
with Bar reference 0 or more Foos, recording which ones it references in
an association table.
Are the values for the name field in entity and enity(entity)_list the
same for a given entity?
The name of Foo and Bar are completely independent.
> transfering the old entity name to it thanks to an UPDATE on
> entity_name, thus preserving the old name.
How?
Again how would you determine where the action started?
Deleting a Foo cascades to the _member assoc-table.
If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member.
I no longer care where the action started.
What happens if an entity_list value is deleted?
Are you going to replicate the above for it to?
If so something like what Brad White suggested would seem to simpler.
Or, create a history table where rows deleted from entity_list_member
are moved to.
> Then an INSERT trigger on entity could locate any (indexed)
"stashed"
> entity names in that extra non-FK column in entity_list_member,
How would it locate it if the name that defined the
FK(entity(name)) was NULL?
In the extra non-FK column I mentioned explicitly, in the _member
assoc-table.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
