Schema

**Below is a schema dump of the pellucid database.  It contains details regarding the creation of tables and formatting of pellucid.

-- phpMyAdmin SQL Dump
-- version 3.3.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 19, 2014 at 01:32 PM
-- Server version: 5.1.40
-- PHP Version: 5.2.12

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `pellucid2`
--

-- --------------------------------------------------------

--
-- Table structure for table `entities`
--

CREATE TABLE IF NOT EXISTS `entities` (
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=241697 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_acos`
--

CREATE TABLE IF NOT EXISTS `entity_acos` (
`entity_id` int(10) NOT NULL DEFAULT '0',
`local_id` varchar(10) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`entity_type` varchar(255) DEFAULT NULL,
`classification` varchar(255) DEFAULT NULL,
`date_start` date DEFAULT NULL,
`counties_served` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(30) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`phonenumber` varchar(46) DEFAULT NULL,
`geo_lat` float DEFAULT NULL,
`geo_long` float DEFAULT NULL,
`website` varchar(255) DEFAULT NULL,
`contact_ipro` varchar(255) DEFAULT NULL,
`contact_cmwf` varchar(255) DEFAULT NULL,
`summary_detail` varchar(255) DEFAULT NULL,
`estimated_members` varchar(255) DEFAULT NULL,
`estimated_physicians` varchar(255) DEFAULT NULL,
`physician_details` varchar(255) DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`entity_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `entity_ascs`
--

CREATE TABLE IF NOT EXISTS `entity_ascs` (
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`aha_id` varchar(7) DEFAULT NULL,
`mpn_id` varchar(15) DEFAULT NULL,
`npi_id` int(10) unsigned DEFAULT NULL,
`local_id` varchar(15) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`address` varchar(60) DEFAULT NULL,
`city` varchar(30) DEFAULT NULL,
`state` enum('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','UT','VA','VI','VT','WA','WI','WV','WY','US') DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(25) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`phone_number` varchar(15) DEFAULT NULL,
`ownership` varchar(100) DEFAULT NULL,
`ipro_cms_ownership_group` varchar(50) DEFAULT NULL,
`aha_ownership` varchar(50) DEFAULT NULL,
`aha_ownership_code` varchar(255) DEFAULT NULL,
`ipro_aha_ownership_group` varchar(50) DEFAULT NULL,
`entity_type` enum('comparator','hrr','system','entity') NOT NULL DEFAULT 'entity',
`geo_lat` double DEFAULT NULL,
`geo_long` double DEFAULT NULL,
`hsa_code` varchar(255) DEFAULT NULL,
`hrr_code` varchar(255) DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=241163 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_asc_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_asc_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` enum('designation') NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`attribute_id`),
KEY `entity_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_calculations`
--

CREATE TABLE IF NOT EXISTS `entity_calculations` (
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`unique_id` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`entity_type` enum('average','median','mode','threshold','total') NOT NULL DEFAULT 'average',
`closed_date` date DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=215004 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_calculation_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_calculation_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`attribute_id`),
KEY `entity_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_clinics`
--

CREATE TABLE IF NOT EXISTS `entity_clinics` (
`entity_id` int(10) DEFAULT NULL,
`entity_hospital_id` int(10) DEFAULT NULL,
`local_id` varchar(4) DEFAULT NULL,
`type` varchar(25) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(2) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(255) DEFAULT NULL,
`county_fips` varchar(3) DEFAULT NULL,
`phone_number` varchar(25) DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `entity_clinic_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_clinic_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`source` varchar(64) DEFAULT NULL,
PRIMARY KEY (`attribute_id`),
KEY `hospital_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3602 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_geographies`
--

CREATE TABLE IF NOT EXISTS `entity_geographies` (
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`unique_id` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`state` enum('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','UT','VA','VI','VT','WA','WI','WV','WY','US','AE','AA','AP','PW','FM','MH','AS') DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`county_name` varchar(30) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`entity_type` varchar(50) NOT NULL DEFAULT 'state',
`geo_lat` double DEFAULT NULL,
`geo_long` double DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=223999 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_geography_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_geography_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`attribute_id`),
KEY `entity_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000000114 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_home_healths`
--

CREATE TABLE IF NOT EXISTS `entity_home_healths` (
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`mpn_id` varchar(15) DEFAULT NULL,
`local_id` varchar(15) DEFAULT NULL,
`local_id2` varchar(15) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` enum('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','US','UT','VA','VI','VT','WA','WI','WV','WY') DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(30) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`region` varchar(25) DEFAULT '',
`phone_number` varchar(46) DEFAULT NULL,
`fax_number` varchar(25) DEFAULT NULL,
`url` varchar(200) DEFAULT NULL,
`entity_type` varchar(25) DEFAULT NULL,
`service_id` varchar(255) DEFAULT NULL,
`ipro_ownership_group` varchar(100) DEFAULT NULL,
`ownership` varchar(100) DEFAULT NULL,
`operator_address` varchar(255) DEFAULT NULL,
`operator_city` varchar(255) DEFAULT NULL,
`operator_state` enum('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','US','UT','VA','VI','VT','WA','WI','WV','WY') DEFAULT NULL,
`operator_zipcode` varchar(5) DEFAULT NULL,
`specialties` varchar(50) DEFAULT NULL,
`aa_br` smallint(6) NOT NULL DEFAULT '0',
`aa_cb` tinyint(4) DEFAULT '0',
`aa_sn` tinyint(4) DEFAULT '0',
`aa_da` varchar(25) DEFAULT NULL,
`opened_date` date NOT NULL DEFAULT '0000-00-00',
`closed_date` date DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`),
KEY `hh_pfi_idx` (`entity_type`,`local_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=241576 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_home_health_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_home_health_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`attribute_id`),
KEY `hospital_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=36309 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_hospitals`
--

CREATE TABLE IF NOT EXISTS `entity_hospitals` (
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`aha_id` varchar(7) DEFAULT NULL,
`mpn_id` varchar(15) DEFAULT NULL,
`npi_id` varchar(10) DEFAULT NULL,
`local_id` varchar(15) DEFAULT NULL,
`aha_sys_id` varchar(4) DEFAULT NULL,
`aha_service_id` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` enum('AK','AL','AS','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','US','UT','VA','VI','VT','WA','WI','WV','WY') DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(30) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`phone_number` varchar(46) DEFAULT NULL,
`ownership` varchar(100) DEFAULT NULL,
`ipro_cms_ownership_group` varchar(50) DEFAULT NULL,
`aha_ownership` varchar(50) DEFAULT NULL,
`aha_ownership_code` varchar(255) DEFAULT NULL,
`ipro_aha_ownership_group` varchar(50) DEFAULT NULL,
`entity_type` enum('comparator','hrr','system','entity','Critical Access Hospitals') NOT NULL DEFAULT 'entity',
`hsa_code` varchar(5) DEFAULT NULL,
`hrr_code` varchar(3) DEFAULT NULL,
`geo_lat` float DEFAULT NULL,
`geo_long` float DEFAULT NULL,
`aa_jc` tinyint(1) DEFAULT '0',
`aa_cp` tinyint(1) DEFAULT '0',
`aa_rt` tinyint(1) DEFAULT '0',
`aa_ms` tinyint(1) DEFAULT '0',
`aa_ns` tinyint(1) DEFAULT '0',
`aa_ca` tinyint(1) DEFAULT '0',
`aa_co` tinyint(1) DEFAULT '0',
`aa_bc` tinyint(1) DEFAULT '0',
`aa_md` tinyint(1) DEFAULT '0',
`aa_ao` tinyint(1) DEFAULT '0',
`aa_cc` tinyint(1) DEFAULT '0',
`aa_hs` tinyint(1) DEFAULT '0',
`aa_mc` tinyint(1) DEFAULT '0',
`aa_sn` tinyint(1) DEFAULT '0',
`aa_ph` tinyint(1) DEFAULT '0',
`closed_date` date DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=241180 ;

--
-- Triggers `entity_hospitals`
--
DROP TRIGGER IF EXISTS `entity_hospitals_history_trigger`;
DELIMITER //
CREATE TRIGGER `entity_hospitals_history_trigger` BEFORE UPDATE ON `entity_hospitals`
FOR EACH ROW BEGIN
SET @hasChange=0;
IF COALESCE(OLD.aha_id, '') != COALESCE(NEW.aha_id, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aha_id',
OLD.aha_id,
NEW.aha_id,
User()
);
END IF;
IF COALESCE(OLD.mpn_id, '') != COALESCE(NEW.mpn_id, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'mpn_id',
OLD.mpn_id,
NEW.mpn_id,
User()
);
END IF;
IF COALESCE(OLD.npi_id, '') != COALESCE(NEW.npi_id, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'npi_id',
OLD.npi_id,
NEW.npi_id,
User()
);
END IF;
IF COALESCE(OLD.local_id, '') != COALESCE(NEW.local_id, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'local_id',
OLD.local_id,
NEW.local_id,
User()
);
END IF;
IF COALESCE(OLD.aha_sys_id, '') != COALESCE(NEW.aha_sys_id, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aha_sys_id',
OLD.aha_sys_id,
NEW.aha_sys_id,
User()
);
END IF;
IF COALESCE(OLD.aha_service_id, '') != COALESCE(NEW.aha_service_id, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aha_service_id',
OLD.aha_service_id,
NEW.aha_service_id,
User()
);
END IF;
IF COALESCE(OLD.name, '') != COALESCE(NEW.name, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'name',
OLD.name,
NEW.name,
User()
);
END IF;
IF COALESCE(OLD.aka, '') != COALESCE(NEW.aka, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aka',
OLD.aka,
NEW.aka,
User()
);
END IF;
IF COALESCE(OLD.address, '') != COALESCE(NEW.address, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'address',
OLD.address,
NEW.address,
User()
);
END IF;
IF COALESCE(OLD.city, '') != COALESCE(NEW.city, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'city',
OLD.city,
NEW.city,
User()
);
END IF;
IF COALESCE(OLD.state, '') != COALESCE(NEW.state, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'state',
OLD.state,
NEW.state,
User()
);
END IF;
IF COALESCE(OLD.state_fips, '') != COALESCE(NEW.state_fips, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'state_fips',
OLD.state_fips,
NEW.state_fips,
User()
);
END IF;
IF COALESCE(OLD.zipcode, '') != COALESCE(NEW.zipcode, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'zipcode',
OLD.zipcode,
NEW.zipcode,
User()
);
END IF;
IF COALESCE(OLD.county_name, '') != COALESCE(NEW.county_name, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'county_name',
OLD.county_name,
NEW.county_name,
User()
);
END IF;
IF COALESCE(OLD.county_fips, '') != COALESCE(NEW.county_fips, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'county_fips',
OLD.county_fips,
NEW.county_fips,
User()
);
END IF;
IF COALESCE(OLD.phone_number, '') != COALESCE(NEW.phone_number, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'phone_number',
OLD.phone_number,
NEW.phone_number,
User()
);
END IF;
IF COALESCE(OLD.ownership, '') != COALESCE(NEW.ownership, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'ownership',
OLD.ownership,
NEW.ownership,
User()
);
END IF;
IF COALESCE(OLD.ipro_cms_ownership_group, '') != COALESCE(NEW.ipro_cms_ownership_group, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'ipro_cms_ownership_group',
OLD.ipro_cms_ownership_group,
NEW.ipro_cms_ownership_group,
User()
);
END IF;
IF COALESCE(OLD.aha_ownership, '') != COALESCE(NEW.aha_ownership, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aha_ownership',
OLD.aha_ownership,
NEW.aha_ownership,
User()
);
END IF;
IF COALESCE(OLD.aha_ownership_code, '') != COALESCE(NEW.aha_ownership_code, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aha_ownership_code',
OLD.aha_ownership_code,
NEW.aha_ownership_code,
User()
);
END IF;
IF COALESCE(OLD.ipro_aha_ownership_group, '') != COALESCE(NEW.ipro_aha_ownership_group, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'ipro_aha_ownership_group',
OLD.ipro_aha_ownership_group,
NEW.ipro_aha_ownership_group,
User()
);
END IF;
IF COALESCE(OLD.entity_type, '') != COALESCE(NEW.entity_type, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'entity_type',
OLD.entity_type,
NEW.entity_type,
User()
);
END IF;
IF COALESCE(OLD.hsa_code, '') != COALESCE(NEW.hsa_code, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'hsa_code',
OLD.hsa_code,
NEW.hsa_code,
User()
);
END IF;
IF COALESCE(OLD.hrr_code, '') != COALESCE(NEW.hrr_code, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'hrr_code',
OLD.hrr_code,
NEW.hrr_code,
User()
);
END IF;
IF COALESCE(OLD.geo_lat, '') != COALESCE(NEW.geo_lat, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'geo_lat',
OLD.geo_lat,
NEW.geo_lat,
User()
);
END IF;
IF COALESCE(OLD.geo_long, '') != COALESCE(NEW.geo_long, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'geo_long',
OLD.geo_long,
NEW.geo_long,
User()
);
END IF;
IF COALESCE(OLD.aa_jc, '') != COALESCE(NEW.aa_jc, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_jc',
OLD.aa_jc,
NEW.aa_jc,
User()
);
END IF;
IF COALESCE(OLD.aa_cp, '') != COALESCE(NEW.aa_cp, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_cp',
OLD.aa_cp,
NEW.aa_cp,
User()
);
END IF;
IF COALESCE(OLD.aa_rt, '') != COALESCE(NEW.aa_rt, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_rt',
OLD.aa_rt,
NEW.aa_rt,
User()
);
END IF;
IF COALESCE(OLD.aa_ms, '') != COALESCE(NEW.aa_ms, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_ms',
OLD.aa_ms,
NEW.aa_ms,
User()
);
END IF;
IF COALESCE(OLD.aa_ns, '') != COALESCE(NEW.aa_ns, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_ns',
OLD.aa_ns,
NEW.aa_ns,
User()
);
END IF;

IF COALESCE(OLD.aa_ca, '') != COALESCE(NEW.aa_ca, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_ca',
OLD.aa_ca,
NEW.aa_ca,
User()
);
END IF;
IF COALESCE(OLD.aa_co, '') != COALESCE(NEW.aa_co, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_co',
OLD.aa_co,
NEW.aa_co,
User()
);
END IF;
IF COALESCE(OLD.aa_bc, '') != COALESCE(NEW.aa_bc, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_bc',
OLD.aa_bc,
NEW.aa_bc,
User()
);
END IF;
IF COALESCE(OLD.aa_md, '') != COALESCE(NEW.aa_md, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_md',
OLD.aa_md,
NEW.aa_md,
User()
);
END IF;
IF COALESCE(OLD.aa_ao, '') != COALESCE(NEW.aa_ao, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_ao',
OLD.aa_ao,
NEW.aa_ao,
User()
);
END IF;
IF COALESCE(OLD.aa_cc, '') != COALESCE(NEW.aa_cc, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_cc',
OLD.aa_cc,
NEW.aa_cc,
User()
);
END IF;
IF COALESCE(OLD.aa_hs, '') != COALESCE(NEW.aa_hs, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_hs',
OLD.aa_hs,
NEW.aa_hs,
User()
);
END IF;
IF COALESCE(OLD.aa_mc, '') != COALESCE(NEW.aa_mc, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_mc',
OLD.aa_mc,
NEW.aa_mc,
User()
);
END IF;
IF COALESCE(OLD.aa_sn, '') != COALESCE(NEW.aa_sn, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_sn',
OLD.aa_sn,
NEW.aa_sn,
User()
);
END IF;
IF COALESCE(OLD.aa_ph, '') != COALESCE(NEW.aa_ph, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'aa_ph',
OLD.aa_ph,
NEW.aa_ph,
User()
);
END IF;
IF COALESCE(OLD.closed_date, '') != COALESCE(NEW.closed_date, '')
THEN
SET @hasChange=1;
INSERT INTO entity_hospitals_update_hist_detail
(
entity_id,
col_name,
old_value,
new_value,
updated_by
)
VALUES
(
NEW.entity_id,
'closed_date',
OLD.closed_date,
NEW.closed_date,
User()
);
END IF;

IF @hasChange = 1
THEN
INSERT INTO entity_hospitals_update_hist
(
`entity_id`,
`aha_id`,
`mpn_id`,
`npi_id`,
`local_id`,
`aha_sys_id`,
`aha_service_id`,
`name`,
`aka`,
`address`,
`city`,
`state`,
`state_fips`,
`zipcode`,
`county_name`,
`county_fips`,
`phone_number`,
`ownership`,
`ipro_cms_ownership_group`,
`aha_ownership`,
`aha_ownership_code`,
`ipro_aha_ownership_group`,
`entity_type`,
`hsa_code`,
`hrr_code`,
`geo_lat`,
`geo_long`,
`aa_jc`,
`aa_cp`,
`aa_rt`,
`aa_ms`,
`aa_ns`,
`aa_ca`,
`aa_co`,
`aa_bc`,
`aa_md`,
`aa_ao`,
`aa_cc`,
`aa_hs`,
`aa_mc`,
`aa_sn`,
`aa_ph`,
`closed_date`,
`start_date`,
`end_date`
)
VALUES
(
OLD.entity_id,
OLD.aha_id,
OLD.mpn_id,
OLD.npi_id,
OLD.local_id,
OLD.aha_sys_id,
OLD.aha_service_id,
OLD.name,
OLD.aka,
OLD.address,
OLD.city,
OLD.state,
OLD.state_fips,
OLD.zipcode,
OLD.county_name,
OLD.county_fips,
OLD.phone_number,
OLD.ownership,
OLD.ipro_cms_ownership_group,
OLD.aha_ownership,
OLD.aha_ownership_code,
OLD.ipro_aha_ownership_group,
OLD.entity_type,
OLD.hsa_code,
OLD.hrr_code,
OLD.geo_lat,
OLD.geo_long,
OLD.aa_jc,
OLD.aa_cp,
OLD.aa_rt,
OLD.aa_ms,
OLD.aa_ns,
OLD.aa_ca,
OLD.aa_co,
OLD.aa_bc,
OLD.aa_md,
OLD.aa_ao,
OLD.aa_cc,
OLD.aa_hs,
OLD.aa_mc,
OLD.aa_sn,
OLD.aa_ph,
OLD.closed_date,
OLD.created,
CURDATE()
);

END IF;

END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_hospitals_update_hist`
--

CREATE TABLE IF NOT EXISTS `entity_hospitals_update_hist` (
`hist_id` int(10) NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned DEFAULT NULL,
`aha_id` varchar(7) DEFAULT NULL,
`mpn_id` varchar(15) DEFAULT NULL,
`npi_id` varchar(10) DEFAULT NULL,
`local_id` varchar(15) DEFAULT NULL,
`aha_sys_id` varchar(4) DEFAULT NULL,
`aha_service_id` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` enum('AK','AL','AS','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','US','UT','VA','VI','VT','WA','WI','WV','WY') DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(30) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`phone_number` varchar(46) DEFAULT NULL,
`ownership` varchar(100) DEFAULT NULL,
`ipro_cms_ownership_group` varchar(50) DEFAULT NULL,
`aha_ownership` varchar(50) DEFAULT NULL,
`aha_ownership_code` varchar(255) DEFAULT NULL,
`ipro_aha_ownership_group` varchar(50) DEFAULT NULL,
`entity_type` enum('comparator','hrr','system','entity','Critical Access Hospitals') DEFAULT NULL,
`hsa_code` varchar(5) DEFAULT NULL,
`hrr_code` varchar(3) DEFAULT NULL,
`geo_lat` float DEFAULT NULL,
`geo_long` float DEFAULT NULL,
`aa_jc` tinyint(1) DEFAULT NULL,
`aa_cp` tinyint(1) DEFAULT NULL,
`aa_rt` tinyint(1) DEFAULT NULL,
`aa_ms` tinyint(1) DEFAULT NULL,
`aa_ns` tinyint(1) DEFAULT NULL,
`aa_ca` tinyint(1) DEFAULT NULL,
`aa_co` tinyint(1) DEFAULT NULL,
`aa_bc` tinyint(1) DEFAULT NULL,
`aa_md` tinyint(1) DEFAULT NULL,
`aa_ao` tinyint(1) DEFAULT NULL,
`aa_cc` tinyint(1) DEFAULT NULL,
`aa_hs` tinyint(1) DEFAULT NULL,
`aa_mc` tinyint(1) DEFAULT NULL,
`aa_sn` tinyint(1) DEFAULT NULL,
`aa_ph` tinyint(1) DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`start_date` timestamp NULL DEFAULT NULL,
`end_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`hist_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=44249 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_hospitals_update_hist_detail`
--

CREATE TABLE IF NOT EXISTS `entity_hospitals_update_hist_detail` (
`update_hist_detail_id` int(10) NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) DEFAULT NULL,
`col_name` varchar(150) DEFAULT NULL,
`old_value` varchar(500) DEFAULT NULL,
`new_value` varchar(500) DEFAULT NULL,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_by` varchar(50) DEFAULT NULL,
PRIMARY KEY (`update_hist_detail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=47987 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_hospital_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_hospital_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`source` varchar(64) DEFAULT NULL,
PRIMARY KEY (`attribute_id`),
KEY `hospital_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1007355906 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_mcos`
--

CREATE TABLE IF NOT EXISTS `entity_mcos` (
`entity_id` int(10) NOT NULL AUTO_INCREMENT,
`mpn_id` varchar(7) DEFAULT NULL,
`npi_id` int(12) DEFAULT NULL,
`local_id` varchar(12) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(2) DEFAULT NULL,
`state_fips` varchar(2) DEFAULT NULL,
`zipcode` varchar(10) DEFAULT NULL,
`county_name` varchar(255) DEFAULT NULL,
`county_fips` varchar(3) DEFAULT NULL,
`phone_number` varchar(25) DEFAULT NULL,
`url` varchar(255) DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=214813 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_mco_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_mco_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` enum('designation') NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`attribute_id`),
KEY `entity_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_nursing_homes`
--

CREATE TABLE IF NOT EXISTS `entity_nursing_homes` (
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`mpn_id` varchar(15) DEFAULT NULL,
`local_id` varchar(15) DEFAULT NULL,
`certification` varchar(64) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` enum('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','US','UT','VA','VI','VT','WA','WI','WV','WY') DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(30) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`phone_number` varchar(46) DEFAULT NULL,
`operator_name` varchar(255) DEFAULT NULL,
`operator_address` varchar(255) DEFAULT NULL,
`operator_city` varchar(64) DEFAULT NULL,
`operator_state` varchar(2) DEFAULT NULL,
`operator_zip` varchar(5) DEFAULT NULL,
`ownership` varchar(64) DEFAULT NULL,
`aa_mf` tinyint(1) DEFAULT '0',
`aa_cc` tinyint(1) DEFAULT NULL,
`aa_sf` tinyint(1) DEFAULT NULL,
`aa_ih` tinyint(1) DEFAULT NULL,
`geo_lat` float DEFAULT NULL,
`geo_long` float DEFAULT NULL,
`opened_date` date DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=241697 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_nursing_home_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_nursing_home_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`attribute_id`),
KEY `hospital_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=484150 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_physicians`
--

CREATE TABLE IF NOT EXISTS `entity_physicians` (
`entity_id` bigint(20) unsigned NOT NULL,
`upin_id` varchar(10) DEFAULT NULL,
`npi_id` varchar(10) DEFAULT NULL,
`dea_id` varchar(10) DEFAULT NULL,
`rde_id` varchar(10) DEFAULT NULL,
`med_license_id` varchar(50) DEFAULT NULL,
`practice_id` varchar(10) DEFAULT NULL,
`last_name` varchar(100) DEFAULT NULL,
`first_name` varchar(100) DEFAULT NULL,
`middle_name` varchar(100) DEFAULT NULL,
`degree` varchar(3) DEFAULT NULL,
`degree_description` varchar(100) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(2) DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(30) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`phone_number` varchar(46) DEFAULT NULL,
`gender` varchar(1) DEFAULT NULL,
`date_of_birth` date DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` timestamp NULL DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `entity_physician_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_physician_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`source` varchar(255) DEFAULT NULL,
PRIMARY KEY (`attribute_id`),
KEY `hospital_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000111857 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_practices`
--

CREATE TABLE IF NOT EXISTS `entity_practices` (
`entity_id` int(20) NOT NULL,
`upin_id` varchar(10) DEFAULT NULL,
`npi_id` varchar(10) DEFAULT NULL,
`dea_id` varchar(10) DEFAULT NULL,
`rde_id` varchar(10) DEFAULT NULL,
`tax_id` varchar(20) DEFAULT NULL,
`med_license_id` varchar(10) DEFAULT NULL,
`practice_name` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(2) DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`zipcode` varchar(5) DEFAULT NULL,
`county_name` varchar(30) DEFAULT NULL,
`county_fips` varchar(10) DEFAULT NULL,
`phone_number` varchar(46) DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` timestamp NULL DEFAULT NULL,
`modified` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `entity_practice_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_practice_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`attribute_id`),
KEY `hospital_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000004404 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_systems`
--

CREATE TABLE IF NOT EXISTS `entity_systems` (
`entity_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`aha_sys_id` varchar(4) DEFAULT NULL,
`local_id` varchar(15) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`state` enum('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','GU','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MP','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','PR','RI','SC','SD','TN','TX','US','UT','VA','VI','VT','WA','WI','WV','WY') DEFAULT NULL,
`state_fips` varchar(10) DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=212242 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_system_attributes`
--

CREATE TABLE IF NOT EXISTS `entity_system_attributes` (
`attribute_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`entity_id` bigint(20) unsigned NOT NULL,
`attribute` varchar(255) NOT NULL,
`value` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`attribute_id`),
KEY `hospital_id` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000000076 ;

-- --------------------------------------------------------

--
-- Table structure for table `entity_values`
--

CREATE TABLE IF NOT EXISTS `entity_values` (
`measure_id` int(11) NOT NULL,
`entity_id` bigint(20) unsigned NOT NULL,
`date_start` date NOT NULL,
`date_end` date NOT NULL,
`source_id` int(11) NOT NULL DEFAULT '0',
`is_comparator` tinyint(1) NOT NULL DEFAULT '0',
`is_private` tinyint(1) NOT NULL DEFAULT '0',
`value` decimal(16,5) DEFAULT NULL,
`unit` enum('percent','per 1000','per 100000','cases','ratio','days','hours','patients','dollars','na','minutes','people','square miles','years','households','per 100','per 10000','micrograms') NOT NULL DEFAULT 'na',
`numerator` decimal(16,5) DEFAULT NULL,
`denominator` decimal(16,5) DEFAULT NULL,
`sig_var_state` enum('-1','0','1') DEFAULT NULL,
`sig_var_nation` enum('-1','0','1') DEFAULT NULL,
`sig_change` enum('-1','0','1') DEFAULT NULL,
`std_dev_from_state` decimal(16,5) DEFAULT NULL,
`std_dev_from_nation` decimal(16,5) DEFAULT NULL,
`std_dev` decimal(16,5) DEFAULT NULL,
`lower_ci` decimal(16,5) DEFAULT NULL,
`upper_ci` decimal(16,5) DEFAULT NULL,
`percentile_nation` smallint(3) DEFAULT NULL,
`percentile_state` smallint(3) DEFAULT NULL,
`rank_place_nation` smallint(6) DEFAULT NULL,
`rank_denominator_nation` smallint(6) DEFAULT NULL,
`rank_place_state` smallint(6) DEFAULT NULL,
`rank_denominator_state` smallint(6) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`batch_id` varchar(64) DEFAULT NULL,
PRIMARY KEY (`measure_id`,`entity_id`,`date_start`,`date_end`),
KEY `entity_values_ibfk_2` (`entity_id`),
KEY `entity_values_batch` (`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `footnotes`
--

CREATE TABLE IF NOT EXISTS `footnotes` (
`footnote_id` int(11) NOT NULL AUTO_INCREMENT,
`text` text NOT NULL,
`source_text` text,
`source` varchar(16) DEFAULT NULL,
`last_edit_date` date DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`footnote_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=131 ;

-- --------------------------------------------------------

--
-- Table structure for table `footnotes_values`
--

CREATE TABLE IF NOT EXISTS `footnotes_values` (
`measure_id` int(11) NOT NULL,
`entity_id` bigint(20) unsigned NOT NULL,
`date_start` date NOT NULL,
`date_end` date NOT NULL,
`footnote_id` int(11) NOT NULL,
`batch_id` varchar(64) DEFAULT NULL,
UNIQUE KEY `footnote_id` (`measure_id`,`entity_id`,`date_start`,`date_end`,`footnote_id`),
KEY `footnotes_ibfk2` (`footnote_id`),
KEY `footnotes_batch` (`batch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `measures`
--

CREATE TABLE IF NOT EXISTS `measures` (
`measure_id` int(11) NOT NULL AUTO_INCREMENT,
`author_id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`name_friendly` text,
`name_official` text,
`name_internal` text,
`description_friendly` text,
`description_long` text,
`author_measure_id` text,
`author_measure_specs` text,
`rationale_long` text,
`rationale_friendly` text,
`evidence` enum('more_is_better','less_is_better','no_evidence','no_difference','within_range_is_better') NOT NULL DEFAULT 'more_is_better',
`measure_type` enum('process','outcome','access','experience','utilization','structure','population') DEFAULT NULL,
`default_unit` enum('percent','per 1000','per 100000','cases','ratio','days','hours','patients','dollars','na','minutes','people','square miles','years','households','per 100','per 10000','micrograms') DEFAULT 'percent',
`measure_incept_date` date NOT NULL DEFAULT '0000-00-00',
`measure_expiry_date` date NOT NULL DEFAULT '0000-00-00',
`nqf_endorsed` enum('0','1') NOT NULL DEFAULT '0',
`nqf_endorsed_date` date NOT NULL DEFAULT '0000-00-00',
`numerator_definition` text,
`denominator_definition` text,
`minimum_sample` int(11) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`measure_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=17109 ;

-- --------------------------------------------------------

--
-- Table structure for table `measure_authors`
--

CREATE TABLE IF NOT EXISTS `measure_authors` (
`author_id` int(11) NOT NULL AUTO_INCREMENT,
`author` text,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=36 ;

-- --------------------------------------------------------

--
-- Table structure for table `measure_groups`
--

CREATE TABLE IF NOT EXISTS `measure_groups` (
`measure_group_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`measure_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=165 ;

-- --------------------------------------------------------

--
-- Table structure for table `measure_groups_measures`
--

CREATE TABLE IF NOT EXISTS `measure_groups_measures` (
`measure_group_id` int(11) NOT NULL,
`measure_id` int(11) NOT NULL,
PRIMARY KEY (`measure_group_id`,`measure_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `relationship_ascs_hospitals`
--

CREATE TABLE IF NOT EXISTS `relationship_ascs_hospitals` (
`asc_id` bigint(20) unsigned NOT NULL,
`hospital_id` bigint(20) unsigned NOT NULL,
`type` enum('asc_affiliation','hospital-ownership') DEFAULT 'asc_affiliation',
PRIMARY KEY (`asc_id`,`hospital_id`),
KEY `asc_hospital_affiliations_ibfk_2` (`hospital_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `value_sources`
--

CREATE TABLE IF NOT EXISTS `value_sources` (
`source_id` int(11) NOT NULL AUTO_INCREMENT,
`title` text NOT NULL,
`update_frequency` enum('triennial','biennial','annual','quarterly','monthly','weekly','daily','hourly','ondemand','unknown') NOT NULL DEFAULT 'unknown',
`update_type` enum('rolling','append','snapshot') NOT NULL DEFAULT 'rolling',
`geo_coverage` enum('world','us','state','county','city','zip','facility') NOT NULL DEFAULT 'world',
`file_format` enum('csv','fixedlength','xls','mdb','sql') NOT NULL DEFAULT 'csv',
`provider_coverage` enum('h','nh','hh','po','cc') NOT NULL DEFAULT 'h',
`provider_payor` enum('allpayor','medicare','medicaid','private','other') NOT NULL DEFAULT 'allpayor',
`owner` text NOT NULL,
`owner_public_url` text NOT NULL,
`owner_data_url` text NOT NULL,
`short_desc` text NOT NULL,
`long_desc` text NOT NULL,
`pellucid_loc` text NOT NULL,
`dua_url` text NOT NULL,
`acquisition_contact_name` text NOT NULL,
`acquisition_contact_email` text NOT NULL,
`acquisition_contact_telephone` text NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`source_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=77 ;

-- --------------------------------------------------------

--
-- Table structure for table `zipcodes`
--

CREATE TABLE IF NOT EXISTS `zipcodes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zipcode` char(5) NOT NULL,
`zip_type` char(1) DEFAULT NULL,
`city_name` varchar(255) DEFAULT NULL,
`city_type` char(1) DEFAULT NULL,
`county_name` varchar(255) DEFAULT NULL,
`county_fips` char(5) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
`state_abbr` char(2) DEFAULT NULL,
`state_fips` char(4) DEFAULT NULL,
`msa_code` char(10) DEFAULT NULL,
`area_code` char(3) DEFAULT NULL,
`time_zone` varchar(32) DEFAULT NULL,
`utc` double DEFAULT NULL,
`dst` enum('Y','N') DEFAULT NULL,
`latitude` double DEFAULT NULL,
`longitude` double DEFAULT NULL,
`hsa_code` int(5) DEFAULT NULL,
`hsa_city` varchar(255) DEFAULT NULL,
`hsa_state` varchar(2) DEFAULT NULL,
`hrr_code` int(3) DEFAULT NULL,
`hrr_city` varchar(255) DEFAULT NULL,
`hrr_state` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `zipcode` (`zipcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=80176 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `entity_ascs`
--
ALTER TABLE `entity_ascs`
ADD CONSTRAINT `entity_ascs_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_asc_attributes`
--
ALTER TABLE `entity_asc_attributes`
ADD CONSTRAINT `asc_attributes_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entity_ascs` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_calculations`
--
ALTER TABLE `entity_calculations`
ADD CONSTRAINT `entity_calculations_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_calculation_attributes`
--
ALTER TABLE `entity_calculation_attributes`
ADD CONSTRAINT `entity_calculation_attributes_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entity_calculations` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_geographies`
--
ALTER TABLE `entity_geographies`
ADD CONSTRAINT `entity_geographies_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_geography_attributes`
--
ALTER TABLE `entity_geography_attributes`
ADD CONSTRAINT `entity_geography_attributes_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entity_geographies` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_home_healths`
--
ALTER TABLE `entity_home_healths`
ADD CONSTRAINT `entity_home_healths_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_hospitals`
--
ALTER TABLE `entity_hospitals`
ADD CONSTRAINT `entity_hospitals_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_hospital_attributes`
--
ALTER TABLE `entity_hospital_attributes`
ADD CONSTRAINT `hospital_attributes_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entity_hospitals` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_systems`
--
ALTER TABLE `entity_systems`
ADD CONSTRAINT `entity_systems_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_system_attributes`
--
ALTER TABLE `entity_system_attributes`
ADD CONSTRAINT `system_attributes_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entity_systems` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `entity_values`
--
ALTER TABLE `entity_values`
ADD CONSTRAINT `entity_values_ibfk_1` FOREIGN KEY (`measure_id`) REFERENCES `measures` (`measure_id`) ON DELETE CASCADE,
ADD CONSTRAINT `entity_values_ibfk_2` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE;

--
-- Constraints for table `footnotes_values`
--
ALTER TABLE `footnotes_values`
ADD CONSTRAINT `footnotes_ibfk2` FOREIGN KEY (`footnote_id`) REFERENCES `footnotes` (`footnote_id`) ON DELETE CASCADE,
ADD CONSTRAINT `footnotes_values_ibfk1` FOREIGN KEY (`measure_id`, `entity_id`, `date_start`, `date_end`) REFERENCES `entity_values` (`measure_id`, `entity_id`, `date_start`, `date_end`) ON DELETE CASCADE;

--
-- Constraints for table `relationship_ascs_hospitals`
--
ALTER TABLE `relationship_ascs_hospitals`
ADD CONSTRAINT `asc_hospital_affiliations_ibfk_1` FOREIGN KEY (`asc_id`) REFERENCES `entity_ascs` (`entity_id`) ON DELETE CASCADE,
ADD CONSTRAINT `asc_hospital_affiliations_ibfk_2` FOREIGN KEY (`hospital_id`) REFERENCES `entity_hospitals` (`entity_id`) ON DELETE CASCADE;