Re: Damsel in distress!
From: Victoria Chin (denryoku_at_hotmail.com)
Date: 02/27/04
- Next message: Victoria Chin: "Re: Damsel in distress!"
- Previous message: John Bell: "Re: Date queries"
- In reply to: Joe Celko: "Re: Damsel in distress!"
- Next in thread: Invotion: "Damsel in distress!"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 27 Feb 2004 06:57:08 -0800
Thank you for your response. You will have to forgive me
on the terminology, I'm not a MCDBA, I am trying to learn
it though. I am trying to write this in SQL and your post
has helped. Thanks again. Vicky
>-----Original Message-----
>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: Victoria Chin: "Re: Damsel in distress!"
- Previous message: John Bell: "Re: Date queries"
- In reply to: Joe Celko: "Re: Damsel in distress!"
- Next in thread: Invotion: "Damsel in distress!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
