>
> On Feb 24, 2011, at 8:52 AM, David Mintz wrote:
>
> 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?
>>
>
> Right -- you can't use -1 if you want DRI, because there is no judge whose
> primary key value is -1.
>
> You should make the judge_id column nullable, and use NULL to mean 'not
> applicable' or 'not assigned'. Then you can declare a foreign key
> constraint to the judges table. A NULL in the foreign key column doesn't
> have to match any row in the referenced table.
>
> You can read more about using NULL in my book "SQL Antipatterns" (
> http://www.pragprog.com/titles/bksqla/), in the chapter "Fear of the
> Unknown."
>
> That said, I think it's weird to delete a row from the judges table
> regardless. Does that mean when a judge retires, you delete his row, and
> the delete cascades to any events that he or she participated in? Doesn't
> that mean destroying a lot of useful historical data? Not to mention
> erasing the legacy of a respectable career in public service?
>
> I would think that in an app like this, you would never actually DELETE a
> row from judges. Instead, you set a `status` attribute for that judge, to
> mark him or her as 'retired' or 'voted out of office' or something. Or else
> set a date field for `end_active_term`. Then code your application logic to
> create new events only for active judges, e.g. those whose `status` is
> 'active', or whose `end_active_term` is either NULL or in the future.
>
> If you don't delete, then you don't need to cascade deletes. :-)
>
Thanks for the helpful observations. The trouble with NULL is that it's too
vague. I have cases where I need something that means "not applicable,"
"unknown," or most often, "we don't care" because the proceeding took place
before the generic duty magistrate whose actual identity really isn't
important, and users don't want to be forced to identify her/him by name.
And you're quite right: deletion will be a rarity in my judges table as well
as most others. But it could happen that one user half-assedly puts in an
inexact duplicate of another row, or otherwise made some mistake, and
someone else comes along and corrects the mistake by deleting it before the
row acquires children, but too late for its id not to appear as a value in
some SELECT menu.
And I do have a de facto boolean 'active' column in this table (it's MySQL)
where 1 means active and 0 means inactive -- retired, deceased, resigned,
etc. Indeed when someone who is known to the app changes hats -- and it
happens -- say from law clerk to prosecutor, prosecutor to judge, they will
not be deleted but made inactive and then they'll be reborn in another row.
I call it reincarnation. Otherwise, yeah, you would trash or falsify your
historical data.
It's really reassuring when someone who knows what he's talking about
suggests you do what you're already doing, so thanks!
Getting back to the first point about NULL, I suppose I could make the data
model even more complicated but I'd of course prefer to keep it as simple as
possible, no more complex than necessary. I'm of course open to more
suggestions, but I think it may be that it's just the challenge of modeling
uncooperative reality in a set of rectangular tables.
--
David Mintz
http://davidmintz.org/
It ain't over:
http://www.healthcare-now.org/
没有评论:
发表评论