我的数据库模型（糟透了）给人头疼，如何改善它？

Question

I have an app which is about sharing wardrobes between users. The idea is simple, members posts their cloths, others can comment or save them by pressing the like button.

I have three tables:

Products:

+-------------+-------------+------+-----+-------------------+----------------+
| 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                 |                |
+-------------+-------------+------+-----+-------------------+----------------+

Likes:

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

Comments:

+-----------+-------------+------+-----+-------------------+----------------+
| 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 |                |
+-----------+-------------+------+-----+-------------------+----------------+

So far, (and I think I went wrong here), to display the products on the homepage along with the number of likes/comments I made subqueries using separate functions embedded into the query, like:

$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)
);
}

Now, after 10.000+ records, to improve performance I have tried:

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.
2. Or, to create new columns for: LIKESCOUNT inside the PRODUCTS table and update this each time a user likes a product by counting the product's appearances (PRODID) in the LIKES table.

Any other thoughts on how to make this right? Thanks

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.

You can count LIKES and COMMENTS in one query. But you need to use subqueries (in order not to create a cross join between LIKES and 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

You can also count the comments and likes in a subselect.

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

But i would probably run three queries

select * from products;

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

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

and combine the results in 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'];
}