Difference between revisions of "VbzCart/tables/cust"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(note about WhenChanged)
 
(7 intermediate revisions by the same user not shown)
Line 4: Line 4:
 
** '''ID_Repl''': If non-null, then this customer ID has been consolidated with the customer whose ID is in ID_Repl; whenever/wherever possible, replace occurrences of this ID with the value of the corresponding ID_Repl.
 
** '''ID_Repl''': If non-null, then this customer ID has been consolidated with the customer whose ID is in ID_Repl; whenever/wherever possible, replace occurrences of this ID with the value of the corresponding ID_Repl.
 
** '''WhenCreated''': This should be NOT NULL on systems with no legacy data
 
** '''WhenCreated''': This should be NOT NULL on systems with no legacy data
−
** '''WhenChanged''': not sure if this field has a purpose; it could be set whenever Notes or ID_Repl are edited, maybe?
+
** '''WhenChanged''': when the contact's dependent records (address, emails, etc.) were last modified (probably not implemented much)
−
** '''WhenUpdated''': When this customer was updated (any time new order comes in, even if no contact info changes)
+
** '''WhenUpdated''': When this record was last modified
 
* '''History''':
 
* '''History''':
 
** '''2009-07-09''' moved to separate wiki page; no design changes
 
** '''2009-07-09''' moved to separate wiki page; no design changes
 
** '''2011-11-29''' added '''WhenUpdated''' field
 
** '''2011-11-29''' added '''WhenUpdated''' field
 +
** '''2013-10-10''' added '''ID_User''' and '''Title''' fields (see {{vbzcart|table|core_users}} for rules)
 +
** '''2016-06-12''' switching definitions of WhenChanged and WhenUpdated
 +
*** Only one record had WhenUpdated set; very few have WhenChanged set.
 +
** '''2019-07-02'''
 +
*** renamed from "core_custs" to "cust" so it will group with the other customer tables
 +
*** removed NOT NULL constraint on ID_Name because we need to create this record before we create the {{l/vc/table|cust_names}} record
 +
**** perhaps we can make this unnecessary later with transactions? but it messes with debugging, even so, so maybe we should just put in a post-creation test to make sure all necessary fields got filled in (log data error if not)
 
==SQL==
 
==SQL==
−
<section begin=sql /><mysql>CREATE TABLE `core_custs` (
+
<source lang=mysql>CREATE TABLE `cust` (
 
   `ID`          INT          NOT NULL AUTO_INCREMENT,
 
   `ID`          INT          NOT NULL AUTO_INCREMENT,
−
   `ID_Name`    INT         NOT NULL  COMMENT "cust_names.ID of default name",
+
   `ID_Name`    INT     DEFAULT NULL  COMMENT "cust_names.ID of default name",
 
   `ID_Addr`    INT      DEFAULT NULL  COMMENT "cust_addrs.ID of default address",
 
   `ID_Addr`    INT      DEFAULT NULL  COMMENT "cust_addrs.ID of default address",
−
   `Notes`       VARCHAR(255)           COMMENT "admin-entered notes about this customer",
+
  `ID_User`    INT      DEFAULT NULL  COMMENT "core_users.ID of user for this customer",
 +
  `Title`  VARCHAR(127) DEFAULT NULL  COMMENT "user-entered title for this customer profile",
 +
   `Notes`   VARCHAR(255) DEFAULT NULL  COMMENT "admin-entered notes about this customer",
 
   `ID_Repl`    INT      DEFAULT NULL  COMMENT "core_custs.ID of replacement",
 
   `ID_Repl`    INT      DEFAULT NULL  COMMENT "core_custs.ID of replacement",
 
   `WhenCreated` DATETIME  /*NOT NULL*/ COMMENT "when this record was created",
 
   `WhenCreated` DATETIME  /*NOT NULL*/ COMMENT "when this record was created",
−
   `WhenChanged` DATETIME DEFAULT NULL  COMMENT "when this record was last edited",
+
   `WhenUpdated` DATETIME DEFAULT NULL  COMMENT "when this record was last updated",
−
   `WhenUpdated` DATETIME DEFAULT NULL  COMMENT "when this contact was last updated",
+
   `WhenChanged` DATETIME DEFAULT NULL  COMMENT "when this record's dependent data was last edited",
 
   PRIMARY KEY(`ID`)
 
   PRIMARY KEY(`ID`)
 
)
 
)
−
ENGINE = MYISAM;</mysql>
+
ENGINE = InnoDB;</source>
−
<section end=sql />
 

Latest revision as of 01:07, 3 July 2019

About

  • Purpose: Core customer table – all the other customer data tables point to this one
  • Fields:
    • ID_Repl: If non-null, then this customer ID has been consolidated with the customer whose ID is in ID_Repl; whenever/wherever possible, replace occurrences of this ID with the value of the corresponding ID_Repl.
    • WhenCreated: This should be NOT NULL on systems with no legacy data
    • WhenChanged: when the contact's dependent records (address, emails, etc.) were last modified (probably not implemented much)
    • WhenUpdated: When this record was last modified
  • History:
    • 2009-07-09 moved to separate wiki page; no design changes
    • 2011-11-29 added WhenUpdated field
    • 2013-10-10 added ID_User and Title fields (see core_users for rules)
    • 2016-06-12 switching definitions of WhenChanged and WhenUpdated
      • Only one record had WhenUpdated set; very few have WhenChanged set.
    • 2019-07-02
      • renamed from "core_custs" to "cust" so it will group with the other customer tables
      • removed NOT NULL constraint on ID_Name because we need to create this record before we create the cust_names record
        • perhaps we can make this unnecessary later with transactions? but it messes with debugging, even so, so maybe we should just put in a post-creation test to make sure all necessary fields got filled in (log data error if not)

SQL

CREATE TABLE `cust` (
  `ID`          INT          NOT NULL AUTO_INCREMENT,
  `ID_Name`     INT      DEFAULT NULL   COMMENT "cust_names.ID of default name",
  `ID_Addr`     INT      DEFAULT NULL   COMMENT "cust_addrs.ID of default address",
  `ID_User`     INT      DEFAULT NULL   COMMENT "core_users.ID of user for this customer",
  `Title`   VARCHAR(127) DEFAULT NULL   COMMENT "user-entered title for this customer profile",
  `Notes`   VARCHAR(255) DEFAULT NULL   COMMENT "admin-entered notes about this customer",
  `ID_Repl`     INT      DEFAULT NULL   COMMENT "core_custs.ID of replacement",
  `WhenCreated` DATETIME   /*NOT NULL*/ COMMENT "when this record was created",
  `WhenUpdated` DATETIME DEFAULT NULL   COMMENT "when this record was last updated",
  `WhenChanged` DATETIME DEFAULT NULL   COMMENT "when this record's dependent data was last edited",
  PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;
Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/cust&oldid=25000"