How can I use a table structure with a 5-star rating database more efficiently?

When designing product databases, we often encounter 5-star reviews. How can we design data tables to ensure query efficiency and reduce data redundancy?

The preliminary design idea is as follows. please correct me.

I. Final results,

Ii. Table Structure

Copy codeThe Code is as follows: create table if not exists 'books '(
'Id' int (8) not null auto_increment,
'Title' varchar (50) 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 (20) 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 ')
);

Iii. Design Ideas

The data table is divided into two parts,

1. The first part is the table votes. The uid and bid are set as the primary key to prevent a user from voting for multiple times;

You can use,

Copy codeCode: average score: SELECT avg (vote) FROM votes WHERE bid = $ bid;

Total rating: SELECT count (uid) FROM votes WHERE bid = $ bid;

If you need time sorting, you can add another timestamp field.

2. Part 2: Redundancy

From vote_1 to vote_5, only the number of scores at each level is recorded. If yes, + 1 is recorded;

Avgrate average score;
Total AmountOfVotes records;

Avgrate and AmountOfVotes calculate vote_1 to vote_5, which reduces the number of queries to table votes.

If comments are used together, add associations to comments,

Copy codeThe Code is as follows:

Create table if not exists 'comments '(
'Id' INT (11) unsigned not null AUTO_INCREMENT,
'Rating _ id' INT (11) unsigned not null default 0,
Primary key ('id ')
)

Iv. questions to be considered for further optimization

The volume of data in the votes table is N times the volume of data in the book. This design also facilitates the votes table sharding without affecting quick query.