SKTHON-Hackathon-Oide
Tables (17)
administrative_area
- id — BIGINT — Primary key — NOT NULL
- area_type — VARCHAR(16) — NOT NULL
- code — VARCHAR(20) — NOT NULL
- name — VARCHAR(100) — NOT NULL
- parent_id — BIGINT — Foreign key → administrative_area.id
- geom — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
area_business_feature
- id — BIGINT — Primary key — NOT NULL
- commercial_area_id — BIGINT — Foreign key → commercial_area.id — NOT NULL
- business_category_code — VARCHAR(30) — NOT NULL
- base_period — DATE — NOT NULL
- store_count — INTEGER
- estimated_sales_amount — NUMERIC(18,2)
- closure_rate — NUMERIC(6,4)
- competition_score — NUMERIC(8,4)
- demand_score — NUMERIC(8,4)
- updated_at — TIMESTAMP — NOT NULL
building
- id — BIGINT — Primary key — NOT NULL
- source_building_id — VARCHAR(100) — NOT NULL — Legacy source key; progressively map to gis_building.gis_building_id. GIS attributes are authoritative in gis_building.
- pnu — VARCHAR(30)
- legal_dong_code — VARCHAR(20)
- legal_dong_id — BIGINT — Foreign key → administrative_area.id
- road_address — VARCHAR(300)
- jibun_address — VARCHAR(300)
- building_name — VARCHAR(200)
- main_use_code — VARCHAR(30)
- main_use_name — VARCHAR(100)
- land_area — NUMERIC(14,2)
- building_area — NUMERIC(14,2)
- total_floor_area — NUMERIC(14,2)
- building_coverage_ratio — NUMERIC(6,2)
- floor_area_ratio — NUMERIC(6,2)
- ground_floor_count — INTEGER
- underground_floor_count — INTEGER
- approval_date — DATE
- geom — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
commercial_area
- id — BIGINT — Primary key — NOT NULL
- commercial_area_code — VARCHAR(30) — NOT NULL
- commercial_area_name — VARCHAR(150) — NOT NULL
- commercial_area_type — VARCHAR(50)
- geom — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
commercial_area_building
- commercial_area_id — BIGINT — Primary key — NOT NULL
- gis_building_id — BIGINT — Foreign key → gis_building.id — NOT NULL
- overlap_ratio — NUMERIC(6,5)
- calculated_at — TIMESTAMP — NOT NULL
commercial_area_feature
- id — BIGINT — Primary key — NOT NULL
- commercial_area_id — BIGINT — Foreign key → commercial_area.id — NOT NULL
- base_period — DATE — NOT NULL
- total_floating_population — INTEGER
- age_20_ratio — NUMERIC(6,4)
- age_30_ratio — NUMERIC(6,4)
- avg_rent_per_sqm — NUMERIC(14,2)
- vacancy_rate — NUMERIC(5,2)
- residential_population — INTEGER
- worker_population — INTEGER
- updated_at — TIMESTAMP — NOT NULL
floating_population_grid
- id — BIGINT — Primary key — NOT NULL
- grid_code — VARCHAR(50) — NOT NULL
- geom — GEOMETRY(POLYGON,5186) — NOT NULL
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
floating_population_statistic
- id — BIGINT — Primary key — NOT NULL
- grid_id — BIGINT — Foreign key → floating_population_grid.id — NOT NULL
- base_period — DATE — NOT NULL
- time_slot — VARCHAR(20) — NOT NULL
- total_population — INTEGER
- male_population — INTEGER
- female_population — INTEGER
- age_10s — INTEGER
- age_20s — INTEGER
- age_30s — INTEGER
- age_40s — INTEGER
- age_50s — INTEGER
- age_60_plus — INTEGER
- residential_population — INTEGER
- worker_population — INTEGER
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
gis_building
- id — BIGINT — Primary key — NOT NULL
- import_id — BIGINT — Foreign key → gis_dataset_import.id — NOT NULL
- gis_building_id — VARCHAR(50) — NOT NULL
- ufid — VARCHAR(50)
- pnu — VARCHAR(19)
- legal_dong_code — VARCHAR(10)
- legal_dong_name — VARCHAR(100)
- sigungu_code — VARCHAR(5)
- lot_main_no — VARCHAR(10)
- lot_sub_no — VARCHAR(10)
- building_category — VARCHAR(50)
- building_use_code — VARCHAR(20)
- building_use_name — VARCHAR(100)
- structure_code — VARCHAR(20)
- structure_name — VARCHAR(100)
- approval_date — DATE
- building_height_m — NUMERIC(10,2)
- building_area_sqm — NUMERIC(14,2)
- total_floor_area_sqm — NUMERIC(14,2)
- roof_height_m — NUMERIC(10,2)
- above_ground_floor_count — INTEGER
- below_ground_floor_count — INTEGER
- geometry — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
- centroid — GEOMETRY(POINT,5186)
- source_updated_at — DATE
- is_current — BOOLEAN — NOT NULL
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
- building_id — BIGINT — Foreign key → building.id — NOT NULL — Optional service-domain building; one building can retain multiple GIS import records.
gis_dataset_import
- id — BIGINT — Primary key — NOT NULL
- dataset_code — VARCHAR(50) — NOT NULL
- source_file_name — VARCHAR(255) — NOT NULL
- source_published_at — DATE
- imported_at — TIMESTAMP — NOT NULL
- source_crs — VARCHAR(30) — NOT NULL
- record_count — INTEGER
- checksum — VARCHAR(128)
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
land_use_zone
- id — BIGINT — Primary key — NOT NULL
- zone_code — VARCHAR(30) — NOT NULL
- zone_name — VARCHAR(100) — NOT NULL
- category — VARCHAR(50)
- geom — GEOMETRY(MULTIPOLYGON,5186) — NOT NULL
recommendation_request
- id — BIGINT — Primary key — NOT NULL
- natural_language_query — TEXT — NOT NULL
- parsed_condition — JSONB — NOT NULL
- weights — JSONB
- created_at — TIMESTAMP — NOT NULL
- user_id — BIGINT — Foreign key → users.ID — NOT NULL
recommendation_result
- id — BIGINT — Primary key — NOT NULL
- request_id — BIGINT — Foreign key → recommendation_request.id — NOT NULL
- building_id — BIGINT — Foreign key → building.id — NOT NULL
- rank — INTEGER — NOT NULL
- total_score — NUMERIC(10,4) — NOT NULL
- score_breakdown — JSONB — NOT NULL
- explanation — TEXT
- created_at — TIMESTAMP — NOT NULL
rent_statistic
- id — BIGINT — Primary key — NOT NULL
- commercial_area_id — BIGINT — Foreign key → commercial_area.id
- administrative_area_id — BIGINT — Foreign key → administrative_area.id
- base_period — DATE — NOT NULL
- avg_rent — NUMERIC(14,2)
- avg_rent_per_sqm — NUMERIC(14,2)
- average_deposit — NUMERIC(18,2)
- vacancy_rate — NUMERIC(5,2)
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
sales_statistic
- id — BIGINT — Primary key — NOT NULL
- commercial_area_id — BIGINT — Foreign key → commercial_area.id — NOT NULL
- business_category_code — VARCHAR(30) — NOT NULL
- base_period — DATE — NOT NULL
- estimated_sales_amount — NUMERIC(18,2)
- transaction_count — BIGINT
- average_transaction_amount — NUMERIC(18,2)
- store_count — INTEGER
- closed_store_count — INTEGER
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
store
- id — BIGINT — Primary key — NOT NULL
- source_store_id — VARCHAR(100) — NOT NULL
- business_category_code — VARCHAR(30) — NOT NULL
- business_category_name — VARCHAR(100) — NOT NULL
- business_subcategory_code — VARCHAR(30)
- business_subcategory_name — VARCHAR(100)
- store_name — VARCHAR(200)
- road_address — VARCHAR(300)
- legal_dong_code — VARCHAR(20)
- geom — GEOMETRY(POINT,5186) — NOT NULL
- opened_at — DATE
- closed_at — DATE
- status — VARCHAR(20) — NOT NULL
- created_at — TIMESTAMP — NOT NULL
- updated_at — TIMESTAMP — NOT NULL
users (유저)
- ID (id) — BIGINT — Primary key — NOT NULL — @auto_increment
- CLERK ID (clerk_id) — VARCHAR(300) — NOT NULL — UNIQUE
- 이름 (name) — VARCHAR(20) — NOT NULL
- 가입 일자 (created_at) — TIMESTAMP — NOT NULL — NOW()
- 탈퇴 일자 (deleted_at) — TIMESTAMP