ブログのコメント機能を実装するために柔軟な MySQL テーブル構造を設計するにはどうすればよいですか?

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    created_at DATETIME
);

CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    article_id INT,
    content TEXT,
    parent_id INT,
    created_at DATETIME,
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (parent_id) REFERENCES comments(id)
);

ALTER TABLE articles ADD COLUMN comment_count INT DEFAULT 0;

<?php
// 获取某篇文章的评论列表
function getCommentsByArticleId($articleId) {
    $sql = "SELECT * FROM comments WHERE article_id = :articleId";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":articleId", $articleId);
    $stmt->execute();

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 添加评论
function addComment($articleId, $content, $parentId = null) {
    $sql = "INSERT INTO comments (article_id, content, parent_id, created_at) VALUES (:articleId, :content, :parentId, NOW())";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":articleId", $articleId);
    $stmt->bindParam(":content", $content);
    $stmt->bindParam(":parentId", $parentId);
    $stmt->execute();

    // 更新文章的评论总数
    $sql = "UPDATE articles SET comment_count = comment_count + 1 WHERE id = :articleId";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":articleId", $articleId);
    $stmt->execute();
}

// 删除评论
function deleteComment($commentId) {
    // 获取评论所属的文章id
    $sql = "SELECT article_id FROM comments WHERE id = :commentId";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":commentId", $commentId);
    $stmt->execute();
    $articleId = $stmt->fetchColumn();

    // 删除评论
    $sql = "DELETE FROM comments WHERE id = :commentId";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":commentId", $commentId);
    $stmt->execute();

    // 更新文章的评论总数
    $sql = "UPDATE articles SET comment_count = comment_count - 1 WHERE id = :articleId";
    $stmt = $pdo->prepare($sql);
    $stmt->bindParam(":articleId", $articleId);
    $stmt->execute();
}
?>