Re: ID field as logical address

In message <GT8Ul.1457$Cc1.718_at_nwrddc01.gnilink.net>, Walter Mitty <wamitty_at_verizon.net> writes

>> 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.
>
>What would the data structure look like if it were fully normalized?

Essentially removing the superfluous ID field would normally be a step towards it. This assumes that the fields listed in your example are sufficient to form a key. If the database permits multiple people with the same first and second names and the same phone number then you have a problem. I can imagine scenarios where that situation might exist.

Adding an ID field makes the problem appear to go away, but in fact it just moves it. You may have William Gates II and William Gates III living at the same address and so sharing a phone number. Adding an ID field does at least allow the database to record the fact that two different people with the same name and phone number exist. It doesn't solve the second part of the problem, which is how to ensure that the database can reliably distinguish data on these two people.

Adding an ID field is only useful if you can reliably link the ID to the real person. The traditional way to do this is to tell each person the ID that you have created for them and insist that they remember it for you. This is how SSNs and payroll numbers work. This system is not foolproof.

-- 
Bernard Peek