SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Cross Database RI Issues

I had an issue with a user recently. Actually it has been going on over a few weeks, due to both of us being busy and slow to respond to the other, but basically a user had two accounts. They had lost the password, changed jobs, and registered a new account rather than changing the email of the old one. As a result their history (QOD, posts, etc.) were in two separate places. They realized this and asked me to change the name on the old account. I did that, but I changed the email on the old account to move to the new one, and got things tangled up.
First, a little background. In order to be more "service oriented", we actually use 3 databases. We have one for sign on, one for forums, one for everything else. However developers built this, and they didn't think a few things through, like cross DB RI. So we have not only some linkage IDs, but also emails stored in a few places.
As a result, when you change your email, or when I do it on the web side, it doesn't change the email in the forums db or the general SSC db. For the SSC db they must be referencing the IDs since those stay in lockstep with the signon ID, but in the forum DB, since we don't create a user there until you post, they must reference by email. So when I changed things and they were looking for your profile (I assume you mean Linked In contact there), it was pulling the "first" one, which was the old one.
They also aren't changing the name on the posts, which is a great non-RI implementation by InstantASP. Your name is stored in the post.  So I had to manually update a number of posts with the new name as well as fix some IDs in new posts to match the old one.
I also had to fix a few other places where things were mis-linked when they were changed somewhere else.
Keeping summarized data in sync can be hard, but when you are dealing with cross linked databases, and you don’t have constraints or strict interfaces that ensure updates propagate throughout all places, it’s easy to get out of sync.
I think I’ve fixed this issue, and saved the script for future use, but we’ll know when I hear back from the user.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.