Low-Level Design: Polling System - Using Nodejs

CREATE DATABASE polling_system; USE polling_system; CREATE TABLE polls ( poll_id INT AUTO_INCREMENT PRIMARY KEY, question VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 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 );

mkdir polling-system cd polling-system npm init -y npm install express mysql2 dotenv

polling-system/ │ ├── .env ├── index.js ├── db/ │ └── db.js ├── routes/ │ └── pollRoutes.js └── controllers/ └── pollController.js

const mysql = require('mysql2/promise'); require('dotenv').config(); const pool = mysql.createPool({ host: process.env.DB_HOST, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, waitForConnections: true, connectionLimit: 10, queueLimit: 0 }); module.exports = pool;

DB_HOST=localhost DB_USER=root DB_PASSWORD=yourpassword DB_NAME=polling_system PORT=3000

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 (question) VALUES (?)', [question] ); const pollId = result.insertId; 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 || !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 [pollResult] = await connection.execute( 'UPDATE polls SET question = ? WHERE poll_id = ?', [question, pollId] ); if (pollResult.affectedRows === 0) { await connection.rollback(); connection.release(); return res.status(404).json({ message: "Poll not found." }); } 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); 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." }); } };

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;

const express = require('express'); const pollRoutes = require('./routes/pollRoutes'); require('dotenv').config(); const app = express(); app.use(express.json()); // Routes app.use('/api', pollRoutes); // Error Handling Middleware app.use((err, req, res, next) => { console.error(err.stack); res.status(500).json({ message: "Internal server error" }); }); // Start Server const PORT = process.env.PORT || 3000; app.listen(PORT, () => { console.log(`Server is running on port ${PORT}`); });