OWC

Data Model

Tables

• surveys
• categories
• survey_questions
• countries
• cities
• companies
• verification_requests
• submissions
• survey_responses
• submission_category_results
• submission_results
• company_category_results
• company_results

Notes

• survey_responses is one row per (submission_id, question_id).
• category_max_score remains unchanged by design.
• submission_results and company_results remain persistent.
• verification_requests and submissions are linked to companies and carry country_id and city_id.
• one submission belongs to one company, one country, and one city.
• company aggregate rows are scoped by company + country + city.

Key constraints

• uq_survey_question_code (survey_id, question_code)
• uq_submission_question (submission_id, question_id)
• uq_submission_category_result_submission_category (submission_id, category_id)
• uq_company_category_result_company_geo_category (company_id, country_id, city_id, category_id)
• unique verification_requests.token
• status checks on verification_requests and submissions

ER diagram

erDiagram
    surveys ||--o{ survey_questions : contains
    categories ||--o{ survey_questions : groups
    surveys ||--o{ submissions : has
    countries ||--o{ cities : contains
    countries ||--o{ companies : classifies
    cities ||--o{ companies : identifies
    surveys ||--o{ verification_requests : used_for
    verification_requests ||--|| submissions : creates
    submissions ||--o{ survey_responses : includes
    survey_questions ||--o{ survey_responses : answered_by
    submissions ||--o{ submission_category_results : aggregates
    submissions ||--|| submission_results : result
    companies ||--o{ company_category_results : aggregates
    companies ||--o{ company_results : aggregates
    surveys ||--o{ company_results : aggregates