Oracle Developer

by Carl F
(Falls Church VA)

I was hired as a developer on a "mature" project, still running on Forms & Reports 6.0 (not 6i) in 2006. My job consisted of fixing problems in a crumbling edifice of hundreds of forms and reports and disjointed database procedures & functions (no packages!), and occasionly adding some new functionality.
My favorite "fix" was a request to allow users to enter more than two pair of IN/OUT times on their timeclock. The timeclock covered a two week period. Well, when I went to look at the table behind the timeclock form and here is what I found:
(PERSON_ID NUMBER,
SUN1_IN1 DATE,
SUN1_OUT1 DATE,
SUN1_IN2 DATE,
SUN1_OUT2 DATE,
MON1_IN1 DATE,
MON1_OUT1 DATE,
MON1_IN2 DATE,
MON1_OUT2 DATE,
.
.
.
(painfully)
.
.
.
SAT1_IN1 DATE,
SAT1_OUT1 DATE,
SAT1_IN2 DATE,
SAT1_OUT2 DATE,
SUN2_IN1 DATE,
SUN2_OUT1 DATE,
SUN2_IN2 DATE,
SUN2_OUT2 DATE,
.
.
.
(you know where this is going)
.
.
.
SAT2_IN1 DATE,
SAT2_OUT1 DATE,
SAT2_IN2 DATE,
SAT2_OUT2 DATE)
That's right: 56 DATE columns to handle 2 weeks of time clock. Well if I were to stick to the original design, for each day, I'd be adding IN3 & OUT3, IN4 & OUT4 (i.e. 56 more columns!) to the table and it would still be constrained (to 4 in/out times). I went to the original developer and offered to build a new table:
(PERSON_ID NUMBER,
WEEK_NO NUMBER(1), -- 1 or 2
WK_DAY VARCHAR2(3) -- SUN, MON,..., SAT
IN_OUT_FLG VARCHAR2(1), -- I or O
IN_OUT_NUM INTEGER, -- 1,2,3,4,...
DTIME VARCHAR2(5)) -- current scheme using DATE did not allow users to go home at midnight (since maximum allowed was 23:59).
I said I would build a view of the new table that looked like the original table so that existing reports could still work. I said that I thought normalization was the way to go. This stategy would allow any number of IN/OUT (actually it would even allow a four week timeclock or addition of a new day of the week for that matter).
He told me (this is the best part) that the table WAS normalized - "normalized for the form". His solution, which I was compelled to implement, was to allow the form to generate new timeclock records so users could add another record of IN1, OUT1, IN2 OUT2 (that's right - 56 columns worth) if they needed an IN3 OUT3 on a single day.