VbzCart/tables/ord msg

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to: navigation, search

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>