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

 

Notes