Re: Damsel in distress!
From: Joe Celko (joe.celko_at_northface.edu)
Date: 02/23/04
- Next message: Tibor Karaszi: "Re: Security"
- Previous message: Anith Sen: "Re: Stored Proc using "like"??"
- In reply to: Victoria Chin: "Damsel in distress!"
- Next in thread: J. M. De Moor: "Re: Damsel in distress!"
- Reply: J. M. De Moor: "Re: Damsel in distress!"
- Reply: Victoria Chin: "Re: Damsel in distress!"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 23 Feb 2004 10:10:54 -0800
I love the title to this posting. Unfortunately, you did not bother
with any DDL and (if I am guessing correctly) are not writting in SQL
Server. Oh, and have a bad schema design.
Have you ever met anyone with a CHAR(50) name? Even Greek and Polish
names are under CHAR(30)! You should have some other identifier for the
employees, not just their name. Also, "who" is a question and not a
name for a data element.
Next, there is no such thing as "id" -- the magic, all-purpose,
one-size-fits-all unique-ifier. RDBMS uses keys; by defintion a key is
a subset of the attribures of the entity in the data model.
Auto-numbering is crap that imitates a sequential file system and should
never be used inside a database -- save it for cursors and other
sequential applications.
Finally, time exists in durations, not points; see Zeno's paradoxes and
Einstein's physics for the details. You need a start and a stop time.
You also need to read ISO-11179 so you will not use names like "leave"
and "who" for a code. Are the leave types really CHAR(10)? That is long
for a code and it invites errors. Are tehre really soooo many of them
and/or dothey change sooo often that you need to put them in another
table? Probably not; I'd guess a CHECK() is better.
Therefore, the table you should have designed looks like this, perhaps
with a few more constraints and columns (columns and rows, NOT fields
and records!!).
CREATE TABLE TimeCards
(emp_id INTEGER NOT NULL,
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
time_in DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
time_out DATETIME,
leave_type CHAR(10) DEFAULT 'Screwing off' NOT NULL
CHECK (leave_type IN (..))
PRIMARY KEY (emp_id, time_in));
The NULL clock_out time means "still at work"; you fill it in when they
leave the job.
>> If they don't come to work, no record [sic] is created in the table
that day. It works great, however, my boss now wants a record [sic]
created automatically for employees when they do not "clock in". <<
Since we did not get any specs, I am going to assume that an absent
employee is shown as (1) leave_type = 'not at work' (2) time_in =
time_out (3) The work day starts at 00:00 Hrs; I know it is probably
more like 08:00 Hrs, but this is a nice fiction.
You will need a Calendar table (you can Google details in several
postings here) that shows the work days. Run this query before you have
to do a report.
INSERT INTO Timecards (emp_id, time_in, time_out)
SELECT P1.emp_id, C1.cal_date, C1.cal_date
FROM Personnel AS P1,
Calendar AS C1
WHERE C1.date_type = 'work'
AND NOT EXISTS
(SELECT *
FROM Timecards AS T1
WHERE T1.emp_id = P1.emp_id
AND C1.cal_date
= EXTRACT(DATE FROM T1.clock_in));
I lapsed into Standard SQL with EXTTRACT(), but you can use whatever
your dialect has. I left the DEFAULT value take effect. In English
this says: Look at the personnel and the work days, and see that a
timecard exists for everyone; if it does not exist, then insert one for
that work day.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Tibor Karaszi: "Re: Security"
- Previous message: Anith Sen: "Re: Stored Proc using "like"??"
- In reply to: Victoria Chin: "Damsel in distress!"
- Next in thread: J. M. De Moor: "Re: Damsel in distress!"
- Reply: J. M. De Moor: "Re: Damsel in distress!"
- Reply: Victoria Chin: "Re: Damsel in distress!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
