我的数据库模型(糟透了)让我头疼，如何改进它 - My database model (sucks) gives to mush headache, how to improve it?

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

我有三个表:

产品:

+-------------+-------------+------+-----+-------------------+----------------+
| 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 +记录之后，为了提高性能，我尝试了:

连接所有三个表，但这样我可以分组/计数的东西一次喜欢没有可能计数评论在同一时间。
或者，在PRODUCTS表中为:LIKESCOUNT创建新列，并在每次用户点赞时通过在likes表中计算产品的外观(PRODID)来更新该列。

关于如何使这个正确，还有其他的想法吗?由于

连接所有三个表，但是这样我可以对like分组/计数一次，而不可能同时计数COMMENTS。

可以在一个查询中统计LIKES和COMMENTS。但是您需要使用子查询(为了避免在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'];
}