relationship_asc_hospitals

Summary

This table documents the relationships between Ambulatory Surgical Centers and hospitals. It serves as a link between entity_hospitals and entity_ascs. This table can be used to find information on the affiliations that ASCs and hospitals have with each other.

Table Creation

DROP TABLE IF EXISTS `astcs_hospitals`;

CREATE TABLE astc_hospital_affiliations ( 
`asc_id` bigint(20) unsigned NOT NULL, 
`hospital_id` bigint(20) unsigned NOT NULL, 
`type` enum ('asc_affiliation','hospital-ownership') DEFAULT 'asc_affiliation', 
PRIMARY KEY(`asc_id`, `hospital_id`), 
CONSTRAINT `asc_hospital_affiliations_ibfk_1` FOREIGN KEY (`asc_id`) REFERENCES `entity_ascs` (`entity_id`) ON DELETE CASCADE, 
CONSTRAINT `asc_hospital_affiliations_ibfk_2` FOREIGN KEY (`hospital_id`) REFERENCES `entity_hospitals` (`entity_id`) ON DELETE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Field Information

`asc_id` the identifying number for an asc that is the same as the asc_id in the entity_asc table 
`hospital_id` the identifying number for a hospital that is the same as the entity_id in the entity_hospital table 
`type` outlines the details of the relationship between the hospital and the astc 
asc_id and hospital_id combine to form the concatenated key for the table 
asc_id and hospital_id are foreign keys that link to entity_ascs and entity_hospitals respectively

Fields

PRIMARY KEY(`astc_id`, `hospital_id`), 
CONSTRAINT `astc_hospital_affiliations_ibfk_1` FOREIGN KEY (`astc_id`) REFERENCES `entity_astcs` (`entity_id`) ON DELETE CASCADE 
CONSTRAINT `astc_hospital_affiliations_ibfk_2` FOREIGN KEY (`hospital_id`) REFERENCES `entity_hospitals` (`entity_id`) ON DELETE CASCADE

Notes

This table is specific to Illinois ASC and hospital links, as we currently only have data on ASCs from Illinois Department of Public Health