VbzCart/docs/tables/ord shipmt

About

• Purpose: Shipments of customer packages (Template:Vbzcart/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>