How can a 5-Star rating database table structure be used more efficiently? _mysql

Product database design, often encountered 5 star evaluation of the situation, how to design the data table to ensure that query efficiency, but also reduce data redundancy?

Preliminary design ideas are as follows, please correct me.

One, the ultimate effect,

Second, table structure

Copy Code code as follows:

CREATE TABLE IF not EXISTS ' books ' (
' ID ' int (8) not NULL auto_increment,
' title ' varchar Not NULL,
' Vote_1 ' int (8) unsigned not NULL,
' vote_2 ' int (8) unsigned not NULL,
' Vote_3 ' int (8) unsigned not NULL,
' Vote_4 ' int (8) unsigned not NULL,
' Vote_5 ' int (8) unsigned not NULL,
' Avgrate ' int (8) unsigned not NULL,
' Amountofvotes ' int (8) unsigned not NULL,
PRIMARY KEY (' id ')
) Auto_increment=1;

CREATE TABLE IF not EXISTS ' users ' (
' ID ' int (8) not NULL auto_increment,
' username ' varchar not NULL,
PRIMARY KEY (' id ')
) Auto_increment=1;

CREATE TABLE IF not EXISTS ' votes ' (
' UID ' int (8) unsigned not NULL,
' Bid ' int (8) unsigned not NULL,
' Vote ' int (1) Not NULL,
PRIMARY KEY (' Bid ', ' uid ')
) ;

third, design ideas

The data table is divided into two parts,

1, the first part, table votes. Where UID and bid are set as primary keys, which prevents a user from voting multiple times;

When you query, you can use the

Copy Code code as follows:

Average score: SELECT avg (vote) from votes WHERE bid = $bid;

Total Evaluations: SELECT count (UID) from votes WHERE bid = $bid;

If you have time to sort the requirements, you can add a timestamp field.

2, part two, redundant part

Vote_1 to Vote_5, only record the number of each level score, with a rating of +1;

Avgrate record average score;
Amountofvotes record total score;

The avgrate and amountofvotes are obtained by calculating vote_1 to Vote_5, which reduces the number of queries to table votes.

If you are in line with the comments, then add the correlation in the comments

Copy Code code as follows:

CREATE TABLE IF not EXISTS ' comments ' (
' ID ' INT (one) UNSIGNED not NULL auto_increment,
' rating_id ' INT (one) UNSIGNED not NULL DEFAULT 0,
PRIMARY KEY (' id ')
)

Four, continue to optimize the need to think about the problem

The amount of data in the votes table would be N times the amount of data in book, and this design would also facilitate the votes of the table, without affecting the quick query.