Help with adding transaction tracking data

The php video web script that I’m trying to modify has a process where Users generate credit units from viewed videos.
The total ‘balance’ of credits is stored in mysql db table named 'User', in a column titled “balance”, with each total reflected in a users’ row, and the 'balance' total gets updated (goes up or down), depending on the activity of the credits.

However, if 2 credits get added to the balance, for example, there is no tracking of what type of activity caused the balance total change.

I believe a separate table that gets a new row inserted for each transaction, that affects the amount should be created? but I don't know how to go about doing that. Any guidance you'd like to share is appreciated.
What else might this need? Or do you have a better/another idea?
I look forward to all comments/suggestions.

 

All you need to do is, create a new table "tracking_data"
Something like this:

id bigint(18) not null auto_increment,
userid bigint(18),
tracking_activity varchar(256),
activity_type char(7),
activity_date date


Now, on each activity page, just before balance is updated, add records to this table,
something like this

insert into `tracking_data`
( id, userid, tracking_activity, activity_type, activity_date ) values
( '', '123', 'watched video ', 'add', 'timestamp' )


insert into `tracking_data`
( id, userid, tracking_activity, activity_type, activity_date ) values
( '', '123', 'purchased video ', 'minus', 'timestamp' )

You can set field types differently.
Instead of BIGINT(), you can use INT()
Instead of varchar(256), you can use char()
Instead of activity_type being a char, you can use boolean (for add or minus balance)

If your update balance is inside a function, then use these inserts inside that function, and you will not need to modify each activity page separately.