Re: ID field as logical address

In message <GT8Ul.1457$Cc1.718@xxxxxxxxxxxxxxxxxxxx>, Walter Mitty <wamitty@xxxxxxxxxxx> 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
.

Relevant Pages

  • Re: CBO influences
    ... > databases from 8i to 9i, and so have several copies of this database ... > generating an access plan that featured several hash joins, ... Initialization parms idendtical, data structure and volume ...
    (comp.databases.oracle.server)
  • Re: Database Design
    ... duty vehicles, rental equipment, parts used for maintenance. ... For instance one employee may assigned to a vehicle during their shift. ... When you get to convert these entities into tables you can create a Physical Data Structure ERD. ... Each of those entities is likely to become a table in your database and everything you have on your yellow pad is likely to become a field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Process to modify multiple queries simultaneously
    ... Access is a relational database product. ... Disclaimer: This author may have received products and services mentioned ... you lost me on data structure. ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.queries)
  • Re: Design issues
    ... Thanks for your suggestions on naming the fields. ... I wanted some help and advise on my existing database table structure and ... normalize your data structure, I'd question how a ProjectNo or a PONo ...
    (microsoft.public.access.tablesdbdesign)
  • Re: ID field as logical address
    ... auto-generated key called ID in every table, and declaring the ID as the ... It's in a separate entity with only one attribute. ... and it's caused by failure to fully normalise the data structure. ...
    (comp.databases.theory)
Loading