Track and Store Changes from a Table?

I have a table in my MySQL database which contains a list of subscribers. I want to be able to track many different changes that happen to this table. For example, I need to get ‘Average Subscribes per Month’, ‘Average Unsubscribes per Month’, etc. Which means, I would need to know how many additions are made to this table per month and similar numbers. I do not know if there is an easier way to do it using Yii. If there is, please let me know.

One think that I’ve considered till now, is to have a table which stores the historical data. Triggers can then be used to track changes in the MySQL, although I’m not very sure how to do it. The key thing here is, the subscribes and unsubscribes can happen anywhere on other websites and get stored in my database. So I really wonder how can we track all this. I’m sure experienced developers would have come across this kind of a problem before. Need some help on this.

This is not really a yii problem but rather a question on how to design your database tables. Its up to you to create a DB layout that matches your requirements. After that you can utilize the events that CActiveRecord provides. E.g. you could override ‘afterFind’ and ‘beforeSave’ method to track any changes that happened since you loaded an object.

Yep, it’s a generic question. But I wanted to know if Yii does provide any mechanism to help with this using AR. I can use beforeSave, afterSave no doubt but as I mentioned, the changes to the database can happen from other websites. For example, someone filling up subscription form elsewhere. Really not sure how that would work so need some help.

You can use the "auditTrail" extension for this functionality, but this will only help to track changes made by your application.

You can use triggers if you need to monitor all changes

Okay, thanks. Triggers seem to be the best solution to my problem.