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
# 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)
);
- Here are the SQL files and link to the states table:
- MySQL Create Contacts Table
- MySQL Contacts Type Look-up Table
- MySQL Referral Type Look-up Table
There is also a separate page for the MySQL Create US State Table Script.