请参考文章轮询系统基础底层设计-I
让我们将整个过程分解为详细的步骤，包括数据库设置、使用 Node.js 和 Express 实现 API 以及与 MySQL 的交互。我们将涵盖：
首先，我们将定义 MySQL 数据库的架构并创建必要的表。
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
);
投票表：存储带有唯一标识符、问题和创建时间戳的投票信息。
选项表：存储与投票相关的选项，通过 poll_id 链接。
投票表：记录每次投票，链接到民意调查、选项和用户。
实体：
关系：
这是 ERD 的描述：
投票表：
选项表:
投票表：
关系将用实体之间的线表示：
让我们使用 Express 和 MySQL 设置一个 Node.js 项目。
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
文件 - db/db.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;
文件 - .env
DB_HOST=localhost DB_USER=root DB_PASSWORD=yourpassword DB_NAME=polling_system PORT=3000
文件-controllers/pollController.js
该文件将实现轮询系统所有必需的 CRUD 操作。
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." });
}
};
文件-routes/pollRoutes.js
定义每个 API 端点的路由：
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;
文件 - index.js
最后，设置服务器：
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}`);
});
每种方法都包含对常见问题的错误处理，例如无效输入、重复投票、缺少投票或选项以及服务器错误。
使用Postman或curl等工具测试每个端点。
这是一个使用 Node.js、Express 和 MySQL 的在线投票系统的全面模块化实现。它处理基本的CRUD操作并确保数据与事务的一致性。它还包括基本的错误处理，以使 API 更加健壮和用户友好。
请参考文章轮询系统基础底层设计-I
更多详情：
获取所有与系统设计相关的文章
标签：SystemDesignWithZeeshanAli
系统设计与zeeshanali
Git：https://github.com/ZeeshanAli-0704/SystemDesignWithZeeshanAli
以上是底层设计：轮询系统 - 使用 Nodejs的详细内容。更多信息请关注PHP中文网其他相关文章！
