-- ## iitp DB Schemas - Initial setup - Creation and Delete if tables exists -- ## ver 0.0.5 last updated data : 2025.10.02 -- ## Only for PostgreSQL -- ## 장애인 자립 지원 허브 플랫폼 용 -- ## -- ################################################ -- ## 시스템 테이블 생성 -- ############################################### -- #### [주의] 통계 데이터 수집으로 sys_stats_src_api_info, stats_src_data_info 테이블의 데이터가 변경되면, sys_data_summary_info 테이블 데이터도 update 해야 함. -- #### [주의] mv_poi, poi_*, emp_dis_* 테이블의 데이터가 변경되면, sys_data_summary_info 테이블 데이터도 update 해야 함. -- # public.sys_data_summary_info definition DROP TABLE IF EXISTS public.sys_data_summary_info; CREATE TABLE public.sys_data_summary_info ( data_id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 CYCLE) NOT NULL, -- system id, 고유 식별자 (자동 증가) target_data_id int4, -- Target Data info sys ID, data_type 에 따라서 id 참조. 예) stats_src_data_info.src_data_id data_type varchar(16) NOT NULL, -- Data Type (basic/poi/emp) self_rlty_type varchar(24), -- 자립 테마 타입 (phys,emo, econ,soc) category varchar(24), -- 카테고리 (기초, 신체 자립 현황, 의료 자립현황, 보조기기 사용 현황, 진로 교육 현황, 고용 현황, 사회망 현황, ...) title varchar(300) NOT NULL, -- 데이터명 sys_tbl_id varchar(40) NOT NULL, -- iitp 데이터 테이블 ID src_org_name varchar(50) NOT NULL, -- 데이터 제공처(기관명) src_latest_chn_dt varchar(12) NOT NULL, -- 수집기관 최종 자료갱신일 (예:2024-07-19) sys_data_ref_dt varchar(12) NULL, -- 데이터를 iitp 시스템에서 마지막 수집/참조 일자 (예:2024-07-19) sys_data_reg_dt varchar(12) NOT NULL, -- 데이터를 iitp 시스템에 등록한 일자 (예:2024-07-19) data_keywords varchar(220), -- 데이터 키워드 (최대 3개), 데이터 포맷 :: [,,] 형식 data_format varchar(60), -- 제공 데이터 포맷 ([csv, json]) data_desc varchar(300), -- 데이터 설명 data_usage_scope varchar(300), -- 이용 데이터 범위 설명 open_api_url varchar(300) NOT NULL, -- open api req url status char(1) DEFAULT 'A' NOT NULL, -- 데이터 상태, "data_status" comm code 참조 del_yn char(1) DEFAULT 'N'::bpchar NOT NULL, -- 삭제여부 (Y: 삭제) created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 deleted_at timestamptz NULL, -- 삭제 일시 (논리 삭제 시 기록) CONSTRAINT pkey_sys_data_sumary_info PRIMARY KEY (data_id) ); CREATE UNIQUE INDEX uidx_sys_data_sumary_info_sys_tbl_id ON public.sys_data_summary_info USING btree (sys_tbl_id); CREATE INDEX idx_sys_data_sumary_info_title ON public.sys_data_summary_info USING btree (title); CREATE INDEX idx_sys_data_sumary_info_data_type ON public.sys_data_summary_info USING btree (data_type); CREATE INDEX idx_sys_data_sumary_info_self_rlty_type ON public.sys_data_summary_info USING btree (self_rlty_type); COMMENT ON TABLE public.sys_data_summary_info IS 'IITP 전체 데이터별 요약 정보'; -- Column comments COMMENT ON COLUMN public.sys_data_summary_info.data_id IS 'system id, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.sys_data_summary_info.target_data_id IS 'Target Data info sys ID, target_data_type 에 따라서 id 참조. 예) stats_src_data_info.src_data_id'; COMMENT ON COLUMN public.sys_data_summary_info.data_type IS 'Target Data Type (basic/poi/emp)'; COMMENT ON COLUMN public.sys_data_summary_info.self_rlty_type IS '자립 테마 타입 (phys,emo, econo,soc)'; COMMENT ON COLUMN public.sys_data_summary_info.category IS '카테고리 (기초, 신체 자립 현황, 의료 자립현황, 보조기기 사용 현황, 진로 교육 현황, 고용 현황, 사회망 현황, ...)'; COMMENT ON COLUMN public.sys_data_summary_info.title IS '데이터명'; COMMENT ON COLUMN public.sys_data_summary_info.sys_tbl_id IS 'iitp 데이터 목록 테이블 ID'; COMMENT ON COLUMN public.sys_data_summary_info.src_org_name IS '데이터 제공처(기관명)'; COMMENT ON COLUMN public.sys_data_summary_info.src_latest_chn_dt IS '수집기관 최종 자료갱신일 (예:2024-07-19)'; COMMENT ON COLUMN public.sys_data_summary_info.sys_data_ref_dt IS '데이터를 iitp 시스템에서 마지막 수집/참조 일자 (예:2024-07-19)'; COMMENT ON COLUMN public.sys_data_summary_info.sys_data_reg_dt IS '데이터를 iitp 시스템에 등록한 일자 (예:2024-07-19)'; COMMENT ON COLUMN public.sys_data_summary_info.data_keywords IS '데이터 키워드 (최대 3개), 데이터 포맷 :: [,,] 형식'; COMMENT ON COLUMN public.sys_data_summary_info.data_format IS '제공 데이터 포맷 ([csv, json])'; COMMENT ON COLUMN public.sys_data_summary_info.data_desc IS '데이터 설명'; COMMENT ON COLUMN public.sys_data_summary_info.data_usage_scope IS '이용 데이터 범위 설명'; COMMENT ON COLUMN public.sys_data_summary_info.open_api_url IS 'open api req url'; COMMENT ON COLUMN public.sys_data_summary_info.status IS '"data_status" comm code 참조'; COMMENT ON COLUMN public.sys_data_summary_info.del_yn IS '삭제여부 (Y: 삭제)'; COMMENT ON COLUMN public.sys_data_summary_info.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.sys_data_summary_info.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.sys_data_summary_info.deleted_at IS '삭제 일시 (논리 삭제 시 기록)'; -- # public.selfdiag_data_category definition DROP TABLE IF EXISTS public.selfdiag_data_category; CREATE TABLE selfdiag_data_category ( id SERIAL PRIMARY KEY, category VARCHAR(50) NOT NULL, -- 카테고리: theme, data_type name VARCHAR(100) NOT NULL, -- 카테고리명: 자립테마별, 데이터 유형별 menu_id VARCHAR(50) NOT NULL, -- 메뉴 id (theme : phy,emo, econ,soc, data_type : basic, poi, emp) menu_name VARCHAR(100) NOT NULL, -- 메뉴 항목명: 신체적 자립, 정서적 자립 등 description VARCHAR(600) NOT NULL, -- 메뉴별 간단한 설명 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, updated_at timestamptz ); COMMENT ON TABLE public.selfdiag_data_category IS 'IITP 데이터 테이블 분류 정보'; -- 컬럼 설명 추가 COMMENT ON COLUMN selfdiag_data_category.category IS '카테고리: theme, data_type'; COMMENT ON COLUMN selfdiag_data_category.name IS '카테고리명: 자립테마별, 데이터 유형별, '; COMMENT ON COLUMN selfdiag_data_category.menu_id IS '메뉴 id (theme : phy,emo, econ,soc, data_type : basic, poi, emp)'; COMMENT ON COLUMN selfdiag_data_category.menu_name IS '메뉴 항목명: 신체적 자립, 정서적 자립 등'; COMMENT ON COLUMN selfdiag_data_category.description IS '메뉴별 상세 설명'; COMMENT ON COLUMN selfdiag_data_category.created_at IS '데이터 생성일'; COMMENT ON COLUMN selfdiag_data_category.updated_at IS '데이터 수정일'; -- 샘플 데이터 INSERT INSERT INTO selfdiag_data_category (category, name, menu_id, menu_name, description) VALUES ('theme', '자립테마별', 'phys', '신체적 자립', '신체적 자립 수준 및 건강 상태 등 신체 기능과 관련된 데이터'), ('theme', '자립테마별', 'emo', '정서적 자립', '심리적 안정 및 지원 등 정서 안정과 관련된 데이터'), ('theme', '자립테마별', 'econ', '경제적 자립', '장애인 진로교육 및 고용 현황 등 일자리와 관련된 데이터'), ('theme', '자립테마별', 'soc', '사회적 자립', '사회복지시설 및 지역사회 참여 현황 등 사회적 기능과 관련된 데이터'), ('data_type', '데이터 유형별', 'basic', '기초 데이터', '장애인과 관련된 기본 통계 및 기초 자료'), ('data_type', '데이터 유형별', 'poi', '이동권 데이터', '교통·보행 등 장애인 이동 및 접근성과 관련된 자료'), ('data_type', '데이터 유형별', 'emp', '일자리 데이터', '고용 현황, 취업 지원, 직업 활동과 관련된 자료'), ('self_rlty_type', '자립유형', 'basic', '기초', '기본 적인 장애 자립 관련 정보(정책, 기관, 시설)'), ('self_rlty_type', '자립유형', 'phys', '신체', '신체적 자립 관련 정보 (정책, 기관, 시설)'), ('self_rlty_type', '자립유형', 'emo', '정서', '정서적 자립 관련된 정보 (정책, 기관, 시설)'), ('self_rlty_type', '자립유형', 'econ', '경제', '경제적 자립 관련된 정보 (정책, 기관, 시설)'), ('self_rlty_type', '자립유형', 'soc', '사회', '사회적 자립 관련된 정보 (정책, 기관, 시설)'); -- # public.selfdiag_policy definition DROP TABLE IF EXISTS public.selfdiag_policy; CREATE TABLE selfdiag_policy ( policy_id SERIAL PRIMARY KEY, -- PK category VARCHAR(90) NOT NULL, -- 구분 (예: 생활 안정 지원, 생활요금 감면) policy_name VARCHAR(300) NOT NULL, -- 항목명 (예: 장애인연금, 장애수당) self_rlty_type VARCHAR(50), -- 관련 자립 유형 (기초, 신체, 경제, 정서, 사회 등) region VARCHAR(100), -- 사업 단위 (전국, 시군구 등) gender VARCHAR(50), -- 대상 성별 (남성, 여성, "여성, 남성") age_cond VARCHAR(50), -- 연령 조건 (minor=미성년자, adult=성인, all=미성년+성인') dis_level VARCHAR(50), -- 장애 정도 (중증, 경증) fin_cond VARCHAR(100), -- 재정 조건 (중위소득 50~100% 등) target VARCHAR(3000), -- 대상자 설명 benefit VARCHAR(6000), -- 지원 내용 설명 link VARCHAR(512), -- 상세 이동 링크(URL) created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성 시각 updated_at timestamptz -- 수정 시각 ); -- COMMENT 구문 COMMENT ON TABLE selfdiag_policy IS '장애인 자립 자가진단 - 정책·서비스 정보'; COMMENT ON COLUMN selfdiag_policy.policy_id IS 'PK: 데이터 고유 ID'; COMMENT ON COLUMN selfdiag_policy.category IS '지원 구분 (생활 안정 지원, 생활요금 감면 등)'; COMMENT ON COLUMN selfdiag_policy.policy_name IS '지원 항목명 (장애인연금, 장애수당 등)'; COMMENT ON COLUMN selfdiag_policy.self_rlty_type IS '관련 자립 유형 (기초, 신체, 경제, 정서, 사회 등)'; COMMENT ON COLUMN selfdiag_policy.region IS '사업 단위 (전국, 시/군/구 등)'; COMMENT ON COLUMN selfdiag_policy.gender IS '성별 조건 (여성, 남성, "여성, 남성")'; COMMENT ON COLUMN selfdiag_policy.age_cond IS '연령 조건 (minor=미성년자, adult=성인, all=미성년+성인)'; COMMENT ON COLUMN selfdiag_policy.dis_level IS '장애 정도 조건 (중증, 경증, 혼합)'; COMMENT ON COLUMN selfdiag_policy.fin_cond IS '재정 조건 (중위소득 기준 등)'; COMMENT ON COLUMN selfdiag_policy.target IS '대상자 상세 설명'; COMMENT ON COLUMN selfdiag_policy.benefit IS '지원 내용 상세'; COMMENT ON COLUMN selfdiag_policy.link IS '상세 페이지 URL'; COMMENT ON COLUMN selfdiag_policy.created_at IS '데이터 생성 시각'; COMMENT ON COLUMN selfdiag_policy.updated_at IS '데이터 최종 수정 시각'; -- # public.selfdiag_provider definition DROP TABLE IF EXISTS public.selfdiag_provider; CREATE TABLE selfdiag_provider ( provider_id SERIAL PRIMARY KEY, -- PK provider_name VARCHAR(200) NOT NULL, -- 제공기관명 (지점명까지 포함) service_name VARCHAR(200) NOT NULL, -- 제공 서비스명 address VARCHAR(300) NOT NULL, -- 주소 (시군구 + 상세주소 포함) phone VARCHAR(50), -- 전화번호 rep_name VARCHAR(100), -- 대표자명 (선택 입력) description VARCHAR(900), -- 비고/기관 상세설명 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성일시 updated_at timestamptz -- 수정일시 ); COMMENT ON TABLE selfdiag_provider IS '장애인 자립 자가진단 - 정책 제공 기관(Provider) 정보 테이블'; COMMENT ON COLUMN selfdiag_provider.provider_id IS 'PK, 제공 기관 식별자'; COMMENT ON COLUMN selfdiag_provider.provider_name IS '제공기관명 (지점명 포함)'; COMMENT ON COLUMN selfdiag_provider.service_name IS '해당 기관이 제공하는 서비스명'; COMMENT ON COLUMN selfdiag_provider.address IS '기관 주소 (시군구, 상세주소 포함)'; COMMENT ON COLUMN selfdiag_provider.phone IS '기관 전화번호'; COMMENT ON COLUMN selfdiag_provider.rep_name IS '대표자명'; COMMENT ON COLUMN selfdiag_provider.description IS '기관 및 서비스에 대한 상세 설명, 비고'; COMMENT ON COLUMN selfdiag_provider.created_at IS '데이터 생성일시'; COMMENT ON COLUMN selfdiag_provider.updated_at IS '데이터 최종 수정일시'; -- # public.selfdiag_facility definition DROP TABLE IF EXISTS public.selfdiag_facility; CREATE TABLE selfdiag_facility ( facility_id SERIAL PRIMARY KEY, device VARCHAR(300), -- 설치 기기 install_area VARCHAR(100), -- 설치 지역 install_site VARCHAR(300), -- 설치 장소 install_spot VARCHAR(300), -- 설치 위치 (건물 내 위치 등) hang_dong VARCHAR(60), -- 행정동 address VARCHAR(300), -- 상세 주소 opening_hours VARCHAR(300), -- 이용 시간 quantity INT DEFAULT 0, -- 설치 대수 note VARCHAR(900), -- 비고 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, updated_at timestamptz ); COMMENT ON TABLE selfdiag_provider IS '장애인 자립 자가진단 - 정책 제공 시설(facility) 정보 테이블'; COMMENT ON COLUMN selfdiag_facility.facility_id IS 'PK, 설치 기기 식별자'; COMMENT ON COLUMN selfdiag_facility.device IS '설치 기기명'; COMMENT ON COLUMN selfdiag_facility.install_area IS '설치 지역(시/구)'; COMMENT ON COLUMN selfdiag_facility.install_site IS '설치 장소명'; COMMENT ON COLUMN selfdiag_facility.install_spot IS '건물 내 설치 위치 상세'; COMMENT ON COLUMN selfdiag_facility.hang_dong IS '행정동'; COMMENT ON COLUMN selfdiag_facility.address IS '상세 주소'; COMMENT ON COLUMN selfdiag_facility.opening_hours IS '이용 시간'; COMMENT ON COLUMN selfdiag_facility.quantity IS '설치 대수'; COMMENT ON COLUMN selfdiag_facility.note IS '비고'; COMMENT ON COLUMN selfdiag_facility.created_at IS '데이터 생성일시'; COMMENT ON COLUMN selfdiag_facility.updated_at IS '데이터 최종 수정일시';