>>> insert a new address, and update the users table to the new address_id >> >> Which changes the user's "primary key". My point was that having the address >> id be part of the primary key is wrong. > > As I said, you don't *have* to do it that way. I was just giving an > example. You could just as easily grab the address id, insert that into > an archive table with a date stamp and then just update the address > itself. Thus *not* changing the "Primary Key". Thus making it more difficult to deal with historical data, and also reducing the "address id" in the "user" row to nothing more than an additional auto-generated number referencing address data that might as well just be put into the user row, because that would be no less normalized anyway than this single address row whose contents keep changing to represent different addresses over time. Using the address id as part of the user primary key forces this choice between a user primary key which changes, or funkiness in tracking addresses. Whether you change the id stored in the user row, or whether you update the address row, you are still constructing a primary key from data that is expected to change. Even the justification for it is thin. The address is certainly one good way to figure out which of two users with the same name is being referred to, but that doesn't make it part of a reasonable primary key. Phone numbers, credit card used, order history--all of these could help discriminate between users and not one of them has any place in the users' primary key. -- Scott Ribe scott_ribe@xxxxxxxxxxxxxxx http://www.killerbytes.com/ (303) 722-0567 voice
