Re: tracking shipments, inventory

>> Do you ever feel like you are tilting at windmills on this? Surely
you must
feel outnumbered. <<

Nah! More like a crusader :) Knowing what something means and using the right words is important. What I do feel is that we have a lot of people writing databases who should not be doing the work. When your mind-set is still back in the days of paper forms and mag tape files, your designs are also back there.

Records and fields do not have keys, constraints or DRI, so you do not think about the schema as a whole when you say those words. In this case, the schema probably should have been something like:

CREATE TABLE ShipmentHistory
(shipment_nbr INTEGER NOT NULL,
shipment_status CHAR(3) NOT NULL
CHECK (shipment_status IN (..)),
san INTEGER NOT NULL -- standard address number REFERENCES Locations(san)
ON UPDATE CASCADE,
start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL, end_date DATE DEFAULT CURRENT_TIMESTAMP, -- null means current PRIMARY KEY ((shipment_nbr, start_date));

This is a state-change model that would let you track the shipments. That is the nature of time. Various paper forms cause the changes, but we do not have to mimic them in this table.

>> My manager refers to an Oracle table as a "file" and it drives me
nuts. <<

Look at the posting in newsgroups to depress yourself even more. Your boss has an excuse and no schema access :) Received on Sat Dec 25 2004 - 18:23:49 CET