VbzCart/tables/ord shipmt

From HTYP, the free directory anyone can edit

[edit] About

• Purpose: Shipments of customer packages (ord_pkgs)
• 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

[edit] SQL

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;