[dba-SQLServer] Update Foreign Keys

hmmmm. Where was that concept used before?  ;)




On Fri, Jul 15, 2011 at 6:57 AM, Francisco Tapia <fhtapia at gmail.com> wrote:

> 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
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>