# Database design for Outlook-style contact management

# Version 1.8


# Written by Patrick Walsh (patrick@phpgroupware.org)


# Changelog
# ---------

# v.1.8
#        - Added an insert into applications sql statement - commented out
#          as it is only meant for development use at this time
#
# v.1.7  - Updated references in all files to to contact.db & organization.db
#         to reflect new name contact.meta & organization.meta
#        - Added field last_name_prefix to contact.main table to accomodate
#          some non-US users. Most notably dutch people at present. 
#        - Changed some field types from text to varchar(255)
#
# v.1.6  -Added pgw_cdb_ prefix to all tables.
#        
#        -Added language_code to the language table for two digit 
#         language codes.  Also added INSERTS for many of the languages.
#
# v.1.5  -Added ldap_dn to allow instant referencing of ldap entries by 
#         distinguished name.  This, of course, applies only to records
#         with ldap_sync=1.
#
# v.1.4  -Added ldap_sync flag to contact_db table for flagging data to 
#         be sync'ed with an ldap server and/or exposed via an ldap 
#         interface.
#
# v.1.3  -Fixed a number of typos in the SQL definitions.  The 
#         definitions work w/o error using MySQL v.3.22.32.  Someone with
#         access to a Postgres server should test this and let me know 
#         if anything needs to be changed.
#
#        -Made the file_as fields NOT NULL so that they can be indexed.
#         This means that the file_as field is absolutely required when
#         saving to the database.  If left blank by the user, perhaps we
#         can assume "last_name, first_name" or "orgranization_name"...
#
#        -Changed the contact_id field to entity_id in category_link.
#
#        -Removed main_phone from the organization table as it is now 
#         replaced by the location table.  
#
#         New table 'organization_location' stores a list of location/
#         phone/ address records that allows each organization to have
#         multiple locations with distinct phone numbers and addresses.
#
#        -In the contact table, main_organization_selector is now called
#         main_organization_location_selector.
#
#        -Removed contact_organization_link table (what was I thinking
#         when I added this?)
#
#        -Edited the contact_organization table so that now each record
#         has a link to an organization location, a contact, and has
#         properties specific to the relationship, like the contact's
#         assistant's name.  This table establishes the contact/org
#         links.
#
# v.1.2: -Added organization_db to store last access, etc. for
#         organizations
#
#        -contact_main: removed address_selector as the main page should
#         only have one address and that should be the mailing address.
#
# v.1.1: -Added organization_client table to provide a mechanism for
#         tracking companies as customers/clients.
#        
#        -Added contact_type field to the category_link table.  
#         contact_type can be either "individual" or "organization" and
#         determines whether contact_id is a contact_id or an 
#         organization_id.
#         
#         This allows the categorization of companies as well as 
#         individual contacts.  [Note: the contact_id field should be
#         renamed to something more generic.. entity_id?']


# The following insert command is used only by developers at this time
# Have patience, and all will be good.
#
#INSERT INTO applications (app_name, app_title, app_enabled, app_order, app_tables, app_version) VALUES ('cdb','cdb-devel',1,31,'pgw_cdb_category,pgw_cdb_category_link,pgw_cdb_contact_address,pgw_cdb_contact_address_category,pgw_cdb_contact_client,pgw_cdb_contact_flag,pgw_cdb_contact_flag_link,pgw_cdb_contact_followup,pgw_cdb_contact_internet,pgw_cdb_contact_main,pgw_cdb_contact_meta,pgw_cdb_contact_notes,pgw_cdb_contact_organization,pgw_cdb_contact_personal,pgw_cdb_contact_phone,pgw_cdb_contact_phone_category,pgw_cdb_language,pgw_cdb_organization,pgw_cdb_organization_client,pgw_cdb_organization_location,pgw_cdb_organization_meta,pgw_cdb_organization_notes',1.5);

