Day 3: Database Schema Design - Building the Foundation of Your Quiz Platform

The Heart of Every Great System: Data Architecture

Imagine you're organizing a massive library. Without a proper cataloging system, finding a specific book becomes impossible when you have millions of volumes. Similarly, in distributed systems handling millions of requests per second, your database schema is the cataloging system that determines whether your application scales gracefully or crumbles under pressure.

Today, we're designing the data foundation for our quiz platform. This isn't just about storing information—we're creating the blueprint that will determine how efficiently our system handles concurrent users, real-time quiz sessions, and complex analytics queries that power platforms like Kahoot or Quizlet.

Why Database Schema Design Matters in Production Systems

In high-scale systems, your schema design directly impacts performance, scalability, and maintainability. A poorly designed schema can create bottlenecks that no amount of hardware can solve. Companies like Netflix and Uber have learned this lesson through expensive rewrites—getting it right from the start saves millions in engineering costs and system downtime.

Consider this: when thousands of students simultaneously submit quiz answers, your database needs to handle these writes while still serving read requests for leaderboards and analytics. Your schema design determines whether this scenario results in smooth operation or system failure.

Understanding Our Quiz Platform's Data Relationships

Think of our quiz platform like a school ecosystem. We have students (users), teachers creating tests (quizzes), individual questions, and student submissions (attempts). Each entity has relationships with others, just like in real life—a student can take multiple quizzes, a quiz contains multiple questions, and each attempt links a specific user to a specific quiz.

These relationships form the backbone of our system. In MongoDB, we'll model these relationships using embedded documents and references, choosing the approach that best serves our query patterns and performance requirements.

Implementing Your MongoDB Schema

Let's build our schema step by step, starting with the core entities and theirelationships.

Source code repo : https://github.com/sysdr/aie/tree/main/day3

Project Structure Setup

First, create your project structure:

mkdir quiz-platform-db
cd quiz-platform-db
mkdir src models config
touch src/app.js models/index.js config/database.js package.json Dockerfile docker-compose.yml

Core Schema Implementation

Our MongoDB schemas will use Mongoose for validation and structure. Here's how we'll implement each entity:

User Schema (models/User.js):

const mongoose = require('mongoose');
const bcrypt = require('bcryptjs');

const userSchema = new mongoose.Schema({
  username: {
    type: String,
    required: true,
    unique: true,
    trim: true,
    minlength: 3,
    maxlength: 30
  },
  email: {
    type: String,
    required: true,
    unique: true,
    lowercase: true,
    match: /^[^\s@]+@[^\s@]+\.[^\s@]+$/
  },
  password: {
    type: String,
    required: true,
    minlength: 6
  },
  role: {
    type: String,
    enum: ['student', 'teacher', 'admin'],
    default: 'student'
  },
  profile: {
    firstName: String,
    lastName: String,
    avatar: String,
    dateOfBirth: Date
  },
  stats: {
    totalQuizzesTaken: { type: Number, default: 0 },
    averageScore: { type: Number, default: 0 },
    totalPoints: { type: Number, default: 0 }
  }
}, {
  timestamps: true
});

// Hash password before saving
userSchema.pre('save', async function(next) {
  if (!this.isModified('password')) return next();
  this.password = await bcrypt.hash(this.password, 10);
  next();
});

module.exports = mongoose.model('User', userSchema);

Quiz Schema (models/Quiz.js):

const mongoose = require('mongoose');

const quizSchema = new mongoose.Schema({
  title: {
    type: String,
    required: true,
    trim: true,
    maxlength: 200
  },
  description: {
    type: String,
    maxlength: 1000
  },
  creator: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true
  },
  category: {
    type: String,
    required: true,
    enum: ['math', 'science', 'history', 'literature', 'general']
  },
  difficulty: {
    type: String,
    enum: ['easy', 'medium', 'hard'],
    default: 'medium'
  },
  timeLimit: {
    type: Number, // in minutes
    default: 30,
    min: 1,
    max: 180
  },
  questions: [{
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Question'
  }],
  settings: {
    isPublic: { type: Boolean, default: true },
    allowRetakes: { type: Boolean, default: true },
    showCorrectAnswers: { type: Boolean, default: true },
    randomizeQuestions: { type: Boolean, default: false }
  },
  stats: {
    totalAttempts: { type: Number, default: 0 },
    averageScore: { type: Number, default: 0 },
    completionRate: { type: Number, default: 0 }
  }
}, {
  timestamps: true
});

