MySQL Create Contacts Table Script & Related Look-up Tables

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, 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 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 '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_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.  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)

);

There is also a separate page for the MySQL create state table script.

HTH,
Kim