MySQL setup of a simple Party Model-based address book.

parties_import.sql

#
# Basic Party Model implementation for MySQL.
# - Parties are either People or Organizations.
# - Party names are tracked separately, including first_used and last_used as dates.
# - Parties can have e-mails addresses, phone numbers, and physical addresses.
# - Parties can have Relationships with one another.
#
# 
# Party Model: http://www.tdan.com/view-articles/5014/
# SO answer that helped me get started: http://stackoverflow.com/questions/5466163/same-data-from-different-entities-in-database-best-practice-phone-numbers-ex/5471265#5471265
# 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Tables

DROP TABLE IF EXISTS `party`;

CREATE TABLE `party` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` char(1) NOT NULL DEFAULT 'P',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_locator_email`;

CREATE TABLE `party_locator_email` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `party_id` int(11) unsigned NOT NULL,
  `address` varchar(254) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `party_id` (`party_id`),
  CONSTRAINT `party_locator_email_ibfk_1` FOREIGN KEY (`party_id`) REFERENCES `party` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_locator_phone`;

CREATE TABLE `party_locator_phone` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `party_id` int(11) unsigned NOT NULL,
  `type` char(1) NOT NULL DEFAULT 'm' COMMENT 'm=mobile, h=home, w=work, f=fax, p=pager, e=emergency',
  `number` varchar(64) NOT NULL DEFAULT '',
  `extension` varchar(16) DEFAULT NULL,
  `first_used` date DEFAULT NULL,
  `last_used` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `party_id` (`party_id`),
  CONSTRAINT `party_locator_phone_ibfk_1` FOREIGN KEY (`party_id`) REFERENCES `party` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_locator_physical`;

CREATE TABLE `party_locator_physical` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `party_id` int(11) unsigned NOT NULL,
  `country_code` char(2) DEFAULT 'us',
  `street` varchar(255) DEFAULT NULL,
  `street_secondary` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `subdivision_id` int(11) unsigned DEFAULT NULL,
  `postal_code` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `party_id` (`party_id`),
  KEY `subdivision_id` (`subdivision_id`),
  CONSTRAINT `party_locator_physical_ibfk_1` FOREIGN KEY (`party_id`) REFERENCES `party` (`id`) ON DELETE CASCADE,
  CONSTRAINT `party_locator_physical_ibfk_2` FOREIGN KEY (`subdivision_id`) REFERENCES `party_locator_physical_subdivision` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_locator_physical_subdivision`;

CREATE TABLE `party_locator_physical_subdivision` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country_code` char(2) NOT NULL DEFAULT 'us',
  `code` char(2) DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_name`;

CREATE TABLE `party_name` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `party_id` int(11) unsigned NOT NULL,
  `salutation` varchar(255) DEFAULT NULL,
  `surname` varchar(255) NOT NULL DEFAULT '',
  `rest_of_the_name` varchar(255) DEFAULT NULL,
  `first_used` date DEFAULT NULL,
  `last_used` date DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `party_id` (`party_id`),
  CONSTRAINT `party_name_ibfk_1` FOREIGN KEY (`party_id`) REFERENCES `party` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_relationship`;

CREATE TABLE `party_relationship` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `relationship_type_id` int(11) unsigned NOT NULL,
  `first_party_id` int(11) unsigned NOT NULL,
  `second_party_id` int(11) unsigned NOT NULL,
  `begin_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `relationship_type_id` (`relationship_type_id`),
  KEY `first_party_id` (`first_party_id`),
  KEY `second_party_id` (`second_party_id`),
  CONSTRAINT `party_relationship_ibfk_1` FOREIGN KEY (`relationship_type_id`) REFERENCES `party_relationship_type` (`id`) ON DELETE CASCADE,
  CONSTRAINT `party_relationship_ibfk_2` FOREIGN KEY (`first_party_id`) REFERENCES `party` (`id`) ON DELETE CASCADE,
  CONSTRAINT `party_relationship_ibfk_3` FOREIGN KEY (`second_party_id`) REFERENCES `party` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_relationship_type`;

