create_tables.sql

CREATE TABLE IF NOT EXISTS public.hospital_locations(
provider_id varchar(32) PRIMARY KEY,
hospital_name text NOT NULL,
address text NOT NULL,
city varchar(64) NOT NULL,
state varchar(32) NOT NULL,
zip_code varchar(32) NOT NULL,
county_name varchar(32),
phone_number numeric,
hospital_type varchar(128),
hospital_ownership varchar(256),
emergency_services boolean,
meets_criteria_for_meaningful_use_of_EHRs boolean
);


CREATE TABLE IF NOT EXISTS public.hospital_ratings(
provider_id varchar(32) PRIMARY KEY,
hospital_overall_rating varchar(32),
hospital_overall_rating_footnote text,
mortality_national_comparison text,
mortality_national_comparison_footnote text,
safety_of_care_national_comparison text,
safety_of_care_national_comparison_footnote text,
readmission_national_comparison text,
readmission_national_comparison_footnote text,
patient_experience_national_comparison text,
patient_experience_national_comparison_footnote text,
effectiveness_of_care_national_comparison text,
effectiveness_of_care_national_comparison_footnote text,
timeliness_of_care_national_comparison text,
timeliness_of_care_national_comparison_footnote text,
efficient_use_of_medical_imaging_national_comparison text,
efficient_use_of_medical_imaging_national_comparison_footnote text
);


CREATE TABLE IF NOT EXISTS public.low_income_area(
zip_code varchar(32) PRIMARY KEY,
city varchar(32) Not Null,
state varchar(32) NOT NULL,
county varchar(32) NOT NULL,
zip_code_designation varchar(32) NOT NULL
);

CREATE TABLE IF NOT EXISTS public.income_per_zip(
zip_code varchar(32) PRIMARY KEY,
total_population numeric NOT NULL,
total_income numeric NOT NULL,
avg_income numeric NOT NULL
);