My application will allow users to like or dislike a product and leave a short feedback. I have to make a functionality which will show graph and produce report based on different time frame, probably it will be yearly, monthly, weekly and daily basis.
I have to show how many users liked or disliked the product on a particular time duration via a chart and generate the report. So my application should be able to produce the daily graph of August 2018 or monthly graph of year 2018 of a particular product (these are examples only). The graph should be able to reveal how many users liked or disliked the product on daily basis if it is daily graph, Similarly it may be for weekly, monthly or yearly time frame
I am not sure what should be the database structure for this type of application? Here what I have thought so far.
// products table
products: id, name, descp...etc
// users table
users: id, name, email ...etc
// user_reactions table
user_reactions: id, user_id(foreign key), product_id(foreign key), action(liked or disliked, tinyint), feedback
// data table, will be used to make graph and report
data: id, product_id(foreign key), date(Y-m-d), total_like, total_dislike.
What, I am thinking is that, I will run a cron job on 23:59:59 every day to count the like and dislike of each product and will add the data in last table, i.e. data table as mentioned above and then will use this data table to make graph and report. I am not sure if this database structure is correct or it have some unseen problem (may be in future?)
Note: My Application will be in PHP and MySQL (web based Application)
No responses yet.