Norway


Chris D’s has a problem. We can see a hint of the kind of problem he needs to deal with by looking at this code:

FUNCTION WHOIS (_ IN VARCHAR2, Action_Date IN DATE)
   RETURN varchar2
IS
  Employee_Name varchar2(50);
BEGIN
   SELECT  employee_name INTO Employee_Name
     FROM eyps_manager.tbemployee_history
    WHERE  ROWNUM=1 AND   employee_ = EMPLOYEE_
          AND effective_start_date <= Action_Date
          AND (Action_Date < effective_end_date OR effective_end_date IS NULL);

   RETURN (Employee_Name);
END WHOIS;

This particular function was written many years ago. The developer responsible, Artie, was fired a short later, because he broke the production database in an unrelated accident involving a badly aimed `DELETE FROM…`.

It’s a weird function- given an EMPLOYEE_ID, it returns an EMPLOYEE_NAME… but why all this work? Why check dates?

This particular business system was purchased back in 1997. The vendor didn’t ship the product with anything so mundane as an EMPLOYEES table- since every business was a unique and special snowflake, there was no way for the vendor to give them exactly the employee-tracking features they needed, so instead it fell to the customer to build the employee features themselves. The vendor would then take that code on for maintenance.

Which brings us to Artie. Artie was told to implement some employee tracking for the sales team. So he did. He gave everyone on the sales team an EMPLOYEE_ID, but instead of using an auto-numbered sequence, or a UUID, he invented a complicated algorithm for generating his own kind-of-unique IDs. These were grouped in blocks, so, for example, all of the IDs in the range “AA1000-AA9999” were assigned to widget sales, while “AB1000A-AB9999A” were for office supply sales.

This introduced a new problem. You see, EMPLOYEE_ID wasn’t a unique ID for an employee. It was actually a sales portfolio ID, a pile of and their orders and sales. Sales people would swap portfolios around as one employee left, or a new hire took on a new portfolio. This made it impossible to know who was actually responsible for which sale.

Artie was to solve that problem, though, as he quickly added the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE fields. Instead of updating rows as portfolios moved around, you could simply add new rows, keeping an ongoing history of which employee held which portfolio at any given time.

There’s also a UI to manage this , which was written circa 2000. It is a simple data-grid with absolutely no validation on any of the fields, which means anyone using it corrupts data on a fairly regular basis, and then Chris or one of his peers has to go into the production database and manually correct the data.

hljs.initHighlightingOnLoad();

CodeSOD: How is an Employee ID like a Writing Desk? - atlasoft 50x50 - CodeSOD: How is an Employee ID like a Writing Desk? [Advertisement]
Atalasoft’s imaging SDKs come with APIs & pre-built controls for viewing, browser scanning, annotating, & OCR/barcode capture. Try it for 30 days with included support.
CodeSOD: How is an Employee ID like a Writing Desk? - TheDailyWtf d yIl2AUoC8zA - CodeSOD: How is an Employee ID like a Writing Desk?

CodeSOD: How is an Employee ID like a Writing Desk? - g2ROVOranig - CodeSOD: How is an Employee ID like a Writing Desk?



Source link

LEAVE A REPLY

Please enter your comment!
Please enter your name here