Difference between revisions of "VbzCart/tables/cust"
Jump to navigation
Jump to search
m (tidying) |
|||
| (6 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 | + | ** '''WhenChanged''': when the contact's dependent records (address, emails, etc.) were last modified (probably not implemented much) |
| − | ** '''WhenUpdated''': When this | + | ** '''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== | ||
| − | <mysql>CREATE TABLE ` | + | <source lang=mysql>CREATE TABLE `cust` ( |
`ID` INT NOT NULL AUTO_INCREMENT, | `ID` INT NOT NULL AUTO_INCREMENT, | ||
| − | `ID_Name` INT | + | `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` | + | `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", | ||
| − | ` | + | `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`) | PRIMARY KEY(`ID`) | ||
) | ) | ||
| − | ENGINE = | + | ENGINE = InnoDB;</source> |
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;