From 4bee4396835da9430c2b63fe86a2b08f8830f7dd Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Tue, 17 Jul 2018 16:13:37 -0400 Subject: [PATCH] Updating contact tables table updates for revamp --- .../create_database_V0.0.3.sql | 63 ++++++++++++++++--- 1 file changed, 53 insertions(+), 10 deletions(-) diff --git a/setup/databaseScripts/create_database_V0.0.3.sql b/setup/databaseScripts/create_database_V0.0.3.sql index b74ad14..34b7ae6 100644 --- a/setup/databaseScripts/create_database_V0.0.3.sql +++ b/setup/databaseScripts/create_database_V0.0.3.sql @@ -1,13 +1,13 @@ --- Gaslight Media Members Database +-- Gaslight Media Members Database -- File Created: 12/09/14 15:27:15 -- Database Version: 0.0.3 -- Database Creation Script - Contacts Add-On --- +-- -- To permit each query below to be executed separately, -- all queries must be separated by a line with four dashes --- +-- -- **** BE SURE TO ALSO UPDATE drop_database_Vxxx.sql FILE WHEN CHANGING TABLES **** --- +-- -- Contacts - used by various entities CREATE TABLE {prefix}contacts ( @@ -24,12 +24,12 @@ CREATE TABLE {prefix}contacts ( descr TEXT NULL, -- Description of position/responsibilities - Displayed image TINYTEXT NULL, -- Image addr1 TINYTEXT NULL, -- Address line 1 - Address is for contact, not necessarily for organization - addr2 TINYTEXT NULL, -- Address line 2 + addr2 TINYTEXT NULL, -- Address line 2 county TINYTEXT NULL, -- County city INT NULL, -- Pointer to city in cities table state TINYTEXT NULL, -- Two character state code - matches states.ini entries - country TINYTEXT NULL, -- Two character country code - matches countries.ini entries - zip TINYTEXT NULL, -- ZIP/Postal Code + country TINYTEXT NULL, -- Two character country code - matches countries.ini entries + zip TINYTEXT NULL, -- ZIP/Postal Code lat FLOAT NULL, -- Latitude of contact location lon FLOAT NULL, -- Longitude of contact location url TINYTEXT NULL, -- URL to information regarding this contact @@ -37,16 +37,33 @@ CREATE TABLE {prefix}contacts ( home_phone TINYTEXT NULL, -- Home phone number - or after-hours phone number mobile_phone TINYTEXT NULL, -- Mobile phone number alt_phone TINYTEXT NULL, -- An alternate phone number - fax TINYTEXT NULL, -- FAX number (do people still use these?) + fax TINYTEXT NULL, -- FAX number (do people still use these?) email TINYTEXT NULL, -- E-Mail address - alt_email TINYTEXT NULL, -- Alternate E-Mail address - Also used to log-in + alt_email TINYTEXT NULL, -- Alternate E-Mail address - Also used to log-in username TINYTEXT NULL, -- Optional username to use for login password TINYTEXT NULL, -- Encrypted password notes TEXT NULL, -- Notes - Not displayed on front-end create_time TIMESTAMP NULL, -- Create date/time modify_time TIMESTAMP NULL, -- Last modified date/time - ref_type INT NULL, -- Type of entity this contact is associated with + ref_type INT NULL, -- Type of entity this contact is associated with ref_dest INT NULL, -- Pointer to the specific entity of ref_type this contact is associated with + mailing_address_type INT NULL, -- Mailing Address Type + mailto_label TINYTEXT NULL, -- Mail To Label + publish BOOLEAN NULL, -- Publish the mailing address on front + use_for_billing BOOLEAN NULL, -- Use For Billing Address + business_org TINYTEXT NULL, -- Business Org/Company Name + business_fname TINYTEXT NULL, -- Business First Name + business_lname TINYTEXT NULL, -- Business Last Name + business_addr1 TINYTEXT NULL, -- Business Address 1 + business_addr2 TINYTEXT NULL, -- Business Address 2 + business_city INT NULL, -- Business City (pointer to city table) + business_state TINYTEXT NULL, -- Business State (states.ini) + business_zip TINYTEXT NULL, -- Business Zip + business_country TINYTEXT NULL, -- Business Country (countries.ini) + business_email TINYTEXT NULL, -- Business Email + business_mobile_phone TINYTEXT NULL, -- Business Mobile Phone + business_publish BOOLEAN NULL, -- Business Publish Flag + business_use_for_billing BOOLEAN NULL, -- Use for Billing Flag PRIMARY KEY (id), INDEX(fname(20)), INDEX(lname(20)), @@ -56,3 +73,29 @@ CREATE TABLE {prefix}contacts ( INDEX(lon), INDEX(email(20)) ); + +---- + +-- Contacts Address +CREATE TABLE {prefix}contact_addresses ( + id INT NOT NULL AUTO_INCREMENT, + address_type INT NULL, -- Address type + use_for_billing BOOLEAN NULL, -- Use For Billing Address + publish BOOLEAN NULL, -- Publish Address on Front + mail_to_label TINYTEXT, -- Mail To Label + addr1 TINYTEXT NULL, -- Address line 1 + addr2 TINYTEXT NULL, -- Address line 2 + county TINYTEXT NULL, -- County + city INT NULL, -- Pointer to city in cities table + state TINYTEXT NULL, -- Two character state code - matches states.ini entries + country TINYTEXT NULL, -- Two character country code - matches countries.ini entries + zip TINYTEXT NULL, -- ZIP/Postal Code + country TINYTEXT NULL, -- Two character country code - matches countries.ini entries + home_phone TINYTEXT NULL, -- Home phone number - or after-hours phone number + mobile_phone TINYTEXT NULL, -- Mobile phone number + email TINYTEXT NULL, -- E-Mail address + PRIMARY KEY (id), + INDEX(city), + INDEX(zip(10)), + INDEX(email(20)) +); -- 2.17.1