Re: ID field as logical address

In message <xWQTl.1481$9L2.793_at_nwrddc02.gnilink.net>, Walter Mitty <wamitty_at_verizon.net> writes

>Let's say that the design has followed the practice of including an
>auto-generated key called ID in every table, and declaring the ID as the
>PRIMARY KEY. Let's further assume a practice that says that you never
>update the ID field of any tuple, once it's been assigned. Now let's say
>you have a table that records values that look like (ID, value). In an
>actual case, value would probably ityself be a tuple, so our tagged tuples
>might look like, for example, (ID, LastName, FirstName, PhoneNumber).
>
>Now, if we update the LastName, the FirstName, and the Phone number of an
>entry, it's still the same entry, because the ID field retains the same
>value.

This appears to be an update anomaly but it's not as simple as that. If we think of the logical data structure the ID isn't in the same entity as the data we want to manage. It's in a separate entity with only one attribute. There is supposed to be a 1:1 relationship between the ID entity and the data entity, but this is not usually maintained by the database. The cardinality of that relationship is managed either programmatically or by external business processes.

Updating the data without making a simultaneous update to the ID field is perfectly permissible within the relational model. They are after all in separate entities with only a tenuous connection. It's an update anomaly and it's caused by failure to fully normalise the data structure.

-- 
Bernard Peek