Relationship Map

A consolidated view of how the tables connect. Arrows show logical foreign-key dependencies (declared as BIGINT without a CONSTRAINT, validated by the ETL).

Macro view

Four functional families, all rooted in tenants:

Tenants & People

Catalog

Tracks

Activity

Table roles

Category	Tables
Master dimensions	tenants, users, admins, products, lessons, modules, showcases, tracks, exams, tags
Junctions (M:N)	user_tags, track_products, track_tags
Events / activity	user_logs, admin_logs, user_lesson_progress, scores, lesson_ratings, exam_results
Consolidated state	user_certificates, user_tracks, user_product_progress, messages
Exam content	exam_questions

Main cardinalities

• 1 tenant → N users, N admins, N products, N tracks
• 1 product → N modules → N lessons
• 1 lesson → 0..1 exam (via id_exam)
• 1 exam → N exam_questions, N exam_results
• 1 user + 1 product → 0..1 user_product_progress (derived, composite PK)
• 1 user + 1 product → N user_certificates (first issue + reissues)
• 1 track ⟷ N products (via track_products)
• 1 track ⟷ N users (via user_tracks, with history)

Join pattern

Because id_tenant appears in almost every table, always include it in joins to preserve the logical partition predicate:

SELECT
    u.name,
    p.title,
    upp.progress
FROM lms.users u
JOIN lms.user_product_progress upp
    ON upp.id_tenant = u.id_tenant
   AND upp.id_user   = u.id
JOIN lms.products p
    ON p.id_tenant = upp.id_tenant
   AND p.id        = upp.id_product
WHERE u.deleted_at IS NULL
  AND p.deleted_at IS NULL;