CREATE TABLE `party_relationship_type` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `first_role_name` varchar(255) DEFAULT NULL,
  `second_role_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_type_organization`;

CREATE TABLE `party_type_organization` (
  `party_id` int(11) unsigned NOT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`party_id`),
  CONSTRAINT `party_type_organization_ibfk_1` FOREIGN KEY (`party_id`) REFERENCES `party` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `party_type_person`;

CREATE TABLE `party_type_person` (
  `party_id` int(11) unsigned NOT NULL,
  `gender` varchar(255) DEFAULT '',
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`party_id`),
  CONSTRAINT `party_type_person_ibfk_1` FOREIGN KEY (`party_id`) REFERENCES `party` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- Views

DROP VIEW IF EXISTS `vw_party_name_last_usage_joiner`;
CREATE VIEW `vw_party_name_last_usage_joiner` AS
SELECT party_name.id AS party_name_id,
party_id,
MAX(`last_used`) AS latest_usage
FROM `party_name`
GROUP BY party_name.party_id;

DROP VIEW IF EXISTS `vw_party_names`;
CREATE VIEW `vw_party_names` AS
SELECT N.*,
CONCAT(N.salutation, IF(ISNULL(N.salutation), '', ' '), N.rest_of_the_name, IF(ISNULL(N.rest_of_the_name), '', ' '), N.surname) AS `name`
FROM party_name N
INNER JOIN vw_party_name_last_usage_joiner
ON N.party_id = vw_party_name_last_usage_joiner.party_id
AND N.last_used = vw_party_name_last_usage_joiner.latest_usage
GROUP BY N.party_id;

DROP VIEW IF EXISTS `vw_party_addresses`;
CREATE VIEW `vw_party_addresses` AS
SELECT
PA.id,
PA.country_code,
PA.street,
PA.street_secondary,
PA.city,
PA.subdivision_id,
PSD.code AS subdivision_code,
PSD.name AS subdivision_name,
PA.postal_code
FROM party_locator_physical PA
LEFT JOIN party_locator_physical_subdivision PSD
ON PA.subdivision_id = PSD.id
GROUP BY PA.id;

DROP VIEW IF EXISTS `vw_people`;
CREATE VIEW `vw_people` AS
SELECT
	party.id,
	N.name AS name,
	N.salutation,
	N.surname,
	N.rest_of_the_name,
	PTP.birth,
	PTP.gender,
	party.created_at,
	party.updated_at
FROM party
INNER JOIN party_type_person PTP
ON PTP.party_id = party.id
LEFT JOIN `vw_party_names` N
ON N.party_id = party.id
GROUP BY party.id;

DROP VIEW IF EXISTS `vw_organizations`;
CREATE VIEW `vw_organizations` AS
SELECT
	party.id,
	N.surname AS `name`,
	PTO.birth,
	party.created_at,
	party.updated_at
FROM party
INNER JOIN party_type_organization PTO
ON PTO.party_id = party.id
LEFT JOIN `vw_party_names` N
ON N.party_id = party.id;

DROP VIEW IF EXISTS `vw_relationships`;
CREATE VIEW `vw_relationships` AS
SELECT
PR.id,
PR.first_party_id,
FP.type AS first_party_type,
PRT.first_role_name,
PRT.name AS relationship_name,
PR.second_party_id,
SP.type AS second_party_type,
PRT.second_role_name,
PR.begin_date,
PR.end_date
FROM party_relationship PR
LEFT JOIN party_relationship_type PRT
ON PRT.id = PR.relationship_type_id
LEFT JOIN party FP
ON FP.id = PR.first_party_id
LEFT JOIN party SP
ON SP.id = PR.second_party_id
GROUP BY PR.id;


/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;