The Database Design Resource Center

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.

Comments for Oracle Developer

Average Rating starstarstarstarstar

Click here to add your own comments

Oct 01, 2015
Rating
starstarstarstarstar
Hello Carl F NEW
by: Anonymous

Hello Carl F! I have checked your article "Oracle Developer" and I know someone who can completely guide you. Because he is an expert in the oracle field and frequently he solves their issues him self. Custom Essay Writing Site. By the way your is really wonderful I have visited here before therefore I know about it.

Sep 02, 2015
Rating
starstarstarstarstar
Oracle Developer NEW
by: Anonymous

The Prophet's SQL Developer device is only an instrument for application engineers to get to the databases. As a GUI instrument, it is less demanding to utilize and more useful than the essential order line device (sqlplus). assignment help . In any case, it does exclude any database programming.

Jun 23, 2015
Rating
starstarstarstarstar
Good post NEW
by: Dr. Effie Sawayn

Hello! Your site is great!
How much time did you spend to write so awesome articles?
I want to suggest you to advertise my site Caketoppers on your blog, is it possible?
I really love your site and your audience, I will pay any money to publish a guest post, for example, on your site.
Please, answer me! Thank you!

Jun 01, 2015
Rating
starstarstarstarstar
Guiding info NEW
by: Latour

Some little youngsters don't enthusiasm for his book and they stay confounded about their study. So training toys is the better method for learning instruction. Along these lines, all the youngsters have more enthusiasm for these toys. These toys help the children to learn training in a positive manner. Our get custom essay is very good for everyone.

Nov 15, 2007
Rating
starstarstarstarstar
Feeling for you...
by: Alf

Carl,
I canot even imagine your thoughts when you first became aware of this: It is a classical breach of 1st normal form about repeating fields, and there's nothing you can do about it since the system is deployed into production...

I suspect we'll be discussing this in deep detail at

http://forum.databasedesign-resource.com

You have my deepest sympathy for being put in this s**t...

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Worst Database Design Experience.


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

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."

Mike, USA

Read more Testimonials

Database Normalization eBook:


Database Normalization eBook



Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.