Extension Documentation

Chapter 6. Database Design
Prev Part III. Design Next

Chapter 6. Database Design

Table of Contents

Designing the Database Objects
Database Objects - Procedures, Views, Triggers
Database Triggers
Database Routines (Procedures)
Database Views
Data Model
Timecodes Table
Users Table
Timesheet Table
Timesheet Log Table
Implementing Database Objects
Additional Database Objects Needed
About Building Database Objects
Default Base Data

In this section we specifically concentrate upon the underlying database objects.

Designing the Database Objects

Once the database requirements are defined, the next step is 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 three main tables can be created:

  • TimeCodes provides all the valid codes and their descriptions which are valid to which the user can allocate time.

  • Users contains information about the users who can be create (or approve) Timesheet entries

  • timesheets contain the weekly entries for the user.

Further detail notes upon the main tables is provided later in this document.

System generated primary keys will be used for all tables so that all the data can be edited without executing a cascade update.

Database Objects - Procedures, Views, Triggers

Our experience tends to indicate that very few (if any) Joomla components make use of the underlying database to its full advantage, preferring instead to implement features at the application layer. This is in our opinion missing a very important 'trick', since the database is 'closest' to the data itself.

Database Triggers

The component uses basic triggers on the tables to record insert, update and delete information which is store in the audit records.

The following description is taken from Wikipedia:

A database trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new record (representing a new worker) is added to the employees table, new records should also be created in the tables of the taxes, vacations and salaries.

