Table of Contents
Once the database requirements were defined, the next step was to turn these into a database design and an outline that describes how the user interface accepts and presents data. Given the requirements described above the two main tables can be created:
-
Triggers contains the trigger definitions to be stored in the database.
Further detail notes upon the table is provided below.
The next was the main table to contain the captured change records (audit records) from the action of the database triggers.
-
Change History contains the actual change records.
System generated primary keys are used for all tables so that all the data can be edited without executing a cascade update.
The data model designed is shown in Data Model.
The sections that follow describes the underlying tables used by the Macrotone Joomla Audit component.
The Change History table contains the change records and is populated by the database triggers created and enabled in the MySQL database.
Table 5.1. Change History table description
Column Name |
Type |
Size |
Not Null? |
Constraints |
Description |
|---|---|---|---|---|---|
id |
Bigint |
20 |
Yes |
Primary key |
The system generated unique identifier for the record. Populated by an auto-sequence. |
table_name |
Varchar |
256 |
Yes |
None |
Name of the table for which the change is recorded. |
component |
Varchar |
255 |
Yes |
None |
The component to which the change record applies. |
state |
TinyInt |
4 |
Yes |
None |
State of the specific record. i.e. Published, archived, trashed etc. |
row_key |
Int |
11 |
No |
None |
The value of the primary key for the record. |
row_key_link |
Text |
255 |
No |
None |
The value of the alias for the record if it exists, the value of the Primary key otherwise. |
column_name |
Varchar |
255 |
Yes |
None |
The name of the table column being recorded. |
column_type |
Varchar |
12 |
Yes |
Varchar |
The type of column for the record. i.e. Integer, Varchar etc. |
old_value |
MediumText |
No |
None |
For an DELETE or UPDATE action the former field value. |
|
new_value |
MediumText |
No |
None |
For an UPDATE or INSERT action the new field value. |
|
action |
Varchar |
12 |
No |
None |
The action of the change record. i.e.INSERT, UPDATE or DELETE. |
change_date |
DateTime |
n/a |
No |
None |
The date when the change was made. |
change_by |
Int |
11 |
No |
None |
The Joomla id of the person who made the change where it can be determined otherwise the super user id. |