Database Schema

Database Schema

Analysis Basis

This summary is based on the database artifacts found in the repository:

• the full schema dump in motmaina-website/migrations/20250722_full_ddl_dump.sql
• incremental SQL migrations in motmaina-website/migrations

I did not find a dedicated seeder structure in the repository. In practice, the schema understanding comes from:

• DDL table creation statements
• later ALTER TABLE, CREATE INDEX, and INSERT migrations
• configuration and route usage that reveal the active product domains

Schema Shape

The current database is a large monolithic relational schema that supports multiple product surfaces inside one application.

It includes:

• public website content
• customer accounts
• consultations and appointments
• courses and subscriptions
• notifications and operational logs
• admin RBAC
• integrations and payment records

This is not a narrowly scoped website database. It is the shared operational database for the broader motmaina platform.

Main Functional Domains

Relationship Diagrams

The following Mermaid diagrams simplify the most important relationships in the current schema.

They are documentation-level diagrams, not a full foreign-key specification.

Consultation and Medical Flow

erDiagram
    CUSTOMERS ||--o{ CUSTOMER_CONSULTATIONS : books
    CONSULTANTS ||--o{ CUSTOMER_CONSULTATIONS : handles
    CONSULTANTS ||--o{ CONSULTANTS_SCHEDULE : owns
    CONSULTATIONS_CATEGORIES ||--o{ CUSTOMER_CONSULTATIONS : classifies
    BRANCHES ||--o{ CUSTOMER_CONSULTATIONS : hosts
    CONSULTANTS_SCHEDULE ||--o{ CUSTOMER_CONSULTATIONS : schedules
    CUSTOMER_CONSULTATIONS ||--o{ CONSULTATION_CANCEL_REQUESTS : may_create
    CUSTOMER_CONSULTATIONS ||--o{ CONSULTATIONS_REPORTS : may_generate
    CUSTOMER_CONSULTATIONS ||--o{ CONSULTATIONS_PRESCRIPTIONS : may_generate
    CONSULTATIONS_DIAGNOSIS ||--o{ CONSULTATIONS_PRESCRIPTIONS : references
    CONSULTATIONS_PRESCRIPTIONS ||--o{ CONSULTATIONS_PRESCRIPTION_MEDICINES : contains
    CONSULTATIONS_MEDICIN ||--o{ CONSULTATIONS_PRESCRIPTION_MEDICINES : selected

Appointment Inquiry and Booking Flow

erDiagram
    BRANCHES ||--o{ APPOINTMENTS : receives
    CONSULTANTS ||--o{ APPOINTMENTS : may_assign
    SERVICES ||--o{ APPOINTMENTS : may_reference
    CUSTOMERS ||--o{ APPOINTMENTS : may_belong_to
    APPOINTMENTS ||--o{ APPOINTMENT_STATUS_LOG : tracks
    APPOINTMENTS ||--o{ APPOINTMENT_ACTIVITIES : records

Courses, Cart, and Subscription Flow

erDiagram
    COURSES_CATEGORIES ||--o{ COURSES : groups
    TRAINERS ||--o{ COURSES : teaches
    COURSES ||--o{ COURSES_CLASSES : contains
    COURSES_CLASSES ||--o{ COURSES_LESSONS : contains
    COURSES_LESSONS ||--o{ COURSES_LESSONS_FILES : attaches
    COLLECTIONS ||--o{ COLLECTION_COURSES : maps
    COURSES ||--o{ COLLECTION_COURSES : maps
    CUSTOMERS ||--o{ CUSTOMER_SUBSCRIPTIONS : owns
    CUSTOMERS ||--o{ CUSTOMER_SUBSCRIPTION_HISTORY : pays_for
    CUSTOMERS ||--o{ CUSTOMER_ORDERS : purchases
    COURSES ||--o{ CUSTOMER_COURSE_CERTIFICATES : awards
    CUSTOMERS ||--o{ CUSTOMER_COURSE_CERTIFICATES : receives

Content and Engagement Flow

erDiagram
    ARTICLES_CATEGORIES ||--o{ ARTICLES : groups
    ARTICLES ||--o{ ARTICLES_PAGES_VIEWS : tracks
    NEWS_CATEGORIES ||--o{ NEWS : groups
    NEWS ||--o{ NEWS_COMMENTS : receives
    NEWS ||--o{ NEWS_PAGES_VIEWS : tracks
    FORUM_CATEGORIES ||--o{ FORUM : groups
    FORUM ||--o{ FORUM_COMMENTS : receives
    FORUM ||--o{ FORUM_LIKES : receives
    LIBRARY_CATEGORIES ||--o{ LIBRARY : groups
    LIBRARY ||--o{ LIBRARY_PAGES_VIEWS : tracks

1. Website and CMS Content

The public website content is stored in classic content tables, including:

• articles
• articles_categories
• news
• news_categories
• forum
• forum_categories
• library
• library_categories
• custome_pages
• custome_pages_categories
• website_slider
• website_slider_categories
• website_settings
• website_features
• website_contacts
• aboutus

These tables generally use bilingual fields such as:

• Title_en and Title_ar
• Content_en and Content_ar
• Name_en and Name_ar

This confirms a multilingual content model with Arabic and English stored directly in the same rows.

Content-related support tables

The schema also tracks engagement and moderation through tables such as:

• articles_pages_views
• news_pages_views
• library_pages_views
• news_comments
• forum_comments
• forum_likes

This shows that the database supports both content publishing and audience interaction.

2. Branches, Services, and Public Discovery

The public healthcare and service discovery layer is represented through tables such as:

• branches
• branches_specialists
• services
• services_categories
• sections
• teams-like public information represented through consultant and management-related entities

The branches table stores location-facing information such as:

• multilingual name and address
• weekly schedule and time
• phone
• map coordinates
• ordering and status

This indicates that the branch model is used directly for public website presentation, not only internal operations.

3. Customers and Identity

Customer identity is centered around the customers table.

Important fields in customers include:

• name and email
• password
• phone and verification flags
• role-related flags such as trainer and consultant status
• guest state
• app token and app default mode

Related customer-supporting tables include:

• customers_docs
• customer_device_tokens
• customer_feedback
• customer_tickets
• customer_ticket_comments
• customer_change_acc_requests
• customer_wallet

Later migrations also add fields and related entities for NFQT integration and extra identity details, including:

• customer_nfqt_info
• nfqt_nationalities
• nfqt_regions
• qualification_levels
• national ID additions on customers

This means the customer domain is broader than simple website login and includes compliance, verification, support, and integration data.

4. Appointments and Public Requests

The appointment entry flow is represented by the appointments table.

Core appointment fields include:

• customer identity or guest-submitted contact details
• specialist, consultant, and branch references
• booking type
• notes
• status
• created timestamp

Later migrations extend this table with:

• source to distinguish website vs app
• richer appointment statuses such as booked, call_later, inquire_only, and booking_in_progress
• appointment_status_log
• appointment_activities

This shows the appointment system evolved from simple inquiry capture into a richer operational workflow with auditability.

5. Consultation Domain

The consultation domain is one of the largest areas in the schema.

The main structural tables include:

• consultants
• consultants_branches
• consultants_categories
• consultants_schedule
• consultant_contract
• consultant_transactions
• consultant_status_logs
• consultations
• consultations_categories
• consultations_schedule
• consultations_time
• consultation_department

The transaction and user-facing execution side includes:

• customer_consultations
• immediate_customer_consultations
• consultation_cancel_requests
• consultation_cancel_requests_audit
• consultation_action_audit
• consultations_reports
• consultation_report_requests_audit
• consultation_absence_reports
• consultation_absence_status_audit

The medical and diagnostic side includes:

• consultations_diagnosis
• consultations_prescriptions
• consultations_prescription_medicines
• consultations_medicin
• consultations_medicins_units
• consultations_units

The psychological assessment side includes:

• consultations_psychological_measures
• consultations_psychological_measures_questions
• consultations_psychological_measures_options
• consultations_psychological_measures_answer
• consultations_psychological_questions_evaluation

The intake and supporting data side includes:

• customer_case_study_questions
• customer_case_study_answers

Consultation evolution from migrations

The migrations show active enhancement of this domain:

• payment transaction IDs added to consultation records
• payment source added as app or web
• reviewer fields added to customer_consultations
• refund workflow and split refund support added to cancellation requests
• auto-cancel absence reporting introduced
• audit tables added for cancellation, action, and medical report workflows
• performance indexes added for high-traffic consultation activity tables

This is a mature, business-critical domain with both operational and clinical data responsibilities.

6. Courses, Collections, and Subscriptions

The e-learning and paid content area is also a major schema domain.

Core catalog tables include:

• courses
• courses_categories
• courses_classes
• courses_lessons
• courses_lessons_files
• collections
• collection_courses
• trainers
• certificates
• certificate_images

Customer learning and subscription tables include:

• customer_subscriptions
• customer_subscription_history
• customer_orders
• cart
• customer_course_certificates
• course_attendance
• watch_video

Learning interaction tables include:

• courses_comments
• courses_homeworks
• courses_homework_answers
• courses_pages_views

The migration history shows performance optimization around course subscription history and cart-related access patterns, which suggests these tables are used in production-heavy flows.

7. Careers and Applications

The recruitment workflow appears as its own domain with tables such as:

• careers
• career_applications
• career_applicants_status_logs
• careers_requests

The status enums were expanded over time to support recruitment pipeline stages such as:

• new
• in_progress
• scheduled
• candidate
• no_show_once
• joined_training

This indicates the website handles real candidate workflow tracking rather than only collecting static CV submissions.

8. Payments, Promotions, and Commerce

Commerce-related tables are spread across multiple domains and include:

• payment_logs
• payment_response_logs
• payment_response_log_refernece
• payment_settings
• invoices
• promocodes
• promocodes_consultations
• promocodes_usage
• promo_codes
• promo_code_usage

The migrations show:

• consultation coupon support
• minimum purchase and maximum discount fields for promocodes
• payment entity naming improvements
• payment source tracking for both consultations and subscriptions

This indicates a shared payment subsystem used by courses, consultations, and potentially other monetized areas.

9. Notifications and Messaging

Notification-related tables include:

• notifications
• notification_public_sent
• notification_templates
• notification_types
• notify_consultant_availability

Later migrations add new notification types and templates for:

• appointments
• medical report approval and rejection
• other operational workflow alerts

This suggests notifications are template-driven and tied closely to product events.

10. Admin and RBAC

The admin authorization model uses RBAC tables:

• rbac_actions
• rbac_menus
• rbac_menu_actions
• rbac_roles_permissions
• rbac_user_roles
• users
• users_roles

Migration inserts also show that new reports and workflow screens are introduced through RBAC menu additions instead of being hardcoded only in the UI.

This means menu visibility and admin access are partially data-driven.

Relationship Patterns

Across the schema, the most common relationship pattern is explicit integer foreign-key-style references by naming convention, for example:

• Customer_ID
• Consultant_ID
• Category_ID
• Branch_ID
• Course_ID
• Consultation_ID

The DDL dump appears to rely heavily on application-managed relationships rather than a strongly normalized, foreign-key-enforced relational model throughout the entire database.

In practice, the application logic likely enforces much of the referential behavior.

Important Recent Schema Changes

Recent migrations show the product is actively evolving in these areas:

• appointment workflow states and status logs
• consultation cancellation and refund handling
• medical report workflows and auditability
• notification templates and notification types
• NFQT-related customer and user data
• performance indexes on consultation and subscription tables
• content metadata such as Schema and Tags for articles and news

This indicates the schema is still growing and is not static.

Seed and Reference Data Observations

I did not find a dedicated seeder framework or seeder directory in the repository.

Instead, reference and setup data appears to be created through SQL migrations, especially for:

• configuration_settings
• rbac_menus
• rbac_actions
• rbac_menu_actions
• rbac_roles_permissions
• notification_types
• notification_templates
• qualification_levels
• nfqt_nationalities
• nfqt_regions

This means production and staging bootstrap data likely depends on running SQL migration files in the correct order.

Documentation-Level Interpretation

At documentation level, the current database can be described as:

• one large operational relational database
• shared by website, customer, consultation, education, admin, and API flows
• bilingual at the content layer
• workflow-heavy in consultations and appointments
• increasingly audit-oriented in newer business-critical domains

Key Risk to Keep in Mind

Because the schema is broad and application-managed, changes in one area can have side effects across multiple modules. The consultation, customer, payment, and notification domains appear especially interconnected and should be treated carefully in future schema changes.