What is the best way to create an access report?

Question

I am setting up a website recipes (PHP and Mysql) and would like to have in the backend a login report, where I can know what recipes each user sees more, to then suggest other recipes based on these reports.

I thought of mounting a table in Mysql of type:

id|id_receita|id_usuario|data       |visualizacoes
1 |   34     |   10     |2018-04-10 |    3

So every recipe he sees, he adds a line, and if he sees the same recipe on the same day, he adds up the views.

My question is: can this make the table very "heavy" when you have many recipes and many users? It is the best way to assemble a report?

Thank you!

Answer 1

Analyzing what you want...

Suggest recipes for the user

There are several ways to do this:

1. Know which user accessed which recipe and how many times to store in the database and select recipes from this data

You will have a field with the table primary key (id), two with the user’s foreign keys (id_usuario) and revenue (id_receita), you have some ways of knowing that amount:

1.1. Have an amount column and always add one more when the user sees a recipe.

This is an efficient way, but is no longer complete, since the only information saved will be how many times the user accessed which recipe, even if added a date field it will save only the last time a particular user accessed a particular recipe

1.2. Store each access in a different row and to know that amount use the COUNT() grouping by user and recipe

It is a more expensive method (more data to store), but this allowed you to store more data that may be useful, for example, you can save the location where the revenue was accessed and from there suggest recipes typical of that location

2. You can also create a form where the user specifies their preferences

In this case I suggest the creation of several columns: lactose, sweet, salty, nutritious etc. and set as TRUE those which have a particular characteristic or FALSE if you don’t have

3. The least expensive way for the database (less data to be saved, in this case, none) is to suggest recipes from the current recipe, so if the user is seeing an X recipe, recipes with characteristics similar to X will be shown to him

IMPORTANT: Do not worry so much about performance, it is not necessary to fetch all the data from the database immediately, search initially only the revenue that the user is viewing and then, through AJAX, search for other

Evaluate what you want with your application, if it is really necessary to be so careful with a "heavy" bench and then choose what you believe to be the best