What would be the appropriate way to register changes to the database using CodeIgniter

Question

I want to create a simple audit system for my small CodeIgniter application. So that the record was edited, it must take a snapshot of the record in the table. One way I could think of is to create a table news_auditthat will replicate all the columns in the news table. It will also create a new entry for each change with an added date added column. What are your views and opinions on creating such functions in a PHP web application?

Answer 1

There are a few things to consider before deciding which solution to use:

If your table is large (or can become large), your audit trail should be in a separate table, as you describe, or performance will suffer.

If you need an audit that cannot (potentially) be changed except to add new entries, it should have INSERT permissions only for the application (and be cast-iron should be on a dedicated logging server ...)

, ( , ), , db , (== ). , , , db NULLS. , .

, , UPDATE TRIGGER , , INSERT). , , , , db . INSERT, , , .

, db , ...

Answer 2

( ), , , , XML .

SQL2005 +, XML , .

, , (, , ...)

.

Answer 3

? , ?

, , diff , .

Answer 4

, , - . "valid_until". "" , "valid_until" . - , valid_until. , . valid_until. , , , valid_until.

. - . . , . WHERE , /.

, .

, Temportal database.

Answer 5

:

• (.. Subversion)
• SQL- (.. patches/)
• , (.. 086_added_login_unique_constraint.sql)
• . , , . . .
• script , .

, , . . /UI/- . .

This approach scales quite well. Worked on a PHP / PostgreSQL project consisting of 1300+ classes and 200+ tables / views.