My project concept is to insert some questions into a database and answers people provide. However, these questions and admin can be modified by the admin later (and even delete them)
I need to recover answers and questions. However, I need to retrieve the exact questions and answers, that means the exact question even if admin has changed them in database.
That is a kind of history manager that saves the state of questions and answers.
I can manage a whole database history etc.. but is there a smarter way to manage it?
First off, you should never delete stuff in this situation. Create a boolean flag on each row and set it to 1 to mark deletion, then do all your queries while taking this flag into account. This will allow you to "mark" stuff as deleted, while not actually pruning from the DB.
This also has the advantage of not completely confusing your foreign key relationships or avalanche-deleting stuff you wanted to keep.
From there, you might want to look into database triggers. These allow you to run pre-stored statements on certain operations (update, insert, delete). This can allow you to keep a "log table" of the changes.
Solution 1.triggers
The easiest solution (depending on your specific needs) would probably be to add an on update/insert/delete trigger to your table, so you can perform extra logging when data is inserted/updated/deleted. That way even manual interventions on the db will be covered...
Check http://dev.mysql.com/doc/refman/5.1/en/triggers.html for more information.
Solution 2.
One simple way to keep version history is to create basically an identical table (eg. with _version suffix). Both of the tables would have a version field, which for the main table you increment for every update you do. The version table would have a composite primary key on (id, version).
Whenever you do an update on the actual table, you also INSERT a new row in the version table with duplicate data. Whenever you want to find the version history, all you need to do is something such as SELECT * FROM content_version WHERE id = CONTENT_ID ORDER BY version.
If you use something like Doctrine ORM, it has a behavior that does this for you automatically via event listeners. You can check it out here: http://www.doctrine-project.org/documentation/manual/1_2/en/behaviors#core-behaviors:versionable