How to properly design a database and write SQL queries?

Question

PHP

Hello!
Need help with database design and SQL queries.
There is a form that allows the visitor to leave a review/comment. The form consists of the following fields: Full name, Email, Review category (multiple categories can be selected), Review text. There is an admin part where the admin can edit, add and delete review categories. I created a database for this application, which consists of 4 tables: user (for admin authorization), comment (for storing reviews), category (for categories) and cat_id (which stores the ID of the comment and category). This is how it looks like:

Is such a base suitable for such an application? Should we remove the cat_id table, and add a category_id column to the comment, which will store category ids? What is the best way to normalize such a base?
If the base is left as it is, how can we link the comment.id columns to cat_id.comment_id and category.id to cat_id.category_id, so that, for example, when a category or comment is deleted, the related records from the cat_id table are also deleted?
To add records to the cat_id table and display comments, I created two methods:

public function create($comment_id, $category_id)
    {
        foreach ($category_id as $value) {
            $sql = 'INSERT INTO `cat_id`(`comment_id`, `category_id`) VALUES ("' . $comment_id . '", "' . $value . '")';
            $result = $this->mysqli->query($sql);
            if (!$result) {
                die($this->mysqli->error);
            }
        }
        return true;
    }
 
public function show_comment($id)
    {
        $sql = 'SELECT firstname, name, lastname, email, comment FROM `comment` WHERE comment.id = ' . $id;
        $result = $this->mysqli->query($sql);
        if (!$result) die($this->mysqli->error);
        $a = $result->fetch_assoc();
 
        $sql = 'SELECT category_id FROM `cat_id` WHERE cat_id.comment_id = ' . $id;
        $result = $this->mysqli->query($sql);
        if (!$result) die($this->mysqli->error);
        $b = $result->fetch_all(MYSQLI_ASSOC);
 
        $c = array();
        $sql = '';
        foreach ($b as $key => $value) {
            $sql .= 'SELECT title FROM `category` WHERE id = ' . $value['category_id'] . ';';
        }
        $result = $this->mysqli->multi_query($sql);
        if (!$result) die($this->mysqli->error);
        do {
            if ($result = $this->mysqli->store_result()) {
                while ($row = $result->fetch_row()) {
                    $c[] = $row[0];
                }
                $result->free();
            }
        } while ($this->mysqli->next_result());
        $d = array_merge($a, $c);
        return $d;
    }

Please help me design a normal database, link tables and make normal SQL queries.

Answer 1

If this is not a tutorial, please use an ORM. Now no one works with bare SQL on such tasks. Because, firstly, it is difficult to deal with someone else's implementation of template mechanisms for opening / closing a connection, accessing tables. Secondly, it is necessary to solve the problems of data design, and not the implementation of their recording in the database. If the task is educational, then the scheme is standard and I like it. Everything is drawn correctly. It is implemented in the same way as any ORM:
1. Create a database connector class. We describe in it the methods of direct writing to the database and deleting records.
2. Create classes Comment and Category. Let them have get, update, delete, create methods. for example get(id) gets raw data from the connector and converts it to an associative array. It also implements getting the fields of the related category. Well, the delete method on Comment runs the delete method on Category, deleting both Comment, Category, and cat_id.

Answer 2

normal architecture. In principle, this is how it is done. In order not to violate the integrity of the database, use foreign keys with the CASCADE attribute on UPDATE and DELETE.
upd: True, I would rename the cat_id table to category_comment_assignment or just category_comment . And I would cut out the id field from it. And I would make a bunch of category_id and comment_id a key. Well, it's so ... for the sake of order :)