Summary
This table contains information on nursing homes. Originally, it was designed to house data specific to Nursing Homes in NY for the NYSprofiles project, but use of the Quarterly CMS Nursing Home Compare data set has introduced data for Medicare and Medicaid Nursing Homes nationwide. This table uses a unique entity_id, housed in the entities table. This entity_id is also linked to the entity_nursing_home_attributes and entity_nursing_home_overrides tables.
Table Creation
CREATE TABLE IF NOT EXISTS `entity_nursing_homes` (
`entity`entity_id` id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`mpn_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 '0',
`aa_sf` tinyint(1) DEFAULT '0',
`aa_ih` tinyint(1) DEFAULT '0',
`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=150935 ;
Fields`entity_id` `
entity_id` the unique id that serves to identify an entity internally. It links to the entities table
`mpn_id` medicare provider number, provided federally
`local_id` identifier that is used locally, generally by the state
`name` name recognized by the AHA
`aka` additional alias that is generally or locally accepted. In this table, it is usually the Federal name
`address`
`city`
`state`
`state`state_fips` fips` nationally recognized ID number associated with the state.
`zipcode`
`county_name`
`county`county_fips` fips` nationally recognized ID number associated with individual counties for each state.
`phone_number`
`operator_name`,
`operator_address`,
`operator_city`,
`operator_state`,
`operator_zip`,
`ownership`,
`aa_mf` is multi-facility? 1=yes 0=no. Determines whether or not an entity has multiple facilities associated with it
`aa_cc`,
`aa_sf',
`aa_ih`,
`geo_lat` latitude location of the facility
`geo_long` longitude location of the facility
`opened_date` the date the entity was opened in YYYY-MM-DD format
`closed_date` the date the entity was closed in YYYY-MM-DD format
`created` the date the row was created in YYYY-MM-DD HH:MM:SS format
`modified` the date the row was last changed in YYYY-MM-DD HH:MM:SS format
Field Informationfield type attributes notes example entity_nursing_homes.entity_id BIGINT(20) NOT NULL AUTO_INCREMENT Provided by Pellucid, FOREIGN KEY: links to entities 100000000001 entity_nursing_homes.mpn_id VARCHAR(15) DEFAULT NULL Provided by national data, AHA 030005 entity_nursing_homes.local_id VARCHAR(15) DEFAULT NULL Provided by local government 0123 entity_nursing_homes.certification VARCHAR(64) DEFAULT NULL Nursing Home Certifications Medicare and Medicaid entity_nursing_homes.name VARCHAR(255) DEFAULT NULL Nationally recognized name Nassau County Nursing Home entity_nursing_homes.aka VARCHAR(255) DEFAULT NULL Other accepted name for the entity Nassau NH entity_nursing_homes.address VARCHAR(255) DEFAULT NULL Physical Location 123 Fake St. entity_nursing_homes.city VARCHAR(255) DEFAULT NULL Physical Location Lake Success entity_nursing_homes.state ENUM DEFAULT NULL Two character abbreviation for the state NY entity_nursing_homes.state_fips VARCHAR(10) DEFAULT NULL Nationally recognized state code 36 entity_nursing_homes.zipcode VARCHAR(5) DEFAULT NULL Five Digit Postal Code 11042 entity_nursing_homes.county_name VARCHAR(30) DEFAULT NULL Physical Location Nassau entity_nursing_homes.county_fips VARCHAR(10) DEFAULT NULL Nationally recognized county code for each state 007 entity_nursing_homes.phone_number VARCHAR(46) DEFAULT NULL Should not include Parenthesis 1234567890 entity_nursing_homes.operator_name VARCHAR(255) DEFAULT NULL Nationally recognized name Acme Health Inc. entity_nursing_homes.operator_address VARCHAR(255) DEFAULT NULL Operator's Physical Location 123 Main St. entity_nursing_homes.operator_city VARCHAR(64) DEFAULT NULL Operator's Physical Location Gotham entity_nursing_homes.operator_state VARCHAR(2) DEFAULT NULL Two character abbreviation for the state NY entity_nursing_homes.operator_zip VARCHAR(5) DEFAULT NULL Operator's Five Digit Postal Code 12345 entity_nursing_homes.ownership VARCHAR(64) DEFAULT NULL Owner/operator of the entity Voluntary--Not for Profit Corporation entity_nursing_homes.aa_mf TINYINT(1) DEFAULT '0' 1=Y,0=N 0 entity_nursing_homes.aa_cc TINYINT(1) DEFAULT '0' 1=Y,0=N 0 entity_nursing_homes.aa_sf TINYINT(1) DEFAULT '0' 1=Y,0=N 0 entity_nursing_homes.aa_ih TINYINT(1) DEFAULT '0' 1=Y,0=N 0 entity_nursing_homes.geo_lat FLOAT DEFAULT NULL Latitude location 12.34 entity_nursing_homes.geo_long FLOAT DEFAULT NULL Longitude location 15.41 entity_nursing_homes.opened_date DATE DEFAULT NULL The date the hospital was opened in YYYY-MM-DD format 2010-06-23 entity_nursing_homes.closed_date DATE DEFAULT NULL The date the hospital was opened in YYYY-MM-DD format 2009-12-31 entity_nursing_homes.created TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' When the row was created. 'YYYY-MM-DD HH:MM:SS' 2010-06-23 14:12:36 entity_nursing_homes.modified TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' When the row was updated last. Auto updates on modification. 'YYYY-MM-DD HH:MM:SS' 2010-06-23 14:12:36
PRIMARY KEY (`entity_id`)
Notes
field | type | attributes | notes | example |
---|---|---|---|---|
entity_nursing_homes.entity_id | BIGINT(20) | NOT NULL AUTO_INCREMENT | Provided by Pellucid, FOREIGN KEY: links to entities | 100000000001 |
entity_nursing_homes.mpn_id | VARCHAR(15) | DEFAULT NULL | Provided by national data, AHA | 030005 |
entity_nursing_homes.local_id | VARCHAR(15) | DEFAULT NULL | Provided by local government | 0123 |
entity_nursing_homes.certification | VARCHAR(64) | DEFAULT NULL | Nursing Home Certifications | Medicare and Medicaid |
entity_nursing_homes.name | VARCHAR(255) | DEFAULT NULL | Nationally recognized name | Nassau County Nursing Home |
entity_nursing_homes.aka | VARCHAR(255) | DEFAULT NULL | Other accepted name for the entity | Nassau NH |
entity_nursing_homes.address | VARCHAR(255) | DEFAULT NULL | Physical Location | 123 Fake St. |
entity_nursing_homes.city | VARCHAR(255) | DEFAULT NULL | Physical Location | Lake Success |
entity_nursing_homes.state | ENUM | DEFAULT NULL | Two character abbreviation for the state | NY |
entity_nursing_homes.state_fips | VARCHAR(10) | DEFAULT NULL | Nationally recognized state code | 36 |
entity_nursing_homes.zipcode | VARCHAR(5) | DEFAULT NULL | Five Digit Postal Code | 11042 |
entity_nursing_homes.county_name | VARCHAR(30) | DEFAULT NULL | Physical Location | Nassau |
entity_nursing_homes.county_fips | VARCHAR(10) | DEFAULT NULL | Nationally recognized county code for each state | 007 |
entity_nursing_homes.phone_number | VARCHAR(46) | DEFAULT NULL | Should not include Parenthesis | 1234567890 |
entity_nursing_homes.operator_name | VARCHAR(255) | DEFAULT NULL | Nationally recognized name | Acme Health Inc. |
entity_nursing_homes.operator_address | VARCHAR(255) | DEFAULT NULL | Operator's Physical Location | 123 Main St. |
entity_nursing_homes.operator_city | VARCHAR(64) | DEFAULT NULL | Operator's Physical Location | Gotham |
entity_nursing_homes.operator_state | VARCHAR(2) | DEFAULT NULL | Two character abbreviation for the state | NY |
entity_nursing_homes.operator_zip | VARCHAR(5) | DEFAULT NULL | Operator's Five Digit Postal Code | 12345 |
entity_nursing_homes.ownership | VARCHAR(64) | DEFAULT NULL | Owner/operator of the entity | Voluntary--Not for Profit Corporation |
entity_nursing_homes.aa_mf | TINYINT(1) | DEFAULT '0' | 1=Y,0=N | 0 |
entity_nursing_homes.aa_cc | TINYINT(1) | DEFAULT '0' | 1=Y,0=N | 0 |
entity_nursing_homes.aa_sf | TINYINT(1) | DEFAULT '0' | 1=Y,0=N | 0 |
entity_nursing_homes.aa_ih | TINYINT(1) | DEFAULT '0' | 1=Y,0=N | 0 |
entity_nursing_homes.geo_lat | FLOAT | DEFAULT NULL | Latitude location | 12.34 |
entity_nursing_homes.geo_long | FLOAT | DEFAULT NULL | Longitude location | 15.41 |
entity_nursing_homes.opened_date | DATE | DEFAULT NULL | The date the hospital was opened in YYYY-MM-DD format | 2010-06-23 |
entity_nursing_homes.closed_date | DATE | DEFAULT NULL | The date the hospital was opened in YYYY-MM-DD format | 2009-12-31 |
entity_nursing_homes.created | TIMESTAMP | NOT NULL DEFAULT '0000-00-00 00:00:00' | When the row was created. 'YYYY-MM-DD HH:MM:SS' | 2010-06-23 14:12:36 |
entity_nursing_homes.modified | TIMESTAMP | NOT NULL DEFAULT '0000-00-00 00:00:00' | When the row was updated last. Auto updates on modification. 'YYYY-MM-DD HH:MM:SS' | 2010-06-23 14:12:36 |
PRIMARY KEY (`entity_id`)