Hi, I have three tables: students , amounts and pending_payments .
With these fields:
Students:
id, nombres, cédula, teléfono.
Amounts:
id, id_curso, concepto, monto.
Pending payments:
id_alumno, id_monto, estado.
The table pagos_pendientes
relates to the other two. Then I must show the amounts that each student has not paid.
The system works like this:
I enroll students in courses, noting that a student can be in 1 or more courses.
Each course has a series of payments according to the duration: 5 months = 5 amounts .
Then, to process the student's payment, I must see the pending amounts that said student has in order to later process said amount.
Having processed the payment, I save the information in a table called pagos_hechos
and update the status of the payment to estado=1
.
What I want to know is how I can see those pending payments that each student has, I save the amounts to pay in the amounts table which contains the id_curso
to know which course those amounts belong to.
I need guidance on how I should relate these tables to obtain the information in a concrete way.
I think you could solve your problem with two tables:
alumnos
pagos
In the present design, the same table
pagos
would serve to indicate the general state of accounts of each student, for this you would only add a columnpagado
of the boolean type to said table. Through that column you would know what each student has paid or not.Thinking of a more sophisticated design, you could even pre-populate the table
pagos
with everything that each student must pay according to the courses in which they are enrolled. In said better designed design, you would have a table that manages, according to the courses, the number of payments that there are for each course and the amount of said payment. You could also manage special clauses according to the student, such as special discounts per student due to concepts such as scholarships or others... That way, when the student comes to pay, you only establishtrue
the payment corresponding to the course and the sequence that they came to pay .That way you avoid having to maintain a third table that stores pending payments. You gain in simplicity and avoid having data in redundant tables.
Here I leave you an example code, knowing that using grouping queries you could obtain interesting results according to the needs of your application.
I hope it helps you.
Código:
Ver Demo
Resultado:
To do this, what you need is to validate the payments already made and the students from the PAGOS_PENDIENTES table and some INNER JOINS to the related tables in order to obtain the columns you want, your code would be the following:
I hope my answer helps you, greetings.
Alejo. I have exactly the same case, with the same tables! What I did was make a single table, payments, with a "status" column, and at the time of registration I run a code that loads all the payments with their due date; then I consult payments due and I can ask them to send an email claiming payment to all those who are pending. Of course, we depend on the data entry that is loaded into the system when a payment was made.