Logic for using foreign key - a user’s notes

Asked

Viewed 118 times

0

I am aware of a problem in the use of foreign key. I cannot find the logic for a system:

The problem: the user gives a note for each question, each one being a table field, and at the end the average is calculated and inserted into the database. But this note, as well as the average is cumulative, that is, the second user will give his notes and will be added with the evaluation of user 1, divided and thus will be generated the average. I’m not getting to understand how I can recover the notes and the average given by user 1. Can anyone help me just with logic, please.

tabela "estabelecimento"

tabela "usuario"

mysql database foreign-key

  • Change your question by showing how your database is

  • I added the image

  • Tell me something: this table you posted already has one idUser and media. In case, it wouldn’t just be making a select media from tabela where idUser = 1?

  • Rephrase your question, I will answer because I think I understand but as it was assembled it is not clear how it works. remember you are involved in this context other users not!

  • I don’t know if I’m the one who’s complicating things or really just that. The user id is to be the foreign key of the user id of a table called users. If I make the command above selecting the average, it will return the average given by that user and not the cumulative average, this?

  • I’ll edit here

  • am answering ....

Show 2 more comments

3 answers

0


You must have a table for User and one for Note, this note must have a foreign key (User Id). Thus, a user will have many notes and a note only a user, when he wants to know about a certain note just make a query using the given note + user id.

I hope I’ve helped.

  • And it would also have to have a foreign key to the establishment code, right? Since each establishment will have an average of the bills. Note: Individual notes should also appear.

  • 1

    Exact! Research a little about MER (Entity Relationship Model) is a method used to plan a database, there are some techniques to identify what are the entities and relationships of your database and what becomes table in the physical database

  • Search on google about DER is a diagram that facilitates understanding of the structure of a database....

  • Step 1 - Definition of Scope Define the context to be worked on. The smaller the scope, the more assertive the model will be. Ex: Mark a query. 2nd Step - Identifying Entities Necessary to raise three questions about the Scope: What is important? Ex: Doctor, Patient, Consultation, etc. What are the attributes (characteristics)? Is he strong? That is, objects that do not depend on others to exist, for example: doctor and patient. From this it is possible to define the entities of the Scope, as in the medical example and patient are entities within this context.

0

If a product has N questions then in modeling it is assumed that we have to have 2 tables one for products and other for questions because this is a relation called 1:N or I mean:

A product can have N quesitos.

In this case in the table quesitos you will have to put a column with the id_product to specify the grade for each product that was evaluated and therefore if you used the primary key of the products table in another table this must be an FK index called in Portuguese foreign key.

Finishing whenever you want to calculate the average of a product just make a querie in the table quesitos looking for the specific id_product field you want to calculate and the id_quesito.

Use the AVG NO SQL command to already have the product average specified. Or Use SUM to add the notes and divide by the total number of users who evaluated that item

https://www.w3schools.com/SQl/func_mysql_avg.asp https://www.w3schools.com/SQl/func_mysql_sum.asp

0

Do so for user average 1:

select media from TABELA where idUser = 1

To catch everyone’s average:

select (sum(media) / count(*)) as mediatotal from TABELA

Browser other questions tagged mysql database foreign-key

You are not signed in. Login or sign up in order to post.