Francisco Tapia
fhtapia at gmail.com
Fri Jul 15 08:57:07 CDT 2011
What a great concept! Sent from my mobile On Jul 15, 2011, at 6:47 AM, David McAfee <davidmcafee at gmail.com> wrote: > Create a junction table for installs. > > tblInstalls > InstalledID (PK, INT) > LocationID (FK, int) > MachineID (fk, int) > Installdate > EntryDate > Entryuserid > > Every record is an insertion. > You never have to overwrite data. > Built in history. > > A simple view/sproc using Max() can show the latest location location for a > given machine or machines at a given location. > > HTH, > David > > Sent from my Droid phone. > On Jul 15, 2011 5:15 AM, "Arthur Fuller" <fuller.artful at gmail.com> 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 >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >