Skip to content

Data Model

Conceptual Model Diagram

erDiagram
  Catalog ||--o{ Schema : "contains"
  Schema ||--o{ AggregationView : "contains"
  AggregationView ||--o{ Question : "has"
  Question ||--o{ ChoiceRow : "has"
  RespondentAttributes }o--|| AggregationView : "source of aggregation (not read by the app)"

Entity Overview

Entity Description
Catalog A Unity Catalog catalog. Fixed to cs in the app.
Schema A schema under the catalog. Fixed to cs_dm in the app.
AggregationView A pre-aggregated view corresponding to one survey. The unit selected in the UI.
Question A survey question identified by question (plus question_number / question_type) within the view.
ChoiceRow One row per question-and-choice pair, carrying sum_answer, total, and rate.
RespondentAttributes Table holding per-respondent attributes (ut_dm_ana_jreslso_tokyo). It is the source of the aggregation but is not currently read by the app.

Business Rules

  • The app reads pre-aggregated views only. It never handles raw response data.
  • Every aggregation query filters by is_comp = 'ใ™ในใฆใฎๆœ‰ๅŠนๅ›ž็ญ”' (backend/sql/table_data.py). This is the single branching point if comparison axes by attribute are ever needed.
  • The frontend assumes question_type takes two values: Text Select (chart view) and Free Text (heatmap + response table). Any other value appears on neither screen.
  • Questions are grouped by the question string. question_number is used only for display labels and sorting, not as a key.
  • Catalog and schema are hardcoded to cs / cs_dm in frontend/src/constants/dwh.ts. Only the table is selectable in the UI.
  • The API row model QuestionDataRow sets extra="allow", so table-specific extra columns pass straight through to the frontend.