|
|
Posted by --CELKO-- on 05/15/07 16:47
Everything you are doing is wrong. Please read any book on data
modeling and the ISO-11179 naming rules. There is no such attribute
as a magical, universal "status" in a valid data model; it has to be
some kind of status -- "shipping_status", "martial_status",
"employment_status", etc.
Monsters postfixes like "_status_cat_id" are really silly! What is
it? A status code, a category? or an identifier? By definition it
cannot be all of those things at once. Hey why not go all out and
have a "_status_cat_id_value_name_time" postfix!
You then load tables with UNIQUEIDENTIFIER and IDENTITY(1,1); but at
least you know that these are for non-relational physical locators,
like sequentially numbering rows to get a fake magnetic tape and not
even pretend that you are doing RDBMS design.
>>. The purpose of the following is to have a status for a person, and also have a historical background as to what they've done, status wise. <<
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.
[Back to original message]
|