entity_hospitals

Summary

The entity_hospitals table contains information on the entities that are considered hospitals. It contains details on the hospital and its operating specifications. The entity_id field links to the entities table, having the same id existing in both tables and acting as a key.

The entity_hospitals table also links to entity_hospital_overrides, entity_hospital_attributes, relationship_ascs_hospitals

Table Creation

Field Information

`entity_id` the identifying entity number. Same as entity_id in the entities table, and serves as a link
`aha_id` number assigned to the hospital in the AHA national survey
`mpn_id` derived from CMS data
`npi_id` national provider number derived from https://nppes.cms.hhs.gov/NPPES/NPIRegistryHome.do
`local_id` provider ID assigned by source (i.e. state)
`aha_sys_id` if entity is a member of a multi-entity system the system can be stored here and looked up in ref_systems. Primarily obtained from AHA Survey database.
`aha_service_id` AHA assigns various service codes to denote the type of hospital
`name` formal business name that is defined by CMS
`aka` alias for the hospital
`address` physical location of the establishment
`city`
`state` the two-letter state abbreviation ('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 is null and national entities have a state code of 'US'
`state_fips` FIPS state code
`zipcode`
`county_name`
`county_fips` FIPS county code
`phone_number`
`entity_type` determines the hospital's classification('comparator','hrr','system','entity')
`hsa_code` HSA identifier (Dartmouth)
`hrr_code` HRR identifier (Dartmouth)
`geo_lat` latitude location
`geo_long` longitude location
`aa_jc` is Joint Commission accredited? (1=Y 0=N)
`aa_cp` has American College of Surgeons approved Cancer Program? (1=Y 0=N)
`aa_rt` has Residency Training approved by ACGME? (1=Y 0=N)
`aa_ms` has a medical school affiliation? (1=Y 0=N)
`aa_ns` is a National League of Nursing nursing school? (1=Y 0=N)
`aa_ca` is CARF accredited? (1=Y 0=N)
`aa_co` is a member of COTH? (1=Y 0=N)
`aa_bc` is Blue Cross contracting or participating? (1=Y 0=N)
`aa_md` is Medicare certified? (1=Y 0=N)
`aa_ao` is American Osteopathic Association accredited? (1=Y 0=N)
`aa_cc` is Catholic Church operated? (1=Y 0=N)
`aa_hs` is member Federation of American Health Care Systems? (1=Y 0=N)
`aa_mc` is an academic medical center? (1=Y 0=N). Found at http://services.aamc.org/memberlistings/index.cfm?fuseaction=home.search&search_type=TH&state_criteria=ALL
`aa_sn` is a CMWF Safety Net Hospital? (1=Y 0=N)
`aa_ph` is a member National Association Public Hospitals (NAPH) (1=Y 0=N)
`closed_date` date the entity was closed in YYYY-MM-DD format
`created` date and time the row was created in YYYY-MM-DD HH:MM:SS format
`modified` date and time the row was modified in YYYY-MM-DD HH:MM:SS format. Can be set to current ON UPDATE CURRENT_TIMESTAMP

Fields

field	type	attributes	notes	example
entity_hospitals.entity_id	BIGINT(20)	UNSIGNED NOT NULL AUTO_INCREMENT	Provided by Pellucid, FOREIGN KEY: links to entities table	1219198502
entity_hospitals.aha_id	VARCHAR(7)	DEFAULT NULL	Provided by AHA National Survey	161514131
entity_hospitals.mpn_id	VARCHAR(15)	DEFAULT NULL	Provided by CMS Data	301912
entity_hospitals.npi_id	VARCHAR(10)	DEFAULT NULL	Derived from Government registry	1918445361
entity_hospitals.local_id	VARCHAR(15)	DEFAULT NULL	Derived from state information	1234568A34D
entity_hospitals.aha_sys_id	VARCHAR(4)	DEFAULT NULL	Pellucid ID of the System it belongs to	7890
entity_hospitals.aha_service_id	VARCHAR(255)	DEFAULT NULL	AHA Denotation of hospital type	007
entity_hospitals.name	VARCHAR(255)	DEFAULT NULL	Defined by CMS	Saint Joseph's Rehabilitation
entity_hospitals.aka	VARCHAR(255)	DEFAULT NULL		St. Joe's Rehab
entity_hospitals.address	VARCHAR(255)	DEFAULT NULL	Physical Location	123 Fake St
entity_hospitals.city	VARCHAR(255)	DEFAULT NULL		Lake Success
entity_hospitals.state	ENUM	DEFAULT NULL	2 character abbreviation for the state	NY
entity_hospitals.state_fips	VARCHAR(10)	DEFAULT NULL	Nationally defined	09
entity_hospitals.zipcode	VARCHAR(5)	DEFAULT NULL		11042
entity_hospitals.county_name	VARCHAR(30)	DEFAULT NULL		Nassau
entity_hospitals.county_fips	VARCHAR(10)	DEFAULT NULL	Determined by state	056
entity_hospitals.phone_number	VARCHAR(46)	DEFAULT NULL		5165550123
entity_hospitals.ownership	VARCHAR(100)	DEFAULT NULL	owner/operator type of the entity	Voluntary - Not For Profit
entity_hospitals.ipro_cms_ownership_group	VARCHAR(50)	DEFAULT NULL	Performed by IPRO	Not For Profit
entity_hospitals.aha_ownership	VARCHAR(50)	DEFAULT NULL	Ownership reported to AHA	For Profit
entity_hospitals.aha_ownership_code	VARCHAR(255)	DEFAULT NULL	Provided by AHA	23
entity_hospitals.ipro_aha_ownership_group	VARCHAR(50)	DEFAULT NULL	Performed by IPRO	Not For Profit
entity_hospitals.entity_type	ENUM('comparator','hrr','system','entity','Critical Access Hospitals')	NOT NULL DEFAULT 'entity'	hospital's classification	entity
entity_hospitals.hsa_code	VARCHAR(5)	DEFAULT NULL	Derived from Dartmouth	12A34
entity_hospitals.hrr_code	VARCHAR(3)	DEFAULT NULL	Derived from Dartmouth	56B78
entity_hospitals.geo_lat	FLOAT	DEFAULT NULL	latitude location	32.3272
entity_hospitals.geo_long	FLOAT	DEFAULT NULL	longitude location	86.2712
entity_hospitals.aa_jc	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_cp	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_rt	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_ms	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_ns	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_ca	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_co	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_bc	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_md	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_ao	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_cc	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_hs	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_mc	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_sn	TINYINT(1)		1=Y 0=N	1
entity_hospitals.aa_ph	TINYINT(1)		1=Y 0=N	1
entity_hospitals.closed_date	DATE	DEFAULT NULL	Date of closure YYYY-MM-DD	2001-01-24
entity_hospitals.created	TIMESTAMP	NOT NULL DEFAULT '0000-00-00 00:00:00'	The date and time the row was created	2010-05-18 09:15:36
entity_hospitals.modified	TIMESTAMP	NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP	The date and time the row was last modified	2010-05-18 09:15:36

PRIMARY KEY (`entity_id`)
CONSTRAINT `entity_hospitals_ibfk_1` FOREIGN KEY (`entity_id`)
REFERENCES `entities` (`entity_id`) ON DELETE CASCADE -- Pellucid only constraint

Notes

2010-05-26

Updated the `aha_service_id` field's type from VARCHAR(3) to VARCHAR(255) because the field was not large enough to store all pertinent data. This was discussed with jmking before implementation.

~JG