php - 我的数据库模型(很糟糕)让人头疼，如何改进它？

Question

我有一个关于在用户之间共享衣柜的应用程序。这个想法很简单，成员发布他们的衣服，其他人可以评论或按“赞”按钮保存它们。

我有三个表:

产品:

+-------------+-------------+------+-----+-------------------+----------------+
| Field       | Type        | Null | Key | Default           | Extra          |
+-------------+-------------+------+-----+-------------------+----------------+
| ID          | int(11)     | NO   | PRI | NULL              | auto_increment |
| NAME        | varchar(16) | NO   |     | NULL              |                |
| FBID        | varchar(32) | NO   |     | NULL              |                |
| TITLE       | text        | NO   |     | NULL              |                |
| DESCRIPTION | text        | NO   |     | NULL              |                |
| BRAND       | varchar(16) | NO   |     | NULL              |                |
| SIZE        | varchar(12) | NO   |     | NULL              |                |
| CATEGORY    | varchar(22) | NO   |     | NULL              |                |
| COLOR       | varchar(12) | NO   |     | NULL              |                |
| COND        | varchar(12) | NO   |     | NULL              |                |
| ORIGPRICE   | varchar(8)  | NO   |     | 0                 |                |
| SALEPRICE   | varchar(8)  | NO   |     | 0                 |                |
| IMAGES      | text        | NO   |     | NULL              |                |
| TIMESTAMP   | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| SOLDSTATUS  | varchar(1)  | NO   |     | 0                 |                |
| VIEWS       | int(6)      | NO   |     | 0                 |                |
| RECOMMENDED | varchar(1)  | NO   |     | 0                 |                |
+-------------+-------------+------+-----+-------------------+----------------+

喜欢:

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| ID     | int(11)     | NO   | PRI | NULL    | auto_increment |
| PRODID | varchar(11) | NO   |     | NULL    |                |
| FBID   | varchar(32) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

评论:

+-----------+-------------+------+-----+-------------------+----------------+
| Field     | Type        | Null | Key | Default           | Extra          |
+-----------+-------------+------+-----+-------------------+----------------+
| ID        | int(11)     | NO   | PRI | NULL              | auto_increment |
| PRODID    | int(11)     | NO   |     | NULL              |                |
| NAME      | varchar(32) | NO   |     | NULL              |                |
| FBID      | varchar(32) | NO   |     | NULL              |                |
| COMMENT   | text        | NO   |     | NULL              |                |
| IMGPATH   | text        | NO   |     | NULL              |                |
| TIMESTAMP | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+-----------+-------------+------+-----+-------------------+----------------+

到目前为止，(我认为我在这里错了)，为了在主页上显示产品以及喜欢/评论的数量，我使用嵌入到查询中的单独函数进行了子查询，例如:

$query = "SELECT * FORM PRODUCTS"
if($result = mysqli_query($mysqli, $query)) {
    while($row = mysqli_fetch_assoc($result)){
             $jsonRow = array(

             'sqlId'            =>  $row['ID'],
             'name'             =>  $row['NAME'],
             'likecount'        =>  countLikes($row['ID'], $mysqli),
             'commentcount'     =>  countComments($row['ID'], $mysqli)
);
}

现在，在 10.000 多条记录之后，为了提高性能，我尝试过:

1. 加入所有三个表，但这样我就可以对“喜欢”进行一次分组/计数，而无法同时对“评论”进行计数。
2. 或者，为以下内容创建新列:在 PRODUCTS 表内的 LIKESCOUNT，并在每次用户喜欢某个产品时通过计算 LIKES 表中产品的出现次数 (PRODID) 来更新该列。

关于如何解决这个问题还有其他想法吗？谢谢

Answer 1

To JOIN all three tables, but this way I can group / count things once for LIKES without the possibility of counting COMMENTS in the same time.

您可以在一个查询中计算“赞”和“评论”的数量。但您需要使用子查询(以免在 LIKES 和 COMMENTS 之间创建交叉联接)。

select sub.*, count(l.PRODID) as likecount
from (
    select p.*, count(c.PRODID) as commentcount
    from products p
    left join comments c on c.PRODID = p.ID
    group by p.ID
) sub 
left join likes l on l.PRODID = sub.ID
group by sub.ID

您还可以计算子选择中的评论和点赞数。

select p.*,
    (
        select count(*)
        from comments c
        where c.PRODID = p.ID
    ) as commentcount,
    (
        select count(*)
        from likes l
        where l.PRODID = p.ID
    ) as likecount
from products p

但我可能会运行三个查询

select * from products;

select PRODID, count(*) as commentcount from comments group by PRODID;

select PRODID, count(*) as likecount from likes group by PRODID;

并在 PHP 中合并结果。

$products = array();

$query = "SELECT * FORM PRODUCTS";
$result = $mysqli->query($query);
while($row = $result->fetch_assoc()) {
    $products[$row['ID']] = array(
        'sqlId'            =>  $row['ID'],
        'name'             =>  $row['NAME'],
        'likecount'        =>  0,
        'commentcount'     =>  0

    );
}

$query = "SELECT PRODID, COUNT(*) as commentcount FROM comments GROUP BY PRODID";
$result = $mysqli->query($query);
while($row = $result->fetch_assoc()) {
    $products[$row['PRODID']]['commentcount'] = $row['commentcount'];
}

$query = "SELECT PRODID, COUNT(*) as likecount FROM likes GROUP BY PRODID";
$result = $mysqli->query($query);
while($row = $result->fetch_assoc()) {
    $products[$row['PRODID']]['likecount'] = $row['likecount'];
}