Table of contents
Case 1 - Handle Versioning for Update
Case 2 - PollID to be as UUID & not Primary Key
Case 3 - Empty or Invalid Options
Case 4 - Duplicate Options
Case 5 - Question Length Limit
Case 6 - Poll Expiration
Please refer to the following articles first:
Low-Level Design: Polling System: Basic
Low-Level Design: Polling System - Using Node.js & SQL
To manage updates to a poll's question and options while retaining the previous details associated with the same poll ID, you can implement a versioning system. This approach allows you to keep track of the historical data for each poll, ensuring that old details are preserved even after updates.
Update the Polls Table
Create the Poll Versions Table
CREATE DATABASE polling_system; USE polling_system; CREATE TABLE polls ( poll_id INT AUTO_INCREMENT PRIMARY KEY, current_version_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (current_version_id) REFERENCES poll_versions(version_id) ON DELETE SET NULL ); CREATE TABLE poll_versions ( version_id INT AUTO_INCREMENT PRIMARY KEY, poll_id INT, question VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE ); CREATE TABLE options ( option_id INT AUTO_INCREMENT PRIMARY KEY, poll_id INT, option_text VARCHAR(255) NOT NULL, FOREIGN KEY (poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE ); CREATE TABLE votes ( vote_id INT AUTO_INCREMENT PRIMARY KEY, poll_id INT, user_id VARCHAR(255) NOT NULL, option_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE, FOREIGN KEY (option_id) REFERENCES options(option_id) ON DELETE CASCADE );
Modify the updatePoll method to check if the question has changed before creating a new version.
const pool = require('../db/db'); // Create Poll exports.createPoll = async (req, res) => { const { question, options } = req.body; if (!question || !options || !Array.isArray(options) || options.length < 2) { return res.status(400).json({ message: "Invalid input data. Question and at least two options are required." }); } try { const connection = await pool.getConnection(); await connection.beginTransaction(); const [result] = await connection.execute( 'INSERT INTO polls (current_version_id) VALUES (NULL)' ); const pollId = result.insertId; const [versionResult] = await connection.execute( 'INSERT INTO poll_versions (poll_id, question) VALUES (?, ?)', [pollId, question] ); const versionId = versionResult.insertId; // Update the current version in the polls table await connection.execute( 'UPDATE polls SET current_version_id = ? WHERE poll_id = ?', [versionId, pollId] ); const optionQueries = options.map(option => { return connection.execute( 'INSERT INTO options (poll_id, option_text) VALUES (?, ?)', [pollId, option] ); }); await Promise.all(optionQueries); await connection.commit(); connection.release(); res.status(201).json({ pollId, message: "Poll created successfully." }); } catch (error) { console.error("Error creating poll:", error.message); res.status(500).json({ message: "Error creating poll." }); } }; // Update Poll exports.updatePoll = async (req, res) => { const { pollId } = req.params; const { question, options } = req.body; if (!pollId || !options || !Array.isArray(options) || options.length < 2) { return res.status(400).json({ message: "Invalid input data. At least two options are required." }); } try { const connection = await pool.getConnection(); await connection.beginTransaction(); // Fetch the existing poll const [existingPoll] = await connection.execute( 'SELECT question FROM poll_versions WHERE poll_id = (SELECT current_version_id FROM polls WHERE poll_id = ?)', [pollId] ); if (existingPoll.length === 0) { await connection.rollback(); connection.release(); return res.status(404).json({ message: "Poll not found." }); } const currentQuestion = existingPoll[0].question; // Check if the question has changed if (currentQuestion !== question) { // Create a new version since the question has changed const [versionResult] = await connection.execute( 'INSERT INTO poll_versions (poll_id, question) VALUES (?, ?)', [pollId, question] ); const versionId = versionResult.insertId; // Update the current version in the polls table await connection.execute( 'UPDATE polls SET current_version_id = ? WHERE poll_id = ?', [versionId, pollId] ); } // Remove old options and insert new ones await connection.execute('DELETE FROM options WHERE poll_id = ?', [pollId]); const optionQueries = options.map(option => { return connection.execute( 'INSERT INTO options (poll_id, option_text) VALUES (?, ?)', [pollId, option] ); }); await Promise.all(optionQueries); await connection.commit(); connection.release(); res.status(200).json({ message: "Poll updated successfully." }); } catch (error) { console.error("Error updating poll:", error.message); await connection.rollback(); res.status(500).json({ message: "Error updating poll." }); } }; // Delete Poll exports.deletePoll = async (req, res) => { const { pollId } = req.params; try { const connection = await pool.getConnection(); const [result] = await connection.execute( 'DELETE FROM polls WHERE poll_id = ?', [pollId] ); connection.release(); if (result.affectedRows === 0) { return res.status(404).json({ message: "Poll not found." }); } res.status(200).json({ message: "Poll deleted successfully." }); } catch (error) { console.error("Error deleting poll:", error.message); res.status(500).json({ message: "Error deleting poll." }); } }; // Vote in Poll exports.voteInPoll = async (req, res) => { const { pollId } = req.params; const { userId, option } = req.body; if (!userId || !option) { return res.status(400).json({ message: "User ID and option are required." }); } try { const connection = await pool.getConnection(); const [userVote] = await connection.execute( 'SELECT * FROM votes WHERE poll_id = ? AND user_id = ?', [pollId, userId] ); if (userVote.length > 0) { connection.release(); return res.status(400).json({ message: "User has already voted." }); } const [optionResult] = await connection.execute( 'SELECT option_id FROM options WHERE poll_id = ? AND option_text = ?', [pollId, option] ); if (optionResult.length === 0) { connection.release(); return res.status(404).json({ message: "Option not found." }); } const optionId = optionResult[0].option_id; await connection.execute( 'INSERT INTO votes (poll_id, user_id, option_id) VALUES (?, ?, ?)', [pollId, userId, optionId] ); connection.release(); res.status(200).json({ message: "Vote cast successfully." }); } catch (error) { console.error("Error casting vote:", error.message); res.status(500).json({ message: "Error casting vote." }); } }; // View Poll Results exports.viewPollResults = async (req, res) => { const { pollId } = req.params; try { const connection = await pool.getConnection(); const [poll] = await connection.execute( 'SELECT * FROM polls WHERE poll_id = ?', [pollId] ); if (poll.length === 0) { connection.release(); return res.status(404).json({ message: "Poll not found." }); } const [options] = await connection.execute( 'SELECT option_text, COUNT(votes.option_id) as vote_count FROM options ' + 'LEFT JOIN votes ON options.option_id = votes.option_id ' + 'WHERE options.poll_id = ? GROUP BY options.option_id', [pollId] ); connection.release(); res.status(200).json({ pollId: poll[0].poll_id, question: poll[0].question, results: options.reduce((acc, option) => { acc[option.option_text] = option.vote_count; return acc; }, {}) }); } catch (error) { console.error("Error viewing poll results:", error.message); res.status(500).json({ message: "Error viewing poll results." }); } };
Ensure the routes are defined properly in your pollRoutes.js.
const express = require('express'); const router = express.Router(); const pollController = require('../controllers/pollController'); // Routes router.post('/polls', pollController.createPoll); router.put('/polls/:pollId', pollController.updatePoll); router.delete('/polls/:pollId', pollController.deletePoll); router.post('/polls/:pollId/vote', pollController.voteInPoll); router.get('/polls/:pollId/results', pollController.viewPollResults); module.exports = router;
Database:
API:
Routing:
To handle a scenario where the pollId is required to be a UUID (Universally Unique Identifier).
Here are the steps to implement UUIDs for thepollId in your polling system without providing code:
** Database Schema Update:**
** UUID Generation:**
** Create Poll Logic:**
** Update Poll Logic:**
Check if the question has changed.
** Voting Logic:**
Validate that the UUID provided in the vote request exists in thepolls table.
** API Updates:**
** Testing:**
** Documentation:**
By following these steps, you can successfully implement UUIDs for pollId in your polling system while ensuring data integrity and historical tracking.
Empty or Invalid Options
Validation Approach:
Duplicate Options
Uniqueness Check:
Question Length Limit
Character Limitation:
Poll Expiration
Expiration Mechanism:
Please refer to the following articles first:
Low-Level Design: Polling System: Basic
Low-Level Design: Polling System - Using Node.js & SQL
More Details:
Get all articles related to system design
Hastag: SystemDesignWithZeeshanAli
systemdesignwithzeeshanali
Git: https://github.com/ZeeshanAli-0704/SystemDesignWithZeeshanAli
The above is the detailed content of Low-Level Design: Polling System - Edge Cases. For more information, please follow other related articles on the PHP Chinese website!