Page Comparison

Summary

Separate entities for home health institutions. Originally, this table was intended to track homes health facilities for NY state, but value generation was expanded to include data from the CMS Home Health Compare Data set, which includes national data.  An entity_id is generated, which is unique to the entities table, and can also be linked to entity_home_health_attributes and entity_home_health_overrides.

Table Creation

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`), 
UNIQUE KEY `hh_pfi_idx` (`entity_type`,`local_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=14265 ; 
ALTER TABLE `entity_home_healths` 
ADD CONSTRAINT `entity_home_healths_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE;

Field Information

`entity_id`
`

entity_id` the unique ID that is created by IPRO and links this table to the entities table 
`mpn_id` the medicare provider number 
`local_id` other identifying number associated with the entity. In this case it is generally the PFI number 
`local_id2` secondary local id. Often is the opcert number 
`name` publicly recognized name for the home health 
`aka` alias for the entity 
`address` 
`city` 
`state` 
`state`state_fips` fips` the federal fips code associated with the state 
`zipcode` 
`county_name` 
`county`county_fips` fips` the fips code assigned to the county. Generally 3-digit 
`region` the region that this entity resides in. This field may be removed later on. 
`phone_number` 
`fax_number` 
`url` website for the entity 
`entity_type` abbreviated classification for the entity 
`service_id` extended entity_type classification 
`ipro_ownership_group` details regarding the nature of the entity (for profit/government owned, etc) 
`ownership` describes who owns the entity 
`operator_address` 
`operator_city` 
`operator_state` 
`operator_zipcode` 
`specialties` list of known specialties for the home health entity 
`aa_br` branches 
`aa_cb` is combination? 0 is no, 1 is yes 
`aa_sn` is special needs? 0 is no, 1 is yes 
`aa_da` deeming agency: Information on the agency that deemed the entity 
`opened_date` 
`closed_date` 
`created` 
`modified`

 

Fields

field	type	attributes	notes	example
entity_home_healths.entity_id	BIGINT(20)	unsigned NOT NULL	from Provided by Pellucid, FOREIGN KEY: links to entities	102034
entity_home_healths.mpn_id	VARCHAR(15)	DEFAULT NULL	Provided by CMS data	031246
entity_home_healths.local_id	VARCHAR(15)	DEFAULT NULL	PFI Number provided by source	4899
entity_home_healths.local_id2	VARCHAR(15)	DEFAULT NULL	Alternate local_id	0101600
entity_home_healths.name	VARCHAR(255)	DEFAULT NULL	Home Health Name	St. John's Home Health
entity_home_healths.aka	VARCHAR(255)	DEFAULT NULL	Alternative Home Health Name	Saint John's HH
entity_home_healths.address	VARCHAR(255)	DEFAULT NULL	Home Health Physical Location	123 Fake St.
entity_home_healths.city	VARCHAR(255)	DEFAULT NULL	Home Health Physical Location	Lake Success
entity_home_healths.state	ENUM	DEFAULT NULL	2 character abbreviation for the state	NY
entity_home_healths.state_fips	VARCHAR(10)	DEFAULT NULL	Assigned by federal government	17
entity_home_healths.zipcode	VARCHAR(5)	DEFAULT NULL	Home Health 5 Digit Postal Code	11042
entity_home_healths.county_name	VARCHAR(30)	DEFAULT NULL	Physical Location	Nassau
entity_home_healths.county_fips	VARCHAR(10)	DEFAULT NULL	Assigned by federal/state government	007
entity_home_healths.region	VARCHAR(25)	DEFAULT NULL	Assigned to area, provided by source	5
entity_home_healths.phone_number	VARCHAR(46)	DEFAULT NULL	Telephone Number	5161234567
entity_home_healths.fax_number	VARCHAR(25)	DEFAULT NULL	Telefax Number	5169876543
entity_home_healths.url	VARCHAR(200)	DEFAULT NULL	Website associated with the entity	www.albanycounty.com
entity_home_healths.entity_type	VARCHAR(25)	DEFAULT NULL	Entity's classification	CHHA
entity_home_healths.service_id	VARCHAR(255)	DEFAULT NULL	Details on the facility or sponsor	Certified Home Health Agency
entity_home_healths.ipro_ownership_group	VARCHAR(100)	DEFAULT NULL	Details regarding the ownership classification	Government, For Profit
entity_home_healths.ownership	VARCHAR(100)	DEFAULT NULL	Owner/operator of the entity	County of Albany
entity_home_healths.operator_address	VARCHAR(255)	DEFAULT NULL	Operator's Physical Location	123 Real St.
entity_home_healths.operator_city	VARCHAR(255)	DEFAULT NULL	Operator's Physical Location	New Hyde Park
entity_home_healths.operator_state	ENUM	DEFAULT NULL	2 character abbreviation for the state	NY
entity_home_healths.operator_zipcode	VARCHAR(5)	DEFAULT NULL	Operator's 5 Digit Postal Code	11040
entity_home_healths.specialties	VARCHAR(50)	DEFAULT NULL	The Specialties of the Home Health	Geriatrics
entity_home_healths.aa_br	SMALLINT(6)	NOT NULL DEFAULT 0	Amount of branches	3
entity_home_healths.aa_cb	TINYINT(4)	DEFAULT 0	Is combo? 1=Y 0=N	1
entity_home_healths.aa_sn	TINYINT(4)	DEFAULT 0	Is special needs? 1=Y 0=N	0
entity_home_healths.aa_da	VARCHAR(25)	DEFAULT NULL	Deeming Agency	AHHA
entity_home_healths.opened_date	DATE	NOT NULL DEFAULT 0000-00-00	Date the HH was opened	2009-01-01
entity_home_healths.closed_date	DATE	DEFAULT NULL	Date the HH was closed	2009-12-31
entity_home_healths.created	TIMESTAMP	NOT NULL DEFAULT 0000-00-00 00:00:00	Date and time the row was created in YYYY-MM-DD HH:MM:SS format	2010-06-14 10:56:37
entity_home_healths.modified	TIMESTAMP	NOT NULL DEFAULT 0000-00-00 00:00:00	Date and time the row was last edited in YYYY-MM-DD HH:MM:SS format	2010-06-14 10:56:37

 

Notes