2015-09-24
Added a sig_var_test field to the measures table, meant to track what kind of significance tests are applied to the measure:
ALTER TABLE `measures` ADD `sig_var_test` VARCHAR(64) NULL DEFAULT NULL AFTER `evidence`;
2015-04-24:
changed aa_br in entity_home_healths to allow for nulls
2015-04-23:
ALTER TABLE `entity_mcos` ADD `payer_type` VARCHAR(255) NULL AFTER `local_id`;
(added payer_type field to allow for NY data to be stored and a crosswalk to be created)
Currently, local_id or provider ID is used in 3 places, only with different payer types
This allows us to link entity_id to the combination of the provider ID and the payer_type fields
ALTER TABLE `entity_mco_attributes` ADD `source` VARCHAR(64) NULL ;
(adds a source to the attributes for MCOS)
This table was previously unused but is populated now
Added measure_type 'mixed' to the possible available measure_types
2015-03-30:
created entity_dialysis_facilities table in pellucid to track national dialysis facilities for ESRD patients
CREATE TABLE IF NOT EXISTS `entity_dialysis_facilities` (
`entity_id` int(10) NOT NULL,
`mpn_id` varchar(7) DEFAULT NULL,
`local_id` varchar(20) DEFAULT NULL,
`network_id` varchar(2) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`aka` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(64) DEFAULT NULL,
`state` varchar(2) DEFAULT NULL,
`state_fips` varchar(2) DEFAULT NULL,
`zipcode` varchar(10) DEFAULT NULL,
`county_name` varchar(64) DEFAULT NULL,
`county_fips` varchar(3) DEFAULT NULL,
`phone_number` varchar(20) DEFAULT NULL,
`ownership` varchar(64) DEFAULT NULL,
`chain` tinyint(1) DEFAULT NULL,
`chain_name` varchar(255) DEFAULT NULL,
`certified_date` date DEFAULT NULL,
`closed_date` date DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2015-03-05:
Reinserted entity_hospices and entity_hospice_attributes tables, brought over their data. John requested they be stored and displayed separately, in congruence with NYDOH requests
Needed to pull/change/close/delete rows that already corresponded to these entities from home healths and HH attributes to avoid redundancy
2015-02-26:
Added primary key to entity_clinics table and reuploaded data, removing duplicates
2015-02-17:
Added state_fips field to entity_clinics table. This is to ensure we are able to handle data from other states
2015-02-13:
Added star_rating column to pellucid using:
ALTER TABLE `entity_values` ADD `star_rating` DECIMAL(3,1) NULL AFTER `gpa`;
2015-02-06:
updated range5 to gpa as quintiles can be calculated using percentages and JMK is looking to track gpa in the future
2015-01-30:
Added 2 columns to entity_values:
letter_grade is a VC3 nullable after unit
range5 is a Decimal(16,5) nullable after letter_grade
2014-07-17:
Removed the entity_hospices table and the entity_hospice_attributes table.
Folded Hospice rows into entity_home_healths as a result of suggestions from John Schoneboom, Jaz King, and Ti-Kuang Lee
2014-07-09: All table structures have been updated to reflect the current Pellucid2 formats with the exception of Measure_sets which was updated to match the HMH db due to it not being present in Pellucid2. Specific changes listed below;
Attributes
'attribute' field was changed from enum to varchar(255)
'created' field was changed from datetime to timestamp
The 'source' field was added as a varchar(64)
Entities
'type' field was changed from enum to varchar(255)
'created' field was changed from datetime to timestamp
Entity ascs
'mpn_id' field was changed from varchar(6) to varchar(15)
'npi_id' field was changed from varchar(10) to int(10)
'address' field was changed from varchar(255) to varchar(60)
'mpn_id' field was changed from varchar(255) to varchar(30)
'county_name' field was changed from varchar(30) to varchar(25)
'phone_number' field was changed from varchar(46) to varchar(15)
'geo_lat' field was changed from float to double
'geo_long' field was changed from float to double
'hsa_code' field was changed from varchar(5) to varchar(255)
'phone_number' field was changed from varchar(64) to varchar(255)
'created' field was changed from datetime to timestamp
The 'aha_sys_id' and 'aha_service_id' fields were removed
Entity Calculations
'entity_type' field was changed from enum('average', 'median', 'mode','threshold') to enum('average', 'median', 'mode','threshold','total')
Entity Geographies
'entity_type' field was changed from enum to varchar(50)
Entity Hospitals
'mpn_id' field was changed from varchar(6) to varchar(15)
'ownership' added as a varchar(100) field
'ipro_cms_ownership_group' added as a varchar(50) field
'aha_ownership' added as a varchar(50) field
'aha_ownership_code' added as a varchar(255) field
'ipro_aha_ownership_group' added as a varchar(50) field
added 'Critical Access Hospitals' to the 'entity_type' enum() field
'hrr_code' field was changed from varchar(5) to varchar(255)
'created' field was changed from datetime to timestamp
Entity Nursing Homes
'certification' added as a varchar(64) field
'operator_name' added as a varchar(255) field
'operator_address' added as a varchar(255) field
'operator_city' added as a varchar(64) field
'operator_state' added as a varchar(2) field
'operator_zip' added as a varchar(5) field
'ownership' added as a varchar(64) field
aa_cc added as a tinyint(1) field
aa_sf added as a tinyint(1) field
aa_ih added as a tinyint(1) field
Entity Physicians
'entity_id' field was changed from int(20) to bigint(20)
'med_license_id' field was changed from varchar(10) to varchar(50)
Entity Values
added 'per 10000' and 'micrograms' to the 'unit' enum() field
Footnotes
'created' field was changed from datetime to timestamp
Measure Authors
'created' field was changed from datetime to timestamp
Measure Groups
'created' field was changed from datetime to timestamp
Measure Sets
'description_alias' added as a text field
'rationale_alias' added as a text field
'suppress_on_numerator_less_than' added as a tintint(1) field
'suppress_on_numerator_null' added as a decimal(12,2) field
'suppression_list' added as a text field
'display' added as a tintint(1) field
'view_template' added as a varchar(255) field
'created' field was changed from datetime to timestamp
Measures
'measure_id' field was changed from smallint(6) to int(11)
'author_id' field was changed from smallint(6) to int(11)
'parent_id' field was changed from smallint(6) to int(11)
added 'within_range_is_better' to the 'evidence' enum() field
added 'per 10000' and 'micrograms' to the 'default_unit' enum() field
'created' field was changed from datetime to timestamp
'modified' field was changed from datetime to timestamp
Overrides
'created' field was changed from datetime to timestamp
Relationship ASCS Hospital
'type' field was changed from enum ('astc-hospital', 'hospital-astc', 'both') to enum('asc_affiliation','hospital-ownership')
Value Sources
'source_id' field was changed from tinyint(4) to int(11)
'file_format' added as an enum('csv','fixedlength','xls','mdb','sql') field
'owner_data_url' added as a text field
'created' field was changed from datetime to timestamp
2013-11-21: updated the structure of entity_nursing_homes to include several new fields (certification, operator_name, operator_address, operator_city, operator_state, operator_zip, ownership, aa_cc, aa_sf, aa_ih)
New structure:
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=240873 ;
2013-11-07: Added the entity_hospices and entity_hospice_attributes tables to pellucid and wntb. Pulled data from home_healths where the entity_type was 'hspc'
2013-05-23: Changed the structure of the entity_type field in entity_calculations to include 'total' as a value. This change was made in pellucid2, pellucid_wntb, alpha (idph and dev), qa and live