SQL tracking payment over time

Question

I have been tasked to move a process that pays people for training from an excel spreadsheet to sql server DB. I need to be able to track payments and the reason why it was approved, denied. Example:

Payment Run Jan1

Student	Class	Amount	Reason for NonPayment
Mary	Introduction to Python	0	No W2
John	Introduction to Java	100

Payment Run Feb 1

Student	Class	Amount	Reason for NonPayment
Mary	Introduction to Python	100

Now I know I should make three tables , One for student info, one for course info , and a linked table with payments. It the payments table that has me stumped. I can do that for Jan1 , but how do I track the changes ? I want to be able to say "On Jan runs Mary did not get paid because she was missing her W2, but she was paid in Feb" . For every payment run, I need to be able to track who got paid, amount paid , reason for nonpayment ( if present ) .

Answer 1

My bad. I was forgetting about many to one relationship. I was thinking more about addresses, where you "retire" the address, and only have the new link active.

So instead of "retiring" the link to the payment table on every run, keep a "PaymentRunDate" field and have a key referencing the user.

Like this ( given Marys ID is 15 , John Id is 5 , dates are in European format)

UserId	Class ID	PaymentRunDate	amount_paid	Reason
15	1	01/01/2022	0	No W2
5	2	01/01/2022	100
15	1	01/02/2022	100

and let the front end worry about how this is presented to the user.