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

field	type	attributes	notes	example
relationship_asc_hospitals.asc_id	BIGINT(20)	UNSIGNED NOT NULL	Provided by Pellucid	01234567890
relationship_asc_hospitals.hospital_id	BIGINT(20)	UNSIGNED NOT NULL	Provided by Pellucid, known as entities.entity_id in entity_hospitals	01234567890
relationship_asc_hospitals.type	enum('asc_affiliation','hospital-ownership')	DEFAULT 'asc_affiliation'	relationship between the two	'asc_affiliation'

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

Browser not supported