Triggers are commonly used to:

  • audit changes (e.g. keep a log of the users and roles involved in changes)

  • enhance changes (e.g. ensure that every change to a record is time-stamped by the server's clock)

  • enforce business rules (e.g. require that every invoice have at least one line item)

  • execute business rules (e.g. notify a manager every time an employee's bank account number changes)

  • replicate data (e.g. store a record of every change, to be shipped to another database later)

  • enhance performance (e.g. update the account balance after every detail transaction, for faster queries)

The examples above are called Data Manipulation Language (DML) triggers because the triggers are defined as part of the Data Manipulation Language and are executed at the time the data is manipulated. Some systems also support non-data triggers, which fire in response to Data Definition Language (DDL) events such as creating tables, or runtime or and events such as logon, commit and rollback. Such DDL triggers can be used for database auditing purposes.

The following are major features of database triggers and their effects:

  • triggers do not accept parameters or arguments (but may store affected-data in temporary tables)

  • triggers cannot perform commit or rollback operations because they are part of the triggering SQL statement (only through autonomous transactions)

MySQL

MySQL 5.0.2 introduced support for triggers. MySQL supports these trigger types:

  • Insert Trigger

  • Update Trigger

  • Delete Trigger

[Note]Note

MySQL allows only one trigger of each type on each table (i.e. one before insert, one after insert, one before update, one after update, one before delete and one after delete).

[Note]Note

MySQL does NOT fire triggers outside of a statement (i.e. API's, foreign key cascades)

Whether a specific database user can create these triggers will depend upon the permissions granted to the database user. The database user that has to have the correct permission in the Joomla environment is the user that is used to perform all database connects as defined in the Joomla installation itself.

[Note]Note

Different databases implement database features such as database triggers in different way, and often with slightly different syntax. For that reason the current version only supports MySQL databases, the most commonly used database for Joomla systems.

Database Routines (Procedures)

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure is actually stored in the database data dictionary.

The component does not use database routines in the initial release.

Typical use for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures can consolidate and centralise logic that was originally implemented in applications. Extensive or complex processing that requires execution of several SQL statements is moved into stored procedures, and all applications call the procedures. One can use nested stored procedures by executing one stored procedure from within another. This is more efficient that handling the actions at the application layer, since the database is 'closer' to the actual data being manipulated.

Stored procedures are similar to database functions. The major difference is that functions can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.

The component in the initial release does not make use of any database procedures.

Database Views

A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated from data within the database, dynamically when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view.

Views can provide advantages over tables:

  • Views can represent a subset of the data contained in a table; consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.

  • Views can join and simplify multiple tables into a single virtual table

  • Views can act as aggregated tables, where the database engine aggregates data (sum, average etc.) and presents the calculated results as part of the data

  • Views can hide the complexity of data; for example a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table

  • Views take very little space to store; the database contains only the definition of a view, not a copy of all the data which it presents

  • Depending on the SQL engine used, views can provide extra security

Timesheet does not in its initial release make use of any database views.

Data Model

The data model designed is graphically shown in Figure 6.1, “Data model for Timesheet 1.0”.

Figure 6.1. Data model for Timesheet 1.0

Data model for Timesheet 1.0

Timecodes Table

Each entry belongs to a specific category.

Table 6.1. Timecodes table description

Column Name

Type

Size

Not Null?

Constraints

Description

id

Number

n/a

Yes

Primary key

A unique numeric identification for each timesheet code. Populated by an auto-sequence.

project_code

Varchar

20

No

None

Short Code or Cost code for Project.

description

Varchar

1024

Yes

Description of Project Code.

active_status

Enum

n/a

Yes

N

Displays the flag status whether the project is active or inactive. Values Y or N

usage

Varchar

2048

No

Optional text providing the user with instructions (if neccessary) as to when the time code should be used.

code_type

Enum

n/a

Yes

P

Displays the code type whether the timesheet code is project (P), holiday (H) or other (O).

state

TinyInt

4

No

None

State of the specific record. i.e. Published, archived, trashed etc.

checked_out

Number

11

Yes

None

Joomla field record locking

checked_out_time

Date

n/a

Yes

None

Joomla field record locking

ordering

Int

11

No

None

the column used for ordering records. Not used.

created_on

Date

n/a

Yes

None

Date the record was created.

created_by

Varchar

255

Yes

None

The user who created the record

modified_on

Date

n/a

Yes

None

The date the record was last modified.

modified_by

Varchar

255

Yes

None

The user who last modified the record.

language

Character

7

No

None

Language code.

Users Table

The users table is used to provide information for their timesheet. It also provides additional checks to ensure that only users specified in this table can enter or view their own timesheet information. The only exception to this is the authorisation for the 'approver' of the submitted information is also defined in the user entry. The entry is created by the back end administrator, selected from the users registered upon the site.

Table 6.2. Timesheet Users table description

Column Name

Type

Size

Not Null?

Constraints

Description

id

Integer

11

Yes

Primary key

The system generated unique identifier for the user. From the Joomla users table.

crid

Int

11

Yes

1

Current Role. Not currently used.

person_name

Varchar

255

Yes

None

The unique name of the person. From the Joomla Users table.

username

Varchar

255

Yes

None

User name in the timesheet application. From the Joomla users table.

userid

Int

11

Yes

None

Association id from Joomla #__users table.

is_admin

TinyInt

0

No

None

Indicates that the user is a timesheet administrator. Not currently used.

manager_ids

Varchar

255

No

None

The ids of the people who can approve this users timesheet.

email

Varchar

255

No

None

An email address for the user. From Joomla #__users table.

state

TinyInt

4

No

None

State of the specific record. i.e. Published, archived, trashed etc.

checked_out

Number

11

Yes

None

Joomla field record locking

checked_out_time

Date

n/a

Yes

None

Joomla field record locking

ordering

Int

11

No

None

the column used for ordering records. Not used.

created_on

Date

n/a

Yes

None

Date the record was created.

created_by

Varchar

255

Yes

None

The user who created the record

modified_on

Date

n/a

Yes

None

The date the record was last modified.

modified_by

Varchar

255

Yes

None

The user who last modified the record.

language

Character

7

No

None

Language code.

Note:

The minimum amount of information is required about the user, sufficient only to be able to control Timesheet entry/viewing.

The foreign key on the username field to the #__users table was removed in release 1.0.2.

[Note]Note

The design deliberately makes the adding of Timesheet users a back end administrative step. The reason is that not all users registered upon a site may be required to create timesheets, and given that there may be a lot of registered users, the administrator can select the desired users that are deemed applicable..

Timesheet Table

This is the main table containing the time records for a specific time period, usually for a weekly period.

Table 6.3. timesheet table description

Column Name

Type

Size

Not Null?

Constraints

Description

id

Number

n/a

Yes

primary key

A unique numeric ID that identifies a record. Populated by an auto sequence.

username

Varchar

255

No

-

Employee whose timesheet is entered.

userid

Int

11

No

-

User id as specified in Joomla user table.

time_period

Varchar

128

Yes

-

Time period for the given timesheet.

week_period

Int

11

Yes

-

The number of days in a working Usually either 5 or 7.

week_start

Varchar

24

Yes

-

The starting week day for the grid display. i.e MON, SUN, etc. Future expansion.

ts_date

Date

n/a

No

None

Approved Date for the timesheet.

no_hrs

Decimal(5,2)

-

No

None

Number of hours worked on a project by the employee for a particular date.

emp_remarks

Varchar

1024

No

None

Remarks entered by the employee as additional notes for admin.

edit_remarks

Varchar

10245

No

None

Remarks upon the reason for the edit of the record. Future expansion.

locked

ENUM

255

No

N

Whether the sheet is locked/approved to prevent further updates by the user. Values Y or N

admin_remarks

Varchar

1024

No

None

Remarks entered by the admin.

flex_01

Text

-

No

None

Flex field 1 for extensibility and future use.

sheet

Text

-

No

None

Field for storage of sheet data in JSON format.

language

Character

7

No

None

Language code.

state

TinyInt

4

No

None

State of the specific record. i.e. Published, archived, trashed etc.

checked_out

Number

11

Yes

None

Joomla field record locking

checked_out_time

Date

n/a

Yes

None

Joomla field record locking

ordering

Int

11

No

None

the column used for ordering records. Not used.

created_on

Date

n/a

Yes

None

Date the record was created.

created_by

Varchar

255

Yes

None

The user who created the record

modified_on

Date

n/a

Yes

None

The date the record was last modified.

modified_by

Varchar

255

Yes

None

The user who last modified the record.

Timesheet Log Table

The Timesheet Log table is used by the logging routines to store information, of different priorities that are used to provide additional information upon the operation of the component. It is built on top of the standard Joomla JLog libraries.

Table 6.4. Log table description

Column Name

Type

Size

Not Null?

Constraints

Description

id

Number

n/a

Yes

primary key

A unique numeric ID that identifies a record. Populated by an auto sequence.

priority

Number

11

Yes

n/a

The priority of the message. i.e. Alert, Info, Notice, Warning etc.

message

Varchar

512

No

n/a

The text message to store.

date

Date

No

n/a

The date the message was generated.

category

Varchar

255

No

n/a

The message category. Default ‘com_timesheet’

There is a compound index upon the category, date and priority columns in that order.

Implementing Database Objects

This first step in building an application was to create the database objects. The tables have been defined above but there are a few other database objects we require.

Additional Database Objects Needed

To populate the primary key values of the tables needed for this application, an auto incrementing sequence is used in the MySQL implementation.

About Building Database Objects

There are several tools for creating objects in MySQL. You can:

  • Use phpAdmin to create tables, views, indexes, procedures, functions, triggers etc.

  • Use a product such as Toad, which provides the same abilities as phpAdmin with a cleaner interface. This is downloadable from the Quest website.

  • Create a script in a standard text editor and then upload the script and use the MySQL command interface, Toad, or phpAdmin to execute it.

Default Base Data

There are a few provided records inserted into the tables upon installation.

The timecodes table has a few sample codes created with an id value of below 11. Any site entered code will have an id of 11 or greater. This is done so that there is no possibility of any codes being overridden on an update.

Prev Up Next
Multi-Lingual support Home Chapter 7. HTML Table grid
Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries