Merge Entities, Logging Changes



  • My boss wants me to give administrative users of our system the ability to merge two users.  Merging here pretty much just means that Person B will be deleted and all his things will be given to Person A.  I've got the actual merging figured out, but now I'm stuck thinking about how to best log everything that happens during a merge.

    Up to this point, logging changes hasn't been considered important.  But when I asked what should happen if an admin-type accidentally merges two people who didn't need to be merged, my boss agreed that the changes that occur during a merge should be logged.  I'm not trying to make it so the admin can just hit an undo button or anything like that, but I at least want to be able to show the admin what was changed or deleted when they did the merge.  That way, they can go back and manually put everything right.  Hopefully, the experience would be traumatic enough to make them think twice before doing something like that again ;-) .

    So, how do you all log changes?  And how should I log changes so that an admin could pull up a list of merges, and upon selecting a merge, see everything that was changed in relation to that merge?
     



  • Uh, what kinda system is this? Are we talking about an Operating system, a COTS CMS, an in-house-developed workflow management system...

    One would kinda need to know the specifics before you could appropriately address questions like "how do you log all changes"



  • @hk0 said:

    Uh, what kinda system is this? Are we talking about an Operating
    system, a COTS CMS, an in-house-developed workflow management system...

    One would kinda need to know the specifics before you could appropriately address questions like "how do you log all changes"

     

    So sorry.  I should have been more specific.  Our system is an in-house-developed system that tracks courses, students, enrollments, payments for enrollments, etc.  All information about these things is stored in a relational database.  My question is about how to store information about two students being merged so that an admin could come back and manually undo a merge between two students should the need arise.  I've thought of a couple of different routes I could go, but I have no idea how stupid my ideas are.  I was hoping to get some ideas from you all.

    When Person B is merged with Person A, everything (enrollments, payments for those enrollments, etc.) that Person B had will be given to Person A.  More specifically, If Person B has an enrollment in Class 1, then that enrollment will be transfered to Person A (the PersonID field in the enrollment record will be changed to point to Person A instead of Person B).  If Person A already has an enrollment in Class 1, then Person B's enrollment will just be deleted.  After everything owned or related to Person B has been given to Person A or deleted, Person B will be deleted

    I've already thought through and coded the actual merging process; I've looked through the database and figured out all the entities that are related to People and how to transfer them from one person to another.  What I am stuck on is how to log everything that occurs in the process of merging two people.

    I figure I'd need some kind of Merges table in which I can record that Person B was merged with Person A.  After that, I'm not too sure what to do.  I could set up a <Entity>MergeLog table for each entity (enrollment, payment, etc.) involved, and then insert into that log table a record detailing each <Entity> record that gets deleted or changed during a merge, along with a MergeID, so that I know which Merge changed which records.  That seems simple and straight forward to me, but anytime I come up with a solution that requires the addition of several new tables to the database, I figure I should check out TDWTF first :-) .

    How stupid am I being here?  Is there something obvious that I am overlooking?  I appreciate any help/advice you all care to give me.



  • If the "recovery" process is to be manual and not processed by the system, why bother with putting the info in the db to begin with?

     

    I'd just go with  verbose text file

    This deleted from Students:

    StudentID | Name | Age | .......
    ------------------------------------------------

    006          | Bob   | 21    | ...........

    etc. etc.

     

    If this is to be a rare event that the system need never again concern itself with, why even complicate it past the basics?
     



  • @UncleMidriff said:

    Is there something obvious that I am overlooking?

    Yeah: dump out all the data on both students to the log. Simple, direct, easy to manually reconstruct (if somewhat laborious), and nothing can go wrong. Anything more complicated just introduces more failure cases for no apparent benefit.
     



  • out of curiosity.  Why would you ever want to merge two people?



  • @tster said:

    out of curiosity.  Why would you ever want to merge two people?

    The administrators want to be able to merge two people in the event that the same person registers with the system twice with different usernames.  We've got some people, it seems, who would rather go through the registration process again than use the password recovery process.  This means that we'll get users "UserBob" and "UserBob2309" who both represent the same person.  When the administrators figure out that something like that is going on, they want to be able to merge the two users.  I'm not entirely convinced that it's such a big problem, or at least, a problem that better login recovery tools couldn't take care of, but they've been asking for it for awhile now, so I figured I'd let them have it (in reality, the boss-man told me to do it, so I'ma do it :-) ).

    Thanks to all for the advice; I knew I was probably making it more complicated than it needed to be.  This is what I have settled on:

    When two users are merged, I create a record in the Merges table indicating that Person B has been merged with Person A.  Then I spit out to a log table everything of interest owned or related to Person A, and the same for Person B.  Then I proceed to merge the two.  That's it.  Now the administrators will have a record of the everything of relevance to the two people pre-merge, so if they need to manually revert back to that, they'll be able to.  Again, thanks for encouraging me to keep it simple.



  • wow.  you either have a terrible password recovery system, a super easy registration, or some stupid users.  (actually you probably have stupid uses no matter of the first two :) )



  • @UncleMidriff said:

    We've got some people, it seems, who would rather go through the registration process again than use the password recovery process.  This means that we'll get users "UserBob" and "UserBob2309" who both represent the same person. 

    Why are you working on this when your system is allowing dupe registrations? Is it terribly complicated to assign them a student ID at the registrar/burser/what-have-you and require that for registration? Seems like that'd be easier to catch than just allowing anyone to register as often as they'd like. Forgot your password? Fine, what's your security answer, etc. Want to register? Need a student ID. Student ID exists already? On to the password recovery system... 



  • @tster said:

    wow.  you either have a terrible password recovery system, a super easy registration, or some stupid users.  (actually you probably have stupid uses no matter of the first two :) )

    I think it's about half #1, and half #3.  As it is now, the password recovery system is just that...it only allows for the recovery of passwords.  Thus, if you've forgotten your username, tough beans.  I'm going to change that though.



  • @sootzoo said:

    @UncleMidriff said:

    We've got some people, it seems, who would rather go through the registration process again than use the password recovery process.  This means that we'll get users "UserBob" and "UserBob2309" who both represent the same person. 

    Why are you working on this when your system is allowing dupe registrations? Is it terribly complicated to assign them a student ID at the registrar/burser/what-have-you and require that for registration? Seems like that'd be easier to catch than just allowing anyone to register as often as they'd like. Forgot your password? Fine, what's your security answer, etc. Want to register? Need a student ID. Student ID exists already? On to the password recovery system... 

    I'm afraid I can't control dupe registrations in the system anymore than worsethanfailure.com can control it.  Our system is open to the public, therefore we can't assume that our users will have some form of ID that we could reliably use to protect against dupe registrations.



  • Storing the merges in the database is only useful if you intend to undo them frequently and want to automate the process.  I'd simply store the data in text files, and probably keep a record in the database that a merge happened, who did the merge and when, with a reference back to the text file.



  • @bstorer said:

    Storing the merges in the database is only useful if you intend to undo them frequently and want to automate the process.  I'd simply store the data in text files, and probably keep a record in the database that a merge happened, who did the merge and when, with a reference back to the text file.

    I do like you say; I keep a record in the DB that Person B was merged into Person A on <Date> by <Administrator>.  Then I stored details about each person pre-merge in a log table.  It's not a complicated log table.  Each record in the log table just has a reference to a particular Merge, the type of information, e.g., "Enrollment Info", a description, i.e., "Course 1, Section A", and the person it belongs to.  So, it's pretty much just what I would write to a text file, but I'm sticking it in a log table since I didn't see any reason to store stuff in a text file when I've got a perfectly good database waiting for me to store data in it.  Is there a particular reason I shouldn't be using the DB and should use text files instead?



  • @UncleMidriff said:

    @bstorer said:

    Storing the merges in the database is only useful if you intend to undo them frequently and want to automate the process.  I'd simply store the data in text files, and probably keep a record in the database that a merge happened, who did the merge and when, with a reference back to the text file.

    I do like you say; I keep a record in the DB that Person B was merged into Person A on <Date> by <Administrator>.  Then I stored details about each person pre-merge in a log table.  It's not a complicated log table.  Each record in the log table just has a reference to a particular Merge, the type of information, e.g., "Enrollment Info", a description, i.e., "Course 1, Section A", and the person it belongs to.  So, it's pretty much just what I would write to a text file, but I'm sticking it in a log table since I didn't see any reason to store stuff in a text file when I've got a perfectly good database waiting for me to store data in it.  Is there a particular reason I shouldn't be using the DB and should use text files instead?

    The reason for the text file is KISS (Keep It Simple Stupid).

    If it was me i would first dump all the info about both users into a text file and then just dump a copy of each query, this ensures you atleast have a SQL paper trail you can follow  and all the information you might need when reversing the merge.

    Now you could dump that information into some sort of blob/text field into a merge table, but why fake a text file, when you can use a text file.



     



  • @stratos said:

    The reason for the text file is KISS (Keep It Simple Stupid).

    If it was me i would first dump all the info about both users into a text file and then just dump a copy of each query, this ensures you atleast have a SQL paper trail you can follow  and all the information you might need when reversing the merge.

    Now you could dump that information into some sort of blob/text field into a merge table, but why fake a text file, when you can use a text file.

    I agree that keeping things simple is best, but I'm not seeing how introducing another, separate data store that I have to link back to my current data store is simpler than just storing data in my current data store, i.e., the database.  Also, with a text file, I can't easily query it to find all merges before <date>, all merges performed by <AdministratorName>, all merges involving <PersonID>, all info regarding merge <MergeID>, etc.

    As for the SQL paper trail, I'm not sure I follow.  I'm using a stored procedure, not dynamically generating SQL for each merge, so the SQL is the same for every merge.  But like I said, I'm not sure if I understand exactly what you mean.



  • unless you automate the fixing of an improper merge then a text file is better.  It's much easier to a human to look at and work with a text file than with a database.


Log in to reply