> Shenanigans! > > That problem occurs regardless of whether or not you use surrogate keys. > You have exceeded the scope of the example. Yes the "problem occurs" in that this is something that needs to be tracked, but the suggested schema presents peculiar problems for what otherwise is a pretty simple thing. Reasonable solution: every address is kept, so each address is a row in an address table, with its own id. Shipment rows simply record the id of the address current at the time of shipment. The user row simply records the id of the most-recent address as the now-current one. That's rational. But if you use the address id as part of the user's primary key, well now you have a primary key that keeps changing as users move. On the other hand, if you don't change the address key but the contents of the address row itself, in order to preserve this bizarre notion of primary key, you have to keep a copy somewhere of the same address with a different id in order to use that for shipments. >> You would update the address, the address id wouldn't change. If you >> want to keep track of old addresses you would keep an archive table >> associated with the user.id. > > But what about historical data that referenced the address? If you move > today, I still want to know where I shipped last week's orders. -- Scott Ribe scott_ribe@xxxxxxxxxxxxxxx http://www.killerbytes.com/ (303) 722-0567 voice
