> I coded the cascading functionality in Zend_Db_Table. I don't recommend
> using it for any RDBMS, whether the RDBMS supports DRI or not.
>
> It is not possible to implement cascading referential integrity in a safe
> manner in any application-space code. It's not a matter of the PHP code in
> Zend_Db_Table being good enough, it's simply not a task that can work
> outside the database engine. You need to enforce consistency through the
> RDBMS engine.
>
If that's so, I would love to pick your brains about the following.
(Coincidentally, the application is for tracking things that take place in a
judicial system, as Rafael's seems to be.) My apologies if this is getting
OT.
I have a table called events: things that happen in and around court that
involve language interpreters. Every event has attributes like language_id,
date, etc, and has has a related judge entity in ALMOST but not quite every
instance! So there's a table called judges. I am using DRI where I can in my
events table, e.g:
CONSTRAINT `events_ibfk_2` FOREIGN KEY (`event_type_id`) REFERENCES
`event_types` (`id`),
CONSTRAINT `events_ibfk_5` FOREIGN KEY (`location_id`) REFERENCES
`locations` (`id`),
CONSTRAINT `events_ibfk_6` FOREIGN KEY (`language_id`) REFERENCES
`languages` (`id`)
but I can't do something like
CONSTRAINT `events_ibfk_7` FOREIGN KEY (`judge_id`) REFERENCES `judges`
(`id`)
because there are some exceptional cases, as noted. My solution is to forgo
DRI and try to enforce referential integrity at the application level. In
`events`, my judge_id is signed, and a negative value like -1 means 'not
applicable' (to the application) and so forth. At event insertion/update
time I will go to great pains to enforce RI, e.g., by locking tables and
making sure nobody has deleted judge so-and-so out from under me.
What solution would you suggest?
Thanks!
--
David Mintz
http://davidmintz.org/
It ain't over:
http://www.healthcare-now.org/
没有评论:
发表评论