Okul.com.tr — Eğitim Ekosistemi

Key Relationships & Entity Map

4 dakika okuma süresi

Project: Okul Database · Hub: Okul Database — Guides

Key Relationships & Entity Map

Top-Level Entity Hierarchy

                    ┌─────────────────┐
                    │     schools     │  ← Core entity
                    └────────┬────────┘
                             │ FK in many tables
          ┌──────────────────┼────────────────────┐
          │                  │                    │
   ┌──────┴──────┐    ┌──────┴──────┐    ┌───────┴────────┐
   │  customers  │    │    leads    │    │ school_filters │
   │  (B2B firm) │    │  (D2C form) │    │ (denorm cache) │
   └──────┬──────┘    └──────┬──────┘    └────────────────┘
          │                  │
   ┌──────┴──────┐    ┌──────┴──────┐
   │  customer   │    │    users    │
   │  agreements │    │  (parents)  │
   └──────┬──────┘    └─────────────┘
          │
   ┌──────┴──────┐
   │  payments   │
   └─────────────┘

B2B Revenue Flow

customers (firm)
  │
  ├──< customer_users           M2M: firm's portal users
  │        └──< customer_user_schools  per-school access grants
  │
  ├──< customer_agreements      one contract per school per period
  │        ├── agreement_type   billing model (fixed/subscription/arge)
  │        ├── products         JSON: product list
  │        │
  │        ├──< payments        installments or one-off
  │        │        └──< payment_transactions  gateway log per attempt
  │        │
  │        └──< customer_agreements_renew_discounts  renewal discounts
  │
  └──< customer_scores          health score (demand/payment/responsiveness...)

D2C Lead Flow

users (parent/student)
  │
  └──< school_offers            submitted inquiry form
           └──> leads           one lead per school per offer
                   ├── stage_id          Pipedrive stage (see ref-lead-stages)
                   ├── approved_status_id  internal qualification status
                   ├── school_answered_deal_status  school's outcome
                   ├── customer_viewed_at  when school saw the lead
                   └──> pipedrive_deals  CRM sync (has lag)

School Entity Hub

schools
  ├── school_filters       ← 1:1, denormalized cache (USE THIS FOR QUERIES)
  ├── school_statistics    ← 1:1, analytics copy
  ├── campuses             ← 1:N, physical locations
  ├── school_details       ← 1:N, EAV extended attributes
  ├── school_fees          ← 1:N, tuition fees by fee_type
  ├── school_features      ← 1:N, template-based features
  ├── school_facilities    ← M:N via pivot, with facilities table
  ├── school_services      ← M:N via pivot, with services table
  ├── school_shifts        ← M:N via pivot, with shifts table
  ├── school_learning_languages ← M:N via pivot
  ├── school_comments      ← 1:N, user reviews (requires approved_at)
  ├── school_announcements ← 1:N, school announcements
  ├── school_interactions  ← 1:N, CRM contact log (is_latest flag)
  ├── school_daily_logs    ← 1:N, daily engagement metrics
  ├── school_badges        ← 1:N, ranking badges
  ├── school_product_prices← 1:N, school-specific pricing
  ├── school_inbound_call_logs ← 1:N, call tracking
  ├── school_name_changes  ← 1:N, historical renames
  ├── leads                ← 1:N via leads.school_id
  ├── discounts            ← 1:N, fee discounts
  └── media                ← via media_type_id=2, row_id=school_id

Location Hierarchy

locations (self-referential)
  parent_id = NULL  → Province (81 total)
  parent_id = prov  → District (hundreds)

schools.city_id   → locations (province)
schools.county_id → locations (district)
campuses.location_id → locations (any level)

school_filters.city_name / county_name → already denormalized, no join needed

Education Hierarchy

universities
  └──< faculty_programs ──< faculty_program_info (EAV)
           └──< faculty_program_winners  (admission scores)

colleges
  ├──< campuses            (physical locations)
  └── college_schools (M:N) ←→ schools

User ↔ Customer Mapping

users.customer_id IS NULL    → Pure D2C user
users.customer_id = X        → User is linked to customer X

customer_users (M2M bridge):
  customer_users.user_id     → users.id
  customer_users.customer_id → customers.id
  customer_users.role_id     → configs (type='customer_user_role')

customer_user_schools (access control):
  customer_user_id → customer_users.id
  school_id        → schools.id
  (a user can only see schools they're explicitly granted access to)

Media Polymorphic

media
  media_type_id → media_types.id  (which entity type)
  media_role_id → media_roles.id  (what purpose)
  row_id        → the entity's ID

Example: School profile photo
  media_type_id = 2 (School)
  media_role_id = 3 (profile)
  row_id        = school_id

See ref-media-roles for full type/role mapping.

configs Lookup Pattern

Many tables store lookup IDs that resolve against configs:

leads.approved_status_id      → configs WHERE type='offer_approved_status'
leads.school_answered_deal_status → configs WHERE type='school_answered_deal_status'
leads.deal_cause_of_reason    → configs WHERE type='deal_cause_of_reason'
school_interactions.reason_id → configs WHERE type='interaction_reason'
school_interactions.result_id → configs WHERE type='interaction_result'
school_interactions.type_id   → configs WHERE type='interaction_type'
customer_users.role_id        → configs WHERE type='customer_user_role'
customer_users.title_id       → configs WHERE type='customer_user_title'

Join pattern:
JOIN configs c ON c.type = '<type_name>'
  AND c.item_id = <the_id_column>
  AND c.deleted_at IS NULL

See ref-configs for all type names.

Common Multi-Table Joins

School + Location + Type (use school_filters instead)

-- If you MUST join schools:
SELECT s.name, st.type, l.name as city
FROM schools s
JOIN schools_types st ON st.id = s.school_type_id
JOIN locations l ON l.id = s.city_id
WHERE s.deleted_at IS NULL
-- BUT: school_filters has all of this pre-joined

Lead + School + Customer

SELECT l.id, l.created_at, s.name as school,
       c.firm_name, sf.city_name
FROM leads l
JOIN schools s ON s.id = l.school_id
JOIN customers c ON c.id = l.customer_id
JOIN school_filters sf ON sf.school_id = l.school_id AND sf.deleted_at IS NULL
WHERE l.deleted_at IS NULL AND (l.is_test = 0 OR l.is_test IS NULL)

Payment + Agreement + Customer

SELECT p.paid_at, p.amount, p.status,
       ca.agreement_date, ca.agreement_type,
       c.firm_name
FROM payments p
JOIN customer_agreements ca ON ca.id = p.customer_agreement_id
JOIN customers c ON c.id = ca.customer_id
WHERE p.deleted_at IS NULL AND p.status = 'success'

Grafik Görünümü

Backlinkler