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.
It depends on what you intend to do with the data you record You could add a simple char column that catches a code or You could add a foreign id column that links back to another table that has each type listed.
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.