CODEWITHSUNDEEP
mysqldesign-patternsrdbms
lucaskos
lucaskos

Reputation: 67

Keeping rating for multiple tables

Having the db design with more than two tables representing main entities e.g. book, publisher, author I want to find out what will be best approach for user-rating for a given entity.

Is it the best to create one table with no relation to the above tables but only for user table (to show user the rating of a given table record) and indicate the type of related table with varchar?

Or is it better to keep separate rating table for each of the tables.

My idea would be as below but I'm not sure is it the best way to store this kind of data.

***rating table***
ObjectType
ObjectId
UserId
RatingScore

Upvotes: 0

Views: 129

Answers (1)

Ananth Cool
Ananth Cool

Reputation: 135

Maintain a rating table as per your description may slow down your DB. For example just consider 1 million user going to give rate score for a book.

So single book entity will have 1 million records in you ratting table. While taking average ratting you have to read this 1 million record which require more memory and time. Instead use below tables to make sure the calculations working with less time and memory consume.

Ratting_Table
Id: 
ObjectName: (Book,Author,Publisher)
ObjectId:(Id of Book,Author,Publisher entity) 
1Ratting_Count: 
2Ratting_Count:
3Ratting_Count:
4Ratting_Count:
5Ratting_Count:

Keep list of available actions on Book, Author, Publisher entity as below.

Activity_Table 
Id:
Action: View, Rated, Ordered,etc.,

For each user record their activity as below.

UserActivity_Table
Id
UserId   
ActivityId: Id from activity table.
ActivityJSON:

For ActivityJSON keep this below three structure for each activity and Save them as JSON in your audit table

For View

{
    ObjectName: Book,Author,Publisher
    ObjectId: (Id of Book,Author,Publisher entity)
    ViewTime: 
}

For Rating 

{
    ObjectName
    ObjectId
    Score
}

For Ordered

{
    ObjectName
    ObjectId
    NumberOfOrders
}

After implement this table systems, in your server side API update user count for each ratting in Ratting_Table. If a user going to update the ratting for already ratted entity (If this feature enabled) take his audit and search for existing ratting score on same entity. Then audit his new ratting as a new entry in UserActivity_Table record and update corresponding entity row Ratting_Table

Upvotes: 1

Related Questions