VbzCart/tables/ord msg

About

• Purpose: Record of messages sent/received about this order
• Notes: This subsystem should probably be rethought at some point when everything else is working -- possibly should be part of the order event log, along with ord_pull
• Relates to: core_orders, ord_pkgs, ord_msg_media
• History:
  • 2009-07-09 Adapted from MS Access
  • 2009-07-10 Changed "Notes" from VARCHAR(255) to MEDIUMTEXT
• Fields:
  • TxtFrom (was "From")
  • TxtTo (was "To")
  • TxtRe (was "Subject")
  • doRelay: does message need to be sent to a third party? (this has a specific meaning, but if I can't remember/reconstruct what it was... are any records using it?)
  • WhenRelayed should only ever be set if doRelay is TRUE
  • Notes may contain the substance of the message record, because sometimes it's more about what happened than about a single message being transferred
• Future:
  • Possibly message relaying should be handled by a separate app, but if not it needs to be rethought a bit:
    • ord_msg_media should probably include a flag to indicate if a given medium (method?) needs human relaying (NO = automatic, like email)
    • Is WhenRelayed basically the mirror of WhenCreated, just in the other direction? Or can they both have meaning in the same context?

SQL

<mysql>CREATE TABLE `ord_msg` (

 `ID`         INT              NOT NULL AUTO_INCREMENT,
 `ID_Ord`     INT              NOT NULL COMMENT "core_orders.ID",
 `ID_Pkg`     INT          DEFAULT NULL COMMENT "ord_pkgs.ID",
 `ID_Media`   INT              NOT NULL COMMENT "ord_msg_media.ID",
 `TxtFrom`    VARCHAR(63)  DEFAULT NULL COMMENT "who this message is from",
 `TxtTo`      VARCHAR(63)  DEFAULT NULL COMMENT "who this message is to",
 `TxtRe`      VARCHAR(127) DEFAULT NULL COMMENT "subject of message",
 `doRelay`    BOOL         DEFAULT NULL COMMENT "YES = this message needs to be relayed",
 `WhenCreated` DATETIME    DEFAULT NULL COMMENT "when message was originally created",
 `WhenEntered` DATETIME    DEFAULT NULL COMMENT "when message was entered into database",
 `WhenRelayed` DATETIME    DEFAULT NULL COMMENT "when message was relayed",
 `Message`    MEDIUMTEXT       NOT NULL COMMENT "the message",
 `Notes`      MEDIUMTEXT   DEFAULT NULL COMMENT "internal notes",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>