Delurk post for me, I need to vent...
I'm a sysadmin stuck with keeping a cruddy hospital patient system reasonably alive and preferably consistent. The vendor decided last year that they didn't want to support the product any more to "Focus on their core market of primary care" instead of specialist hospital systems. This of course caught the powers that be totally unawares (they were warned by me repeatedly) and so we have no replacement. Yes, we have to run an unsupported patient system.
Recently users began to complain about Word docs attached to patient records going missing and being replaced by empty documents. The following search revealed WTFs layered on WTFs.
An individual entry in a patient journal can have one and only one document attached. Despite this there are naturally no constraints in the database. No unique, no foreign kyes, nothing. There is also a bug somewhere in the appication layer that results in aditional attachments being linked to the same journal entry. The vendor must have realized that something wierd was happening because they've implemented a check for this when you fetch a document. SQL profiler revealed the following:
SELECT COUNT(*) FROM documents WHERE journal_entry = <whatever>
Immediatly followed by SELECT MAX(document_id) FROM documents WHERE journal_entry = <whatever>
So first they check for a condition that should never happen and could be prevented by decent DB-design, and then they ignore the result of said check (hey, it should never happen anyway...) and assume that the document with the highest ID is the one we want. A bug also sometimes results in blank documents being written to the database when the users attempt to edit an existing doc. This new blank doc has a higher id than the original. I can't fix the bug because I don't have the source and the vendor refuses to have anything to do with the product anymore so I had try for a workaround.
OK, then to find the stored procedure responsible and fix it to get the original document instead, never mind all the erroneous documents being created. Exchanging MAX for MIN in the query above should do it. But NOOOOO They didn't use a stored procedure for this. There are a hundred SPs or so in the DB so they knew what they were, but "get the document attached to a journal entry" they decided to do straight from the application. So the fix is waiting for the problem to occur and the manually deleting all the erroneous docs assosciated with a particular journal entry. By running a DELETE FROM documents WHERE <blah blah> on a live patient system.
TL;DR: No DB-constraints, serious bugs, checks that do nothing, inconsistent use of stored procs. And these people want to "focus on primary care systems". Yay.