jwcolby
jwcolby at colbyconsulting.com
Fri Jul 15 07:42:16 CDT 2011
Arthur, FKs are updated all of the time. Since I only use autonumber PKs the FKs do not form part of the PK of the child table and therefore I do not need cascade updates. As for the history, the history belongs to the machine. If the machine moves, then the history moves as well. If the history needs other "location" data to be valid then it needed to have location FKs in that history data, which it apparently does not. If it truly needs that location data then add a new location field to the immediate child of the machine, update with the location where the data was accumulated and off you go. It seems to me that location is probably not what is actually being tracked however but rather instruments (taking the measurements) and you probably already have an instrument id in the measurements. If not you have bigger problems than location data. If you copy the machine and create a new record then you have the same machine in two different locations. Clearly a problem in this universe. You will now be working around a problem that you created. I understand that you like the PIT architecture stuff but unless the system is designed from the ground up to use that it seems unwise to me to be applying it piecemeal. The machine moved. The location ID gets updated. FK updates happen all the time in my world. Think people / cars and so forth. John W. Colby www.ColbyConsulting.com On 7/15/2011 8:14 AM, Arthur Fuller wrote: > I'd like to poll the readership to ask, "Do you permit FKs to be updated, > and if so under what circumstances?" > > I'm asking because a client and I are discussing a situation where this has > arisen: > > A Client may have several locations. > A Location has zero or more machines installed. > A Machine has related data in at least one table (Assessments and optionally > Measurements). > >> From time to time the Client may want to move a Machine from one Location to > another. > > The client suggested that I simply replace the FK LocationID on the Machine > record with the LocationID of the new Location. I pointed out that there are > two possible results to this operation: > > a) do a Cascade Update on the tables under Machines. This approach "destroys > history", so to speak, in that the data really no longer applies to the > relocated Machine. The Assessments and Measurements no longer apply to the > new Location. > b) Orphan the Assessments and Measurements. This is unacceptable, IMO. > > So I suggested that rather than change the Machine's LocationID, we instead > copy the Machine data (only) to a new row, assigning it the new LocationID > and leaving the old row intact, along with its Assessments and Measurements > > In a somewhat related topic, "Do you permit Cascase DELETEs, and if so, > under what circumstances?" I'll respond to that one first. The only time I > permit this is when using staging tables. For example, a wizard may accept > new data into several tables. The last step in the wizard is equivalent to > "COMMIT" -- it writes the accumulated data to the "real" tables. There is > also a "Cancel" button, which if pressed causes a Cascade Delete across all > the tables involved. > > Arthur > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >