Chapter 5. Designing the Database Objects

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.

Figure 5.1. Data model for JAudit 1.0

Data model for JAudit 1.0

The sections that follow describes the underlying tables used by the Macrotone Joomla Audit component.

Change History Table

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.


Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries