[dba-SQLServer] Update Foreign Keys

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
>