Re: [fw-db] Zend_Db_Table Issue

Would it not work if you made judge_id NULL instead of -1?

On 24/02/2011 16:52, David Mintz wrote:
> On Mon, Feb 21, 2011 at 6:44 PM, Bill Karwin<bill@karwin.com> wrote:
>
>> 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!
>