module.exports = mongoose.model('Quiz', quizSchema);

Question Schema (models/Question.js):

const mongoose = require('mongoose');

const questionSchema = new mongoose.Schema({
  question: {
    type: String,
    required: true,
    trim: true,
    maxlength: 500
  },
  type: {
    type: String,
    enum: ['multiple-choice', 'true-false', 'short-answer'],
    required: true
  },
  options: [{
    text: { type: String, required: true },
    isCorrect: { type: Boolean, default: false }
  }],
  correctAnswer: String, // For short-answer questions
  explanation: String,
  difficulty: {
    type: String,
    enum: ['easy', 'medium', 'hard'],
    default: 'medium'
  },
  points: {
    type: Number,
    default: 1,
    min: 1,
    max: 10
  },
  tags: [String],
  creator: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true
  }
}, {
  timestamps: true
});

// Validation for multiple choice questions
questionSchema.pre('save', function(next) {
  if (this.type === 'multiple-choice') {
    if (this.options.length < 2) {
      return next(new Error('Multiple choice questions must have at least 2 options'));
    }
    const correctAnswers = this.options.filter(opt => opt.isCorrect);
    if (correctAnswers.length === 0) {
      return next(new Error('Multiple choice questions must have at least one correct answer'));
    }
  }
  next();
});

module.exports = mongoose.model('Question', questionSchema);

Attempt Schema (models/Attempt.js):

const mongoose = require('mongoose');

const attemptSchema = new mongoose.Schema({
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true
  },
  quiz: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Quiz',
    required: true
  },
  answers: [{
    question: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Question',
      required: true
    },
    userAnswer: mongoose.Schema.Types.Mixed,
    isCorrect: Boolean,
    pointsEarned: { type: Number, default: 0 },
    timeSpent: Number // in seconds
  }],
  score: {
    totalPoints: { type: Number, default: 0 },
    maxPoints: { type: Number, required: true },
    percentage: { type: Number, default: 0 }
  },
  timing: {
    startedAt: { type: Date, default: Date.now },
    completedAt: Date,
    totalTime: Number // in seconds
  },
  status: {
    type: String,
    enum: ['in-progress', 'completed', 'abandoned'],
    default: 'in-progress'
  }
}, {
  timestamps: true
});

// Calculate final score before saving
attemptSchema.pre('save', function(next) {
  if (this.status === 'completed') {
    this.score.totalPoints = this.answers.reduce((sum, answer) => sum + answer.pointsEarned, 0);
    this.score.percentage = (this.score.totalPoints / this.score.maxPoints) * 100;
    
    if (!this.timing.completedAt) {
      this.timing.completedAt = new Date();
      this.timing.totalTime = Math.floor((this.timing.completedAt - this.timing.startedAt) / 1000);
    }
  }
  next();
});

module.exports = mongoose.model('Attempt', attemptSchema);

Database Configuration and Application Setup

Database Configuration (config/database.js):

const mongoose = require('mongoose');

const connectDatabase = async () => {
  try {
    const conn = await mongoose.connect(process.env.MONGODB_URI || 'mongodb://localhost:27017/quiz-platform', {
      useNewUrlParser: true,
      useUnifiedTopology: true,
    });
    
    console.log(`MongoDB Connected: ${conn.connection.host}`);
  } catch (error) {
    console.error('Database connection error:', error);
    process.exit(1);
  }
};

module.exports = connectDatabase;

Main Application (src/app.js):

const express = require('express');
const mongoose = require('mongoose');
const connectDatabase = require('../config/database');

// Import models
const User = require('../models/User');
const Quiz = require('../models/Quiz');
const Question = require('../models/Question');
const Attempt = require('../models/Attempt');

const app = express();
const PORT = process.env.PORT || 3000;

// Middleware
app.use(express.json());

// Connect to database
connectDatabase();

