Multi-Tenant SaaS for Online Q&A: Database Schema and UML Diagrams

What We Are Building

We are building an online question-and-answer (quiz) service. Many universities buy the same service. Each one brings its own admins, teachers, and students. University A must never see University B's data. We will design the tables and the UML diagrams that make this safe. The engine is MySQL / MariaDB.

Three Ways to Do Multi-Tenancy

Before the tables, pick a tenancy model. There are three common ones. We compare cost, isolation, and effort, then choose.

Diagram — How Many Universities Share One System

Three universities send requests to one platform. The platform stores every row in shared tables, each stamped with its tenant_id. Colour shows which tenant a row belongs to.

Tenancy and Billing Tables

Start with the tables that define a university and its plan. plans is a global table — it has no tenant_id because plans are the same for everyone. tenants and subscriptions are per-university.

-- ============ PLANS (global lookup) ============
CREATE TABLE plans (
    id             INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    name           VARCHAR(60)   NOT NULL,      -- Free, Standard, Campus
    max_teachers   INT UNSIGNED   NOT NULL,
    max_students   INT UNSIGNED   NOT NULL,
    price_monthly  DECIMAL(10,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_plan_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ TENANTS (the universities) ============
CREATE TABLE tenants (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(150)    NOT NULL,        -- "Delhi Tech University"
    subdomain   VARCHAR(63)     NOT NULL,        -- dtu.qaservice.com
    plan_id     INT UNSIGNED    NULL,            -- FK -> plans (current plan)
    status      ENUM('trial','active','suspended') NOT NULL DEFAULT 'trial',
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_tenant_subdomain (subdomain),
    CONSTRAINT fk_tenant_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ SUBSCRIPTIONS (billing history per tenant) ============
CREATE TABLE subscriptions (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id   BIGINT UNSIGNED NOT NULL,        -- FK -> tenants
    plan_id     INT UNSIGNED    NOT NULL,        -- FK -> plans
    starts_on   DATE            NOT NULL,
    ends_on     DATE            NULL,
    status      ENUM('active','past_due','cancelled') NOT NULL DEFAULT 'active',
    PRIMARY KEY (id),
    KEY ix_sub_tenant (tenant_id),
    CONSTRAINT fk_sub_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_sub_plan   FOREIGN KEY (plan_id)   REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Users and Roles — Admin, Teacher, Student

Every user belongs to one tenant. A user can hold one or more roles. We keep roles in a small global lookup and link them with a join table. This lets a person be, say, both a teacher and an admin in the same university.

-- ============ USERS (per tenant) ============
CREATE TABLE users (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id      BIGINT UNSIGNED NOT NULL,       -- FK -> tenants
    name           VARCHAR(120)    NOT NULL,
    email          VARCHAR(190)    NOT NULL,
    password_hash  VARCHAR(255)    NOT NULL,
    status         ENUM('active','invited','disabled') NOT NULL DEFAULT 'active',
    created_at     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    -- same email may exist in two different universities, so scope it by tenant
    UNIQUE KEY uq_user_email (tenant_id, email),
    KEY ix_users_tenant (tenant_id),
    CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ ROLES (global lookup) ============
CREATE TABLE roles (
    id        TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    role_key  VARCHAR(20)      NOT NULL,   -- 'admin','teacher','student'
    label     VARCHAR(40)      NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_role_key (role_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ USER_ROLES (who has which role) ============
CREATE TABLE user_roles (
    user_id    BIGINT UNSIGNED  NOT NULL,      -- FK -> users
    role_id    TINYINT UNSIGNED NOT NULL,      -- FK -> roles
    tenant_id  BIGINT UNSIGNED  NOT NULL,      -- copied in for fast tenant checks
    PRIMARY KEY (user_id, role_id),
    KEY ix_ur_tenant (tenant_id),
    CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

UML Use-Case Diagram — Who Does What

The three actors and their actions inside one tenant. The dashed box is the system boundary: everything inside runs per university.

Academic Structure — Courses and Enrollments

A teacher creates courses. Students enrol in them. Both tables carry tenant_id, and the course code is unique only inside one university.

-- ============ COURSES ============
CREATE TABLE courses (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id   BIGINT UNSIGNED NOT NULL,       -- FK -> tenants
    name        VARCHAR(150)    NOT NULL,
    code        VARCHAR(30)     NOT NULL,       -- "CS101"
    created_by  BIGINT UNSIGNED NOT NULL,       -- FK -> users (a teacher)
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_course_code (tenant_id, code),
    KEY ix_courses_tenant (tenant_id),
    CONSTRAINT fk_courses_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_courses_teacher FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ ENROLLMENTS (student in course) ============
CREATE TABLE enrollments (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id    BIGINT UNSIGNED NOT NULL,      -- FK -> tenants
    course_id    BIGINT UNSIGNED NOT NULL,      -- FK -> courses
    student_id   BIGINT UNSIGNED NOT NULL,      -- FK -> users (a student)
    enrolled_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_enroll (course_id, student_id),  -- no double enrolment
    KEY ix_enroll_tenant (tenant_id),
    CONSTRAINT fk_enroll_course  FOREIGN KEY (course_id)  REFERENCES courses(id) ON DELETE CASCADE,
    CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The Q&A Content — Quizzes, Questions, Options

A quiz belongs to a course. It holds many questions. A choice question holds many options, and each option is marked right or wrong with is_correct. This is how the service knows how to grade.

-- ============ QUIZZES ============
CREATE TABLE quizzes (
    id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        BIGINT UNSIGNED NOT NULL,      -- FK -> tenants
    course_id        BIGINT UNSIGNED NOT NULL,      -- FK -> courses
    title            VARCHAR(200)    NOT NULL,
    created_by       BIGINT UNSIGNED NOT NULL,      -- FK -> users (a teacher)
    time_limit_mins  INT UNSIGNED    NULL,
    is_published     TINYINT(1)      NOT NULL DEFAULT 0,
    created_at       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY ix_quiz_tenant (tenant_id, course_id),
    CONSTRAINT fk_quiz_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_quiz_course  FOREIGN KEY (course_id)  REFERENCES courses(id) ON DELETE CASCADE,
    CONSTRAINT fk_quiz_teacher FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ QUESTIONS ============
CREATE TABLE questions (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id   BIGINT UNSIGNED NOT NULL,       -- FK -> tenants
    quiz_id     BIGINT UNSIGNED NOT NULL,       -- FK -> quizzes
    body        TEXT            NOT NULL,
    qtype       ENUM('mcq_single','mcq_multi','true_false','short_text') NOT NULL,
    marks       DECIMAL(6,2)   NOT NULL DEFAULT 1,
    position    INT UNSIGNED    NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY ix_q_tenant (tenant_id),
    KEY ix_q_quiz (quiz_id, position),
    CONSTRAINT fk_q_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_q_quiz   FOREIGN KEY (quiz_id)   REFERENCES quizzes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ QUESTION_OPTIONS (choices; correct flag) ============
CREATE TABLE question_options (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id    BIGINT UNSIGNED NOT NULL,      -- FK -> tenants
    question_id  BIGINT UNSIGNED NOT NULL,      -- FK -> questions
    label        VARCHAR(500)    NOT NULL,
    is_correct   TINYINT(1)      NOT NULL DEFAULT 0,
    position     INT UNSIGNED    NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY ix_opt_question (question_id),
    CONSTRAINT fk_opt_tenant   FOREIGN KEY (tenant_id)   REFERENCES tenants(id)   ON DELETE CASCADE,
    CONSTRAINT fk_opt_question FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Attempts and Results

When a student starts a quiz, we create one attempt. Each answer they give is one row in attempt_answers. Choice answers point to the picked option; short answers store text. After grading, we save the score on the attempt.

-- ============ ATTEMPTS (one student sitting one quiz) ============
CREATE TABLE attempts (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id     BIGINT UNSIGNED NOT NULL,     -- FK -> tenants
    quiz_id       BIGINT UNSIGNED NOT NULL,     -- FK -> quizzes
    student_id    BIGINT UNSIGNED NOT NULL,     -- FK -> users
    started_at    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    submitted_at  TIMESTAMP       NULL,
    score         DECIMAL(8,2)   NULL,
    status        ENUM('in_progress','submitted','graded') NOT NULL DEFAULT 'in_progress',
    PRIMARY KEY (id),
    KEY ix_att_tenant (tenant_id),
    KEY ix_att_quiz (quiz_id, student_id),
    CONSTRAINT fk_att_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_att_quiz    FOREIGN KEY (quiz_id)    REFERENCES quizzes(id) ON DELETE CASCADE,
    CONSTRAINT fk_att_student FOREIGN KEY (student_id) REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============ ATTEMPT_ANSWERS (one answer per question) ============
CREATE TABLE attempt_answers (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id           BIGINT UNSIGNED NOT NULL,   -- FK -> tenants
    attempt_id          BIGINT UNSIGNED NOT NULL,   -- FK -> attempts
    question_id         BIGINT UNSIGNED NOT NULL,   -- FK -> questions
    selected_option_id  BIGINT UNSIGNED NULL,       -- FK -> question_options (choice)
    answer_text         TEXT            NULL,       -- for short_text
    awarded_marks       DECIMAL(6,2)   NULL,       -- filled at grading
    PRIMARY KEY (id),
    UNIQUE KEY uq_att_ans (attempt_id, question_id, selected_option_id),
    KEY ix_aa_tenant (tenant_id),
    CONSTRAINT fk_aa_tenant   FOREIGN KEY (tenant_id)          REFERENCES tenants(id)          ON DELETE CASCADE,
    CONSTRAINT fk_aa_attempt  FOREIGN KEY (attempt_id)         REFERENCES attempts(id)         ON DELETE CASCADE,
    CONSTRAINT fk_aa_question FOREIGN KEY (question_id)        REFERENCES questions(id)        ON DELETE CASCADE,
    CONSTRAINT fk_aa_option   FOREIGN KEY (selected_option_id) REFERENCES question_options(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

UML Class / ER Diagrams — The Full Schema

Part 1: tenancy and identity. Every arrow reads one-to-many (1 → N) from parent to child. plans and roles are global; the rest carry tenant_id.

Part 2: the academic and Q&A tables. Dashed boxes are tables defined earlier that these point back to.

How Every Request Is Scoped to One Tenant

Isolation is not magic. It is a habit applied on every request. Here is the flow from the moment a user opens the site to the moment a query runs.

Example Queries

List published quizzes for a student's courses (tenant-scoped)

SELECT qz.id, qz.title, c.name AS course
FROM   quizzes qz
JOIN   courses c      ON c.id = qz.course_id
JOIN   enrollments e  ON e.course_id = c.id
WHERE  qz.tenant_id = 1            -- current tenant, always first
  AND  e.student_id = 57
  AND  qz.is_published = 1
ORDER BY qz.created_at DESC;

Auto-grade a submitted attempt

-- award marks: correct option -> question marks, else 0
UPDATE attempt_answers aa
JOIN   questions q         ON q.id = aa.question_id
LEFT JOIN question_options o ON o.id = aa.selected_option_id
SET    aa.awarded_marks = IF(o.is_correct = 1, q.marks, 0)
WHERE  aa.tenant_id  = 1
  AND  aa.attempt_id = 9001;

-- roll the total up onto the attempt
UPDATE attempts a
SET    a.score  = (SELECT COALESCE(SUM(awarded_marks), 0)
                   FROM attempt_answers
                   WHERE attempt_id = 9001 AND tenant_id = 1),
       a.status = 'graded'
WHERE  a.id = 9001 AND a.tenant_id = 1;

Admin dashboard — average score per quiz

SELECT qz.title, ROUND(AVG(a.score), 1) AS avg_score, COUNT(*) AS attempts
FROM   attempts a
JOIN   quizzes qz ON qz.id = a.quiz_id
WHERE  a.tenant_id = 1
  AND  a.status = 'graded'
GROUP BY qz.id, qz.title
ORDER BY avg_score DESC;

Global vs Tenant-Scoped Tables

Not every table needs a tenant_id. Lookups that are the same for everyone stay global. Everything that belongs to a university is scoped. Keep this split clear.

Golden Rules