Domain 3 — Services & Pricing
The service model keeps the original three admin layers (category → option group → option value) and two nurse layers (variant → variant option). This EAV-style configurability is deliberately kept — it lets admins add a new pricing dimension (e.g. "شبانهروزی / live-in", "number of patients") without a migration, and lets each nurse price every combination independently. The only addition is a denormalized read model for search.
service_categories [CORE]
Role: Admin-managed top-level care types (Elderly, Post-Surgery, Infant, Chronic, Companionship). The primary search dimension. Why admin-managed rows: the catalog is a business lever, not a code constant. Fields unchanged. Relations: 1:N → service_option_groups, nurse_service_variants.
service_option_groups [CORE] / service_option_values [CORE]
Role: The configurable dimensions (group, e.g. "نوع شیفت") and their concrete choices (value, e.g. "شبانهروزی"). A NULL service_category_id on a group = cross-category (e.g. shift type applies everywhere). Why two tables: separating dimension from choice lets a dimension be is_required and reused across categories. Fields unchanged. Relations: service_categories 1:N service_option_groups 1:N service_option_values.
nurse_service_variants [CORE]
Role: The atomic bookable unit — a specific nurse offering a category with a chosen option combination at a price. Why this is the bookable unit (not the nurse): a nurse offers many priced combinations; search and booking operate on the exact thing the customer pays for. The price_unit (per_hour/per_session/per_half_day/per_day/per_24h) determines display and, with session_count, the engagement total. Fields unchanged. Consider a uniqueness strategy on (nurse_id, category, option-set) to prevent duplicate identical listings. Relations: N:1 → nurse_profiles, service_categories; 1:N → nurse_service_variant_options, booking_requests.
nurse_service_variant_options [CORE]
Role: The option values that define a variant's configuration. Why: one row per dimension makes the variant's meaning explicit and queryable. UNIQUE(variant_id, option_group_id) — one value per dimension. Relations: N:1 → nurse_service_variants, service_option_groups, service_option_values.
nurse_search_index [CORE] — NEW
Role: A denormalized, one-row-per-bookable-variant read model holding every search-relevant field flat: nurse (verified + accepting), variant (category, price, unit), areas (city/district), gender, rating, partner center. Why: nurse search otherwise needs 4+ joins (nurse_profiles → variants → variant_options → service_areas) plus a rating sort from day one — slow at modest scale. A maintained-on-write flat table is far cheaper than adding Elasticsearch at MVP stage.
| Field | Type | Notes |
|---|---|---|
id | BIGINT PK | |
variant_id | BIGINT FK → nurse_service_variants | |
nurse_id | BIGINT FK → nurse_profiles | |
service_category_id | BIGINT FK | |
price, price_unit | … | Copied from variant |
city_id, district_id | BIGINT | One row per covered area (fan-out) |
nurse_gender | NVARCHAR(10) | For same-gender filtering |
average_rating, total_reviews, total_completed_bookings | … | Copied from profile |
is_searchable | BIT | True only when nurse is_verified=1, not suspended, accepting, and variant is_active=1 |
updated_at | DATETIME2 |
Relations (read-only projection): maintained on writes to nurse_profiles, nurse_service_variants, nurse_service_areas, reviews. Invariant: a row is is_searchable=1 only when its source nurse/variant are bookable.
nurse_availability_slots [MVP] / nurse_availability_exceptions [MVP]
Role: Recurring weekly windows + date overrides. Why soft-constraint: these are guidance only — the nurse still accepts/rejects each request; they inform search but never block a request. day_of_week uses the Shamsi week (0=Saturday … 6=Friday). Fields unchanged, with CHECK end_time > start_time. Relations: N:1 → nurse_profiles.