// Test route to verify schema functionality
app.get('/api/test-schema', async (req, res) => {
  try {
    // Create a test user
    const testUser = new User({
      username: 'testuser',
      email: 'test@example.com',
      password: 'password123',
      profile: {
        firstName: 'Test',
        lastName: 'User'
      }
    });
    
    const savedUser = await testUser.save();
    
    // Create a test question
    const testQuestion = new Question({
      question: 'What is 2 + 2?',
      type: 'multiple-choice',
      options: [
        { text: '3', isCorrect: false },
        { text: '4', isCorrect: true },
        { text: '5', isCorrect: false }
      ],
      creator: savedUser._id
    });
    
    const savedQuestion = await testQuestion.save();
    
    // Create a test quiz
    const testQuiz = new Quiz({
      title: 'Basic Math Quiz',
      description: 'A simple math quiz for testing',
      creator: savedUser._id,
      category: 'math',
      questions: [savedQuestion._id]
    });
    
    const savedQuiz = await testQuiz.save();
    
    res.json({
      message: 'Schema test successful!',
      data: {
        user: savedUser,
        question: savedQuestion,
        quiz: savedQuiz
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Health check endpoint
app.get('/health', (req, res) => {
  res.json({ status: 'OK', timestamp: new Date().toISOString() });
});

app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

Package.json:

{
  "name": "quiz-platform-db",
  "version": "1.0.0",
  "description": "Quiz Platform Database Schema Implementation",
  "main": "src/app.js",
  "scripts": {
    "start": "node src/app.js",
    "dev": "nodemon src/app.js",
    "test": "echo \"No tests yet\" && exit 0"
  },
  "dependencies": {
    "express": "^4.18.2",
    "mongoose": "^7.5.0",
    "bcryptjs": "^2.4.3"
  },
  "devDependencies": {
    "nodemon": "^3.0.1"
  }
}

Docker Configuration

Dockerfile:

FROM node:18-alpine

WORKDIR /app

COPY package*.json ./
RUN npm install

COPY . .

EXPOSE 3000

CMD ["npm", "start"]

docker-compose.yml:

version: '3.8'

services:
  app:
    build: .
    ports:
      - "3000:3000"
    environment:
      - MONGODB_URI=mongodb://mongo:27017/quiz-platform
    depends_on:
      - mongo
    volumes:
      - .:/app
      - /app/node_modules

  mongo:
    image: mongo:7
    ports:
      - "27017:27017"
    volumes:
      - mongo_data:/data/db

volumes:
  mongo_data:

Building and Testing Your Implementation

Local Development Setup

1. Install Dependencies:

npm install

2. Start MongoDB locally:

# If you have MongoDB installed locally
mongod

# Or use Docker
docker run -d -p 27017:27017 --name quiz-mongo mongo:7

3. Run the Application:

npm run dev

4. Test the Schema: Visit http://localhost:3000/api/test-schema to verify your schemas work correctly.

Docker Deployment

1. Build and Run with Docker Compose:

docker-compose up --build

2. Verify the Application:

• Health check: http://localhost:3000/health

• Schema test: http://localhost:3000/api/test-schema

3. View Logs:

docker-compose logs -f app

Understanding the Production Impact

This schema design incorporates several production-ready patterns. The embedded stats in each model enable real-time analytics without complex aggregation queries. The indexing strategy on frequently queried fields like email and username ensures fast lookups even with millions of users. The pre-save middleware handles business logic consistently, preventing data inconsistencies that plague many production systems.

The relationship design balances normalization with performance. We reference users in quizzes and attempts to maintain data integrity while avoiding duplication. Questions are separate entities to enable reuse across multiple quizzes, a pattern that scales beautifully as your content library grows.

Assignment: Build Your Quiz Analytics Dashboard

Your homework is to extend this schema with analytics capabilities. Create an Analytics model that tracks daily quiz completion rates, popular categories, and user engagement metrics. This model should efficiently support dashboard queries without impacting the main quiz-taking experience.

Implement aggregation pipelines that calculate weekly user retention rates and identify trending quiz topics. Your solution should handle the scenario where marketing teams need real-time insights while students are actively taking quizzes.

Test your implementation with sample data representing 1000 users taking various quizzes over a simulated week. Measure query performance and optimize your aggregation pipelines to ensure dashboard loads remain under 200ms.

Solution Implementation

The complete solution includes additional models for analytics, optimized aggregation pipelines, and performance monitoring endpoints. Your implementation should demonstrate understanding of MongoDB's aggregation framework and how to design schemas that serve both transactional and analytical workloads efficiently.

This foundation will support the real-time features we'll build in coming days, including live leaderboards, instant result processing, and concurrent user sessions. Each design decision made today directly impacts your system's ability to scale from hundreds to millions of users.