September 11, 2003 at 7:43 am
Hi there
I am rewriting a database for a charity, and was wondering if I could get some advice on table structures.. The charity has memberships, and currently a lot of people pay for these memberships by direct debit. The current structure has one direct debit instruction table (ddinst), with all the details (eg account details, direct debit frequency, direct debit amount etc) and there is a direct debit history table (ddhist) which has the actual direct debits recorded every month (so it records the date, the amount, whether it was rejected or not etc.)
The problem I have is what to do if the direct debit instruction is amended in any way..i.e the payment amount is increased or decreased or the frequency is increased.. ddinst will be amended as the only thing that is changing is a small detail (if they changed the bank account details a new record would have to be created) If ddinst is amended, then I would have to look at say the payment amounts in ddhist to see that they used to pay less, or that they used to pay annualy instead of yearly... I am not sure how to record the changes as they could affect different columns. The payment amounts change quite regularly as they often run upgrade campaigns to encourage people to donate more money, and the amounts that people change is not uniform
Is there an easy way to record the change and which field changed and in what way? Would the best way be when you update the record to have a trigger fire to insert a record into a table say 'trackchanges' table?
September 11, 2003 at 6:00 pm
Triggers is definitely an option. I personally am not a fan of triggers but that is one of the best purposes for them.
Another option is to build in this type of audit inserts in the stored procedure you use to do your updates and inserts.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply