MySQL Create Contacts Table Script

Summary

Everybody needs a contacts table sooner or later. There is nothing that special about this one, but it is a place to start. Mostly likely, you will need to add and/or subtract field names. The straight SQL files are linked below, including the three look-up tables referenced in the contacts table.

  # Create a MySQL Contacts Table.
  # 2007-05 http://kimbriggs.com/
  #
  # Meant to be all-inlcusive contacts. Businesses, 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
  # Table refers to 3 other tables: Contact Type, Referral Type, and State
  #
  drop table if exists contacts;
  
  create table contacts
  (
  contact_id        int unsigned not null auto_increment comment 'Unique Key',
  contact_type_id   smallint unsigned comment 'FK: contact_type table',
  referral_type_id  smallint unsigned comment 'FK: referral_type table',
  title1            varchar(8)  comment 'Primary contact title (Mr., etc.)',
  first_name1       varchar(32) comment 'Primary contact first name',
  mid_name1         varchar(32) comment 'Primary contact middle name',
  last_name1        varchar(32) comment 'Primary contact last name',
  title2            varchar(8)  comment 'Secondary contact title (Mr., etc.)',
  first_name2       varchar(32) comment 'Secondary contact first name',
  mid_name2         varchar(32) comment 'Secondary contact middle name',
  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, usu required',
  address2          varchar(32) comment 'Second line of address, optional',
  city              varchar(32) comment 'City or town',
  state_id          varchar(32) comment 'FK: state table, 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. Use NULL timestamp',
  referrer          varchar(64) comment 'Name of referring person, site, 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 calls okay',
  primary key (contact_id)
  );

There is also a separate page for the MySQL Create US State Table Script.

Here is my idea for a Contact Type table.

#
# Create a MySQL Contact Type Look-up Table.  
#
# 2007-05  http://kimbriggs.com
#
# Table can hold over 65,000 contact types
# Describes the different kinds of entries in a Contacts table
# Add and/or subtract lines in insert statement to match requirements
#


drop table if exists contact_type;


create table contact_type
(
contact_type_id   smallint       unsigned not null auto_increment comment 'PK: Unique contact type ID',
contact_type      varchar(32)    not null	comment 'Contact type (donor, volunteer, business, etc.)',
contact_type_abbr varchar(8)     comment 'Optional abbreviation for contact type',

primary key (contact_type_id)
);


insert into contact_type
(contact_type_id, contact_type)
values
(NULL, 'OTHER'),
(NULL, 'donor'),
(NULL, 'sponsor'),
(NULL, 'volunteer'),
(NULL, 'attendee'),
(NULL, 'committee_member'),
(NULL, 'business'),
(NULL, 'professional'),
(NULL, 'press'),
(NULL, 'corporate'),
(NULL, 'foundation'),
(NULL, 'government'),
(NULL, 'subscriber'),
(NULL, 'employee'),
(NULL, 'partner'),
(NULL, 'board_member')
;