VbzCart/tables/ord shipmt
Jump to navigation
Jump to search
About
- Purpose: Shipments of customer packages (
- REDIRECT Template:l/vc/table)
- History:
- 2008-12-27 Adapted from MS Access table "Shipments"
- 2009-07-07 Added to L48 version of database
- Fields:
- WhenClosed can be set whenever it is known that the shipment is not going to be added to -- either when the shipment is being taken to the carrier, or whenever it is noted that the carrier has picked it up.
- ReceiptCost and OutsideCost were mainly for the bad old days when it was quickest to take the stuff to the post office and have them weigh and stamp everything.
- OrderCost was called "PostageTotal" in Access
- SupplCost was called "MaterialsCost" in Access
- ID_Whse originally pointed to cat_supp; that area of data needs some tidying. At one point it seemed like a good idea to have all contact info in one table (suppliers, warehouses, maybe even customers), but any minimal form-design work this might save is counteracted by it being a general maintenance headache (plus you need those extra fields to indicate which type of contact each record is). So... eventually, a separate table for shipping locations... or maybe that should be "shippers", depending on whether we end up actually implementing the idea that this was supposed to support -- i.e. allowing multiple businesses to display their stock on vbz.net, a little like Amazon (though I had the idea before Amazon did it).
- Abbr is customarily the date in YYYY-MM-DD format plus "po" (for a shipment taken to the post office) or "mbx" (for a shipment left in the mailbox for pickup). On days with multiple shipments, a letter might be added to the date to differentiate between them, e.g. 1999-12-23-A, 1999-12-23-B, etc.
- Descr is single-line and may appear in some lists; Notes can be multi-line and generally would not.
- isOnHold may be obsolete
- isDedicated may also be obsolete, or at least there should be a tidier way of doing this
SQL
<mysql>CREATE TABLE `ord_shipmt` (
`ID` INT NOT NULL AUTO_INCREMENT, `WhenCreated` DATETIME COMMENT "when this record was created", `WhenShipped` DATETIME COMMENT "time when shipment was picked up by, or dropped off at, the carrier", `WhenClosed` DATETIME COMMENT "when this shipment was no longer available for more pkgs", `ReceiptCost` DECIMAL(9,2) COMMENT "total from postal receipt, including non-order items (e.g. stamps)", `OutsideCost` DECIMAL(9,2) COMMENT "amount for non-order items", `OrderCost` DECIMAL(9,2) COMMENT "amount for order shipment only; ReceiptCost-OutsideCost=OrderCost", `SupplCost` DECIMAL(9,2) COMMENT "estimated cost of materials/supplies used for shipping (mainly envelopes)", `ID_Whse` INT DEFAULT NULL COMMENT "location from which items were shipped", `Carrier` VARCHAR(15) COMMENT "shipping carrier: USPS, UPS, FedEx...", `Abbr` VARCHAR(63) COMMENT "short identifying string - usually date (YYYY-MM-DD) plus an indicator", `Descr` VARCHAR(255) COMMENT "optional descriptive text - 'dropped in overnight box', 'handed to carrier'", `Notes` TEXT COMMENT "more lengthy multi-line notes", `isDedicated` BIT COMMENT "TRUE = special-purpose shipment", `isOnHold` BIT COMMENT "TRUE = these packages are actually still here (may be obsolete)", PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>