[dba-SQLServer] Update Foreign Keys

I will tell you this arthur. To this day I do not let my clients (be
it my current employer nor my freelance stuff) dictate the
architecture of a database.  I will always work with my colleagues and
design what makes the most business sense.

I guess its different if the client is also a db developer, but
generally my clients are not thus why I can say, it like this.

Sent from my mobile

On Jul 15, 2011, at 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
>