Re: Looking for Employee data model

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 27 May 2007 15:30:02 -0700
Message-ID: <1180305002.246077.287770_at_u30g2000hsc.googlegroups.com>

>> I'm looking for examples of an Employee data model that touches on concepts of Employee status, position, title, department, division, section, work location, contact information, network account information, etc.<<

When you see "etc." in a spec, it is too general for a template.

Go to accounting and ask what they need for each employee; likewise Human Resources. Here is a "cut & paste" on history tales in general:

The usual design error is to have only one time in a row to capture when an event started, then do horrible self-joins to get the duration of the status change. Let me use a history table for price changes. The fact to store is that a price had a duration:

CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
  REFERENCES Inventory(upc),
 start_date DATE NOT NULL,
 end_date DATE, -- null means current
 CHECK(start_date < end_date),
 PRIMARY KEY (upc, start_date),
 item_price DECIMAL (12,4) NOT NULL
  CHECK (item_price > 0.0000),
 etc.);

You actually needs more checks to assure that the start date is at 00:00 and the end dates is at 23:59:59.999 Hrs. You then use a BETWEEN predicate to get the appropriate price.

SELECT ..
  FROM PriceHistory AS H, Orders AS O
 WHERE O.sales_date BETWEEN H.start_date

           AND COALESCE (end_date, CURRENT_TIMESTAMP);

It is also a good idea to have a VIEW with the current data:

CREATE VIEW CurrentPrices (..)
AS
SELECT ..
  FROM PriceHistory
 WHERE end_date IS NULL;

There is a common kludge to repair a failure to design a history table properly that you can put in a VIEW if you are not able to set things right. Assume that every day we take a short inventory and put it in a journal. The journal is a clip board paper form that has one line per item per day, perhaps with gaps in the data. We want to get this into the proper format, namely periods shown with a (start_date, end_date) pair for durations where each item had the same quantity on hand. This is due to Alejandro Mesa

CREATE TABLE InventoryJournal
(journal_date DATETIME NOT NULL,
 item_id CHAR(2) NOT NULL,
 onhand_qty INTEGER NOT NULL);

WITH ItemGroups
AS
(SELECT journal_date, item_id, onhand_qty,

        ROW_NUMBER() OVER(ORDER BY item_id, journal_date, onhand_qty)
        - ROW_NUMBER() OVER(PARTITION BY item_id, onhand_qty
                            ORDER BY journal_date) AS item_grp_nbr

   FROM Journal),

QtyByDateRanges
AS
(SELECT MIN(journal_date) AS start_date,

        MAX(journal_date) AS end_date,
        item_id, onhand_qty

   FROM ItemGroups
  GROUP BY item_id, onhand_qty, item_grp_nbr)

SELECT start_date, end_date, item_id, onhand_qty   FROM QtyByDateRanges;

This might be easier to see with some data and intermediate steps

INSERT INTO InventoryJournal VALUES('2007-01-01', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-01', 'BB', 200);
INSERT INTO InventoryJournal VALUES('2007-01-02', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-02', 'BB', 200);
INSERT INTO InventoryJournal VALUES('2007-01-03', 'AA', 100);
INSERT INTO InventoryJournal VALUES('2007-01-03', 'BB', 300);

start_date   end_date     item_id onhand_qty
==========================================
'2007-01-01' '2007-01-03'  'AA'    100

'2007-01-01' '2007-01-02' 'BB' 200
'2007-01-03' '2007-01-03' 'BB' 300

Now, download the Rick Snodgrass book on Temporal Queries in SQL from the University of Arizona website (it is free). And finally Google up my article at www.DBAzine.com on transition constraints. Received on Mon May 28 2007 - 00:30:02 CEST