Deleting completed procedures vs. TP

[mowgli]

Not sure how to do this yet, but floating it here to see if it's even acceptable. Right now, treatment plan procedures are retained in the database after deletion and status marked "D." A lot of extra TP procedures get created, whether in various TP's or just by using "schedule recall" or deleting appointments without deleting associated TP items attached to them. Not only does it clutter up the database with a lot of deletions, but I don't see how you can distinguish these from deletions of actual completed procedures, unless the provider entered some notes. How about just deleting the TP items out of the database altogether, and only retaining deleted complete procedures? The setting of procstatus to "D" could be conditioned on whether the procstatus is "C", and the procedure deleted otherwise. Current code in Data Interface\Procedures.cs is below:

//set the procedure deleted-----------------------------------------------------------------------------------------
command="UPDATE procedurelog SET ProcStatus = "+POut.PInt((int)ProcStat.D)+", "
+"AptNum=0, "
+"PlannedAptNum=0 "
+"WHERE ProcNum = '"+POut.PInt(procNum)+"'";
General.NonQ(command);

[jordansparks]

One school of thought says that the eventual goal should be to delete no data at all. Preserve ever single change. So that was one factor that led us to choose to retain those procedures. It's true that relational databases are not designed in such a way as to make permanent retention easy. You are correct that the choice we made does not allow you to tell if the procedure was originally complete or TP. But the way we did it seemed like a first good approach. I suppose we could eventually move towards allowing actual deletion if there are no notes attached. And as more of a quick fix, we could add to the db maint tool to permanently delete procedures that are now just tagged as deleted.

[mowgli]

...And as more of a quick fix, we could add to the db maint tool to permanently delete procedures that are now just tagged as deleted.

Something like this?
delete from procedurelog where procstatus = 6 and procnum not in (select procnum from procnote)

[jordansparks]

"IN()" is pretty slow on MySQL especially when the size of your set would be ALL notes in the database. I would probably use AND NOT EXISTS(SELECT * FROM procnote WHERE procnote.ProcNum=procedurelog.ProcNum)