#
# Create a MySQL Contacts Table.  2007-05  http://kimbriggs.com/
#
# Meant to be all-inlcusive contacts.  Businesses, internal, customers, etc.
# Table can hold 4,300,000,000 records (4.3 billion)
# Handles contact couples with comletely different names at same address
# Has No/Yes fields for receiving mail, emails, and phone calls
# This table refers to 2 other tables: Contact Type and Referral Type
#

drop table if exists contacts;


create table contacts
(
contact_id         int              unsigned   not null auto_increment comment 'PK: Unique contact ID',
contact_type_id    smallint         unsigned   comment 'FK: contact_type table (donor, business, etc.)',
referral_type_id   smallint         unsigned   comment 'FK: referral_type table (website, mailing, etc.)',
title1             varchar(8)       comment 'Primary contact title (Mr., Dr., etc.)',	
first_name1        varchar(32)      comment 'Primary contact first name',
mid_name1          varchar(32)      comment 'Primary contact middle name or initial',
last_name1         varchar(32)      comment 'Primary contact last name',
title2             varchar(8)       comment 'Secondary contact title (Mr., Dr., etc.)',
first_name2        varchar(32)      comment 'Secondary contact first name',
mid_name2          varchar(32)      comment 'Secondary contact middle name or initial',
last_name2         varchar(32)      comment 'Secondary contact last name',
organization       varchar(32)      comment 'Business or organization name',
address1           varchar(64)      comment 'First line of address, usually required',
address2           varchar(32)      comment 'Second line of address, usually optional',
city               varchar(32)      comment 'City or town',
state              varchar(32)      comment 'State or provence',
postal_code        varchar(16)      comment 'Postal code like US zip code',
country            varchar(32)      comment 'Country name, use US for United States',
phone1             varchar(32)      comment 'Contact choice for primary phone',
phone2             varchar(32)      comment 'Contact choice for secondary phone',
email              varchar(64)      comment 'Primary email address',
date_created       timestamp        comment 'Date record created.  Insert NULL for current timestamp',
referrer           varchar(64)      comment 'Name of referring person, site, publication, etc.',
notes              text             comment 'Freeform text field',
receive_mail       varchar(8)       default 'No'   comment 'Receive mailings via post',
receive_email      varchar(8)       default 'No'   comment 'Receive emails',
receive_calls      varchar(8)       default 'No'   comment 'Receive telephone calls at primary phone',

primary key (contact_id)
);