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 (EMPLOYEE_ID 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_id = EMPLOYEE_ID
          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 time 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 customers 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 ready 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 data, 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.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!