#
# The following DROP TABLE commands are used by the
# db developers for testing different db schemes.
# Uncomment only if you do not care about the data
# in your contact databases
#

#DROP TABLE IF EXISTS pgw_cdb_contact_main;
#DROP TABLE IF EXISTS pgw_cdb_contact_personal;
#DROP TABLE IF EXISTS pgw_cdb_contact_db;
#DROP TABLE IF EXISTS pgw_cdb_contact_meta;
#DROP TABLE IF EXISTS pgw_cdb_contact_client;
#DROP TABLE IF EXISTS pgw_cdb_contact_notes;
#DROP TABLE IF EXISTS pgw_cdb_contact_internet;
#DROP TABLE IF EXISTS pgw_cdb_contact_phone;
#DROP TABLE IF EXISTS pgw_cdb_contact_phone_category;
#DROP TABLE IF EXISTS pgw_cdb_contact_address;
#DROP TABLE IF EXISTS pgw_cdb_contact_organization;
#DROP TABLE IF EXISTS pgw_cdb_contact_address_category;
#DROP TABLE IF EXISTS pgw_cdb_category;
#DROP TABLE IF EXISTS pgw_cdb_category_link;
#DROP TABLE IF EXISTS pgw_cdb_language;
#DROP TABLE IF EXISTS pgw_cdb_contact_flag;
#DROP TABLE IF EXISTS pgw_cdb_contact_flag_link;
#DROP TABLE IF EXISTS pgw_cdb_contact_followup;
#DROP TABLE IF EXISTS pgw_cdb_organization;
#DROP TABLE IF EXISTS pgw_cdb_organization_notes;
#DROP TABLE IF EXISTS pgw_cdb_organization_client;
#DROP TABLE IF EXISTS pgw_cdb_organization_db;
#DROP TABLE IF EXISTS pgw_cdb_organization_meta;
#DROP TABLE IF EXISTS pgw_cdb_organization_location;


#
# Table structure for table 'contact_main'
#
CREATE TABLE pgw_cdb_contact_main (
  contact_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  main_organization_location_selector int(11) unsigned DEFAULT '0' NOT NULL,
  first_name varchar(255),
  middle_name varchar(255),
  last_name varchar(255),
  last_name_prefix varchar(10),
  initials char(6),
  location varchar(255),
  nickname varchar(255),
  profession varchar(255),
  suffix varchar(20),
  title varchar(20),
  email_selector int(11) DEFAULT '0' NOT NULL,
  web_selector int(11) DEFAULT '0' NOT NULL,
  mailing_address_selector int(11) DEFAULT '0' NOT NULL,
  phone1_selector int(11) DEFAULT '0' NOT NULL,
  phone2_selector int(11) DEFAULT '0' NOT NULL,
  phone3_selector int(11) DEFAULT '0' NOT NULL,
  phone4_selector int(11) DEFAULT '0' NOT NULL,
  phone5_selector int(11) DEFAULT '0' NOT NULL,
  phone6_selector int(11) DEFAULT '0' NOT NULL,
  phone7_selector int(11) DEFAULT '0' NOT NULL,
  phone8_selector int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (contact_id)
);
#
# Table structure for table 'contact_phone'
#
CREATE TABLE pgw_cdb_contact_phone (
  contact_phone_id int(11) unsigned DEFAULT '0' NOT NULL,
  contact_id int(11) unsigned DEFAULT '0' NOT NULL,
  phone_category_id int(11) unsigned NOT NULL,
  number char(100),
  PRIMARY KEY (contact_phone_id),
  KEY idx_contact_id (contact_id),
  KEY idx_phone_category_id (phone_category_id)
);

#
# Table structure for table 'contact_phone_category'
#
# This table has an entry for each type of telephone
# number.  An example of a type that is not used 
# below is College Telephone number.
#
CREATE TABLE pgw_cdb_contact_phone_category (
  phone_category_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  phone_category_name char(20) NOT NULL,
  PRIMARY KEY (phone_category_id)
);


#
# Default data for table 'contact_phone_category'
#

INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (1,'Business Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (2,'Business Phone2');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (3,'Business Pager');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (4,'Business Fax');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (5,'Home Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (6,'Home Phone2');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (7,'Home Pager');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (8,'Home Fax');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (9,'Other Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (10,'Other Pager');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (11,'Other Fax');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (12,'Call Back');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (13,'Car Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (14,'ISDN');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (15,'Mobile Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (16,'Primary Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (17,'Radio Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (18,'Telex');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (19,'TTY/TDD Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (20,'Family Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (21,'Vacation Home Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (22,'School Phone');
INSERT INTO pgw_cdb_contact_phone_category (phone_category_id, phone_category_name) VALUES (23,'Answering Service');

#
# Table structure for table 'organization'
# Companies can be catalogued and referenced as 
# individual entities.  Assuming that you have 
# multiple contacts in any organization, this should 
# save input time and errors.
#
# Minor issue: any given organization may have more 
# than one main phone for different locations.  
# Perhaps there should be tables giving location 
# lists for each company with different "main" 
# phone numbers for each location.  Each contact
# could then choose what location they are at...?
#
CREATE TABLE pgw_cdb_organization (
  organization_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  organization_name varchar(100) NOT NULL,
  organization_home_page text,
  organization_network_name varchar(100),
  organization_org_id_num varchar(20),
  PRIMARY KEY (organization_id)
);

#
# Table structure for table 'organization_location'
# This table contains location/address/phone info
# for organizations.  Any org. can have multiple
# locations.
CREATE TABLE pgw_cdb_organization_location (
  location_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  organization_id int(11) unsigned NOT NULL,
  location text,
  address_city varchar(255),
  address_country varchar(255),
  postal_code varchar(255),
  address_street varchar(255),
  number varchar(100),
  PRIMARY KEY (location_id),
  KEY (organization_id)
);


#
# Table structure for table 'organization_client'
# Organizations/Entities who are clients have their 
# own set of info.
#
CREATE TABLE pgw_cdb_organization_client (
  organization_id int(11) unsigned DEFAULT '0' NOT NULL,
  account text,
  billing_info text,
  customer_id varchar(20),
  referred_by text,
  PRIMARY KEY (organization_id)
);  

#
# Table structure for table 'organization_db'
#
CREATE TABLE pgw_cdb_organization_meta (
  organization_id int(11) unsigned DEFAULT '0' NOT NULL,
  status ENUM("active","pending","deleted") DEFAULT 'pending' NOT NULL,
  created int(11) DEFAULT '0' NOT NULL,
  modified int(11) DEFAULT '0' NOT NULL,
  file_as varchar(50) NOT NULL,
  followup_status int(11) unsigned DEFAULT '0' NOT NULL,
  created_by int(11) DEFAULT '0' NOT NULL,
  keywords text,
  group_access int(11) DEFAULT '0',
  version float(8,2),
  PRIMARY KEY (organization_id),
  KEY (file_as(4))
);  

#
# Table structure for table 'organization_notes'
# Notes are kept in separate tables in order to keep 
# the other tables optimized. 
#
CREATE TABLE pgw_cdb_organization_notes (
  organization_id int(11) unsigned DEFAULT '0' NOT NULL,
  note text,
  PRIMARY KEY (organization_id)
);

#
# Table structure for table 'contact_organization'
# This table is specific to a contact.  This is 
# contact-specific organization information like who 
# the contact's assistant is and such.
#
# For each link (below) between a contact and org
# there may be additional information such as an
# assistant's name and department and such.
#
CREATE TABLE pgw_cdb_contact_organization (
  organization_link_id int(11) unsigned DEFAULT '0' NOT NULL,
  organization_location_id int(11) unsigned DEFAULT '0' NOT NULL,
  contact_id int(11) unsigned DEFAULT '0' NOT NULL,
  assistant_name varchar(255),
  assistant_phone varchar(100),
  assistant_email varchar(50),
  department varchar(255),
  job_title varchar(255),
  manager_name varchar(255),
  PRIMARY KEY (organization_link_id)
);  


#
# Table structure for table 'contact_notes'
# Again, different database for notes for 
# optimization purposes.
#
CREATE TABLE pgw_cdb_contact_notes (
  contact_id int(11) unsigned DEFAULT '0' NOT NULL,
  note text,
  PRIMARY KEY (contact_id)
);

#
# Table structure for table 'contact_client'
# Contacts who are clients have their own set 
# of info.
#
CREATE TABLE pgw_cdb_contact_client (
  contact_id int(11) unsigned DEFAULT '0' NOT NULL,
  account text,
  billing_info text,
  customer_id varchar(20),
  referred_by text,
  PRIMARY KEY (contact_id)
);  

#
# Table structure for table 'contact_personal'
# Contact's personal information
#'
CREATE TABLE pgw_cdb_contact_personal (
  contact_id int(11) unsigned DEFAULT '0' NOT NULL,
  birthday int(11),
  children text,
  gender ENUM("unspecified","male","female"),
  gov_id_num varchar(20),
  hobbies text,
  language int(11),
  spouse text,
  anniversary int(11),
  PRIMARY KEY (contact_id)
);  

#
# Table structure for table 'language'
# List of languages for future international
# compatibility and international contacts.
#
CREATE TABLE pgw_cdb_language (
  language_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  language_name char(40) NOT NULL,
  language_code char(10) NOT NULL,
  PRIMARY KEY (language_id),
  KEY (language_code),
  UNIQUE (language_name)
);  

#
# Populate the pgw_cdb_language table
#
# --- This has been moved to a separate file: cdb_language.sql ---
#

#
# Table structure for table 'category'
#
CREATE TABLE pgw_cdb_category (
  category_id int(11) unsigned DEFAULT '0' NOT NULL,
  name char(40) NOT NULL,
  PRIMARY KEY (category_id),
  UNIQUE (name)
);  

#
# Table structure for table 'category'
#
CREATE TABLE pgw_cdb_category_link (
  category_link_id int(11) unsigned DEFAULT '0' NOT NULL,
  category_id int(11) unsigned DEFAULT '0' NOT NULL,
  entity_id int(11) unsigned DEFAULT '0' NOT NULL,
  contact_type ENUM("individual","organization") DEFAULT 'individual' NOT NULL,
  PRIMARY KEY (category_link_id),
  KEY (entity_id),
  KEY (category_id),
  KEY (contact_type)
);  

#
# Table structure for table 'contact_db'
#
CREATE TABLE pgw_cdb_contact_meta (
  contact_id int(11) unsigned DEFAULT '0' NOT NULL,
  status ENUM("active","pending","deleted") DEFAULT 'pending' NOT NULL,
  created int(11) DEFAULT '0' NOT NULL,
  modified int(11) DEFAULT '0' NOT NULL,
  file_as varchar(50) NOT NULL,
  followup_status int(11) unsigned DEFAULT '0' NOT NULL,
  created_by int(11) DEFAULT '0' NOT NULL,
  keywords text,
  group_access int(11) DEFAULT '0',
  version float(8,2),
  ldap_sync int(11) DEFAULT '0',
  ldap_dn varchar(100),
  PRIMARY KEY (contact_id),
  KEY (file_as(4))
);  

#
# Table structure for table 'contact_flag'
# List of choices for different status flags.
# Each person can make their own set of status flags
# and these flags will act like Eudora labels.
# When flagged, the contact row will be colored
# with the color of the flag.
#
CREATE TABLE pgw_cdb_contact_flag (
  flag_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  user_id int(11) DEFAULT '0' NOT NULL,
  flag_name char(40),
  flag_color char(6),
  PRIMARY KEY (flag_id),
  KEY idx_user_id (user_id)
);

CREATE TABLE pgw_cdb_contact_flag_link (
  flag_link_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  user_id int(11) DEFAULT '0' NOT NULL,
  contact_id int(11) DEFAULT '0' NOT NULL,
  flag_id int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (flag_link_id),
  KEY idx_user_id (user_id),
  KEY idx_contact_id (contact_id)
);

#
# Table structure for table 'contact_followup'
# Flag contacts for later followup.  There will
# be a marker next to their name in the color specified.
#
CREATE TABLE pgw_cdb_contact_followup (
  followup_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  followup_name char(20),
  followup_color char(6),
  PRIMARY KEY (followup_id)
);

#
# Default data for table 'contact_phone_category'
#

INSERT INTO pgw_cdb_contact_followup (followup_id, followup_name, followup_color) VALUES (1,'Follow Up', 'FF2222'); #red
INSERT INTO pgw_cdb_contact_followup (followup_id, followup_name, followup_color) VALUES (2,'For Your Information', '6699cc'); #blue
INSERT INTO pgw_cdb_contact_followup (followup_id, followup_name, followup_color) VALUES (3,'Review', '83d8a4'); #green
INSERT INTO pgw_cdb_contact_followup (followup_id, followup_name, followup_color) VALUES (4,'Call', 'cccc99'); #gold
INSERT INTO pgw_cdb_contact_followup (followup_id, followup_name, followup_color) VALUES (5,'Arrange Meeting', 'CC99CC'); #purple
INSERT INTO pgw_cdb_contact_followup (followup_id, followup_name, followup_color) VALUES (6,'Send E-mail', 'FF9966'); #orange
INSERT INTO pgw_cdb_contact_followup (followup_id, followup_name, followup_color) VALUES (7,'Send Letter', 'FFFF33'); #yellow


#
# Table structure for table 'contact_internet'
#
CREATE TABLE pgw_cdb_contact_internet (
  contact_id int(11) unsigned DEFAULT '0' NOT NULL,
  email_home char(50),
  email_home_display char(50),
  email_business char(50),
  email_business_display char(50),
  email_other char(50),
  email_other_display char(50),
  ftp char(100),
  free_busy_address char(100),
  personal_home_page char(100),
  business_home_page char(100),
  other_home_page char(100),
  send_as_plain_text int(11) DEFAULT '1' NOT NULL,
  icq char(20),
  PRIMARY KEY (contact_id)
);

#
# Table structure for table 'contact_address_category'
#
# This table has an entry for each type of address.
# For example, Business, Vacation, Family, etc.
#
CREATE TABLE pgw_cdb_contact_address_category (
  address_category_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  address_category_name char(20) NOT NULL,
  PRIMARY KEY (address_category_id)
);


#
# Default data for table 'contact_address_category'
#

INSERT INTO pgw_cdb_contact_address_category (address_category_id, address_category_name) VALUES (1,'Business Address');
INSERT INTO pgw_cdb_contact_address_category (address_category_id, address_category_name) VALUES (2,'Home Address');
INSERT INTO pgw_cdb_contact_address_category (address_category_id, address_category_name) VALUES (3,'Other Address');
INSERT INTO pgw_cdb_contact_address_category (address_category_id, address_category_name) VALUES (4,'Family Address');
INSERT INTO pgw_cdb_contact_address_category (address_category_id, address_category_name) VALUES (5,'Vacation Home Address');

#
# Table structure for table 'contact_address'
#
CREATE TABLE pgw_cdb_contact_address (
  contact_address_id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  contact_id int(11) unsigned DEFAULT '0' NOT NULL,
  address_category_id int(11) NOT NULL,
  address_city varchar(255),
  address_country varchar(255),
  postal_code varchar(255),
  address_street varchar(255),
  PRIMARY KEY (contact_address_id),
  KEY (contact_id),
  KEY (address_category_id)
);
