Re: Abstract identifiers, logical pointers, or foreign keys considered not enough

From: Costin Cozianu <c_cozianu_at_hotmail.com>
Date: Sat, 11 Oct 2003 19:42:25 -0700
Message-ID: <bmaeu8$jurkh$1@ID-152540.news.uni-berlin.de>

Lauri Pietarinen wrote:

> Costin Cozianu <c_cozianu_at_hotmail.com> wrote in message news:<bm6o4c$j8uu5$1_at_ID-152540.news.uni-berlin.de>...
> 

>>You can group several unrelated things together without giving them an
>>id. Like apples and oranges that we put in a basket, we don't need to
>>give them apple_or_orange_id.
>>
>>Let's think of CUSTOMER, who have a default payment mechanism on record,
>>where the PAYMENT_MECHANISM can be
>>
>> Credit Card, (CardtyTYPE, Number )
>>
>>OR
>>
>> Electronic Withdrawal From Account: (Bank Number, AccountNumber)
>>
>>You really don't get the business user to care that we create an extra
>>ID for these two entities, they are already identifiable through their data.
>>
>>if we create a PAYMENT_MECHANISM_ID in current databases is just becuase
>>the DBMS vendor doesn't allow us to say:
>>
>> type PaymentMechanism = CreditCard | ElectronicWithdrawalFromAccount
>>
>>So database developers will create a PAYMENT_MECHANISM table and invent
>>PAYMENT_MECHANISM_ID, and even then it is either impossible or extremely
>>awkward to specify the needed integrity constraint.
>>
>>But if the end user sees
>>
>> PAYMENT_MECHANISM_ID: 1020303030303,
>>
>>where he expects a credit card number or account details, he will say
>>"What ???"
>>
>>Sometimes, they are forced to swallow such invention and make them part
>>of their business model, just because they have to use our software, but
>>not because a mathematical model of the business actually needs
>>surrogate keys.

> 
> 
> In my mind the rational for using surrogates in databases is that
> they insulate us from cascading changes in the case that we have to
> change the primary key.
> 

But this is a physical level concern. The table holding the foreign key, may just physically hold a pointer to the record from where it will get the (foreign key/candidate key)value. Then an "ON UPDATE CASCADE" will be just as easy as if there was nothing to cascade.

Plus the scenario you described is not quite kosher. In general you don;t allow users to update the logical identifier of an entity. For example he mistype his license number, so you want to update that information in all different tables.

But business wise this is not always allowable, what if, for example that license ended up in a different table, and was validated and produced business consequences ? Like it was printed on a legal document, or the guy just got a better insurance rate on the license number of his friend, after which he "corrected" it. So it is nto always that we want ON UPDATE CASCADE.

Regardless of the problem related to changing the identity of entities, in the case I presented, introducing a PAYMENT_ID is a strange way to plumb the inadequacy of a type system.

Costin Received on Sat Oct 11 2003 - 21:42:25 CDT