Re: Multiple Foreign keys from 1 child to 1 parent discussion

[Peter Brawley]

> aside from the locking that could occur as the heavily accessed user table is checked.

The declared FKs pointing at user.id make sense if each is allowed to point at a different user.id, otherwise not.

Querying the bill table imposes locks only if the query is For Update.

For Inserts Updates and Deletes, referential integrity can be enforced by FKs or by application code. It'd be very hard, mebbe impossible, to write code whose locks are faster than those enforced by InnoDB FKs.

> the bill table is not a direct child of user, encounter is a direct child of user

No sure what you mean. If a table has N FKs pointing at one key in another table, it instantiates N child-parent relationships to that table.

> there is a foreign key from bill to encounter.

Do the biz rules permit the bill->encounter->user.id chain to point to a different user.id than bill.user_id?

So you're really asking whether any of those references are allowed by the schema's business rules to point at different user_ids.



Edited 1 time(s). Last edit at 06/06/2016 04:53PM by Peter Brawley.