I have a db schema where user data is stored using foreign key references , these foreign keys are admin defined . Also , there is some data which is stored without primary keys , however I have other constraints in place to avoid redundancy and other issues .
Due to the requirements of the application when a user 'updates' their info I have to delete all user records from the 'updated' table and reinsert all the user's records again . (I have looked into all other options)
Now because of my search solution (solr) , I need to track changes to the user data (updates/deletes) . I am planning on having a view to compare the last committed data to the real time data . I am fearful of how sustainable it would be to have a stored procedure running every 20 minutes or so , is there a better way of tracking data with SQL ?
You could create a change table that contains the same columns as the original table plus another column called "UpdatedOn." Then set up a trigger to write to this change table the original values when the original table is changed.
Example: Original Table:
Name | Address | City
Jane Doe | 1 Main St. | New York
Change to Original Table:
Name | Address | City
Jane Doe | 2 Smith St. | Dubuque
...which triggers an insert to the Change Table:
Name | Address | City | UpdatedOn
Jane Doe | 1 Main St. | New York | 2012-01-01
There is information about using triggers in mysql here: http://dev.mysql.com/doc/refman/5.0/en/triggers.html