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:
(you know where this is going)
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:
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.
Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant
Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."