Case Study - Cal Pacific

October 15, 2020

Data Fidelity was approached to build an entirely new database for Cal Pacific.

Web Design Newcastle

The new Database at Cal Pacific was to replace their existing database, an Excel spreadsheet, which had been in use since 2009. As a company operating in the mining & resources industry, their main business model was to provide NATA, AS2290 gas calibrations, as well as Diesel Emissions testing for vehicles used around New South Wales & Queensland mining sites. Cal Pacific technicians would receive a purchase order from a client, book a time & then conduct testing on site. Cal Pacific had initially approached Data Fidelity instead of a Web Design business in Newcastle to build their public E-commerce website, Cal Pacific, although once work had begun, the scope of works had significantly grown in size once the potential for improving business productivity and processes had been realised. To better understand their business and its processes, we travelled to Newcastle to meet in person and learn more about the Gas Calibrations & Emissions Testing industry, learn more about Cal Pacific & its team, what they do and what makes their business special. Cal Pacific is a unique, fantastic business providing amazingly cost-effective calibration & emissions testing for the mining & resources industry, and our new system had to reflect their level of expertise and efficiency. Upon initial consultation, Data Fidelity was tasked with observing their current workflows, its limitations and its potential for scalability & versatility. We found that although their Excel spreadsheet had in fact functioned for the previous 11 years, only a single user at any given time could access the database, and make changes to certificate data, causing a bottleneck with staff & stakeholder productivity; especially on busy days. We were given a few deliverables in mind:

1) Translate the old, spreadsheet-based system into a new, SQL-based system.
2) Completely open-source, with no ongoing licensing and hosting fees
3) Exceptional security, including firewall and monitoring

4) High-level of scalability, allowing the same system to be used for a minimum of 10+ years before requiring a potential overhaul
5) Replace the existing accounting systems, integrated within the new Database
6) Synchronisation between Cal Pacific’s live E-commerce site and the internal Database

Due to the specific and unique goals of Cal Pacific, Data Fidelity has opted to build the system upon the WordPress framework. Although WordPress is commonly thought of as a website platform, it can in fact be used for much more than a simple or E-commerce website. In its simplest form, WordPress is a series of pre-built SQL, HTML and PHP functions and its limitations are the extent of which these specific languages can be used and interpreted. Specific to the goals, as WordPress is an open-source, community-driven framework, it was well within WordPress’ scalability that we could build a system with all the required functions. For Web Design Newcastle

The first step with delivering the project, was to ascertain every point of data used in the current system, and what sort of data it was. This took about a day to refine the data types, and data sets, into a model that was an accurate translation from the existing Excel spreadsheets.

Generally, in the design phase, we find it best to keep design & discussion of data in its simplest form. For Cal Pacific, this meant discussing things such as ‘What is an applied gas value & what makes it important when conducting a gas calibration test’. Of course an applied value is a number, and is therefore a NUM value type as opposed to an INT or VARCHAR. Most database developers associate data types and data sets with a standalone database built with a system such as Oracle or Microsoft SQL Server Management Studio; Although these systems do in fact work, the same system can be built using WordPress, by accessing the database via phpmyadmin, or via SSH through Microsoft SSMS and creating tables via SQL command line.

There are some relationships that quantified creating new tables not currently used by their spreadsheet, to further streamline the database via 2NF (2nd normal form). In the old database, every certificate has an associated technician who had conducted the tests. In the new system, what was done to accurately translate the old system, but optimize the database was to create an additional table (Technicians), which dynamically populates a ‘Tech’ field when creating a certificate.
For Web Design Newcastle

When the database design was completed, although in a functioning form, we had to accurately translate ‘Database language’ into ‘Gas Calibration & Diesel Emissions testing language’. This was completed by creating what is referred to as ‘frontend’ data tables, allowing employees & stakeholders to view previously entered data, including

Gas calibration, Diesel emissions & AS2290 certificate records
Jobs register, including a calendar view (filtered by user, to show only the users jobs, unless role is admin), tabular view, filterable by last week, yesterday, today, tomorrow, this week, this month, all jobs, unscheduled, overdue, or incomplete jobs.
a ‘dropdown options’ view, allowing for frontend editing of tables related to dynamic dropdown options, specific to core data sets (such as technicians, gas types, testing conditions, Unit of measurement).

In addition to creating custom functions allowing for front-end adding, editing, duplicating or deleting of records, there was an additional function which came with the system design, which was to translate over twelve excel spreadsheets of data, into a singular ‘dashboard’ view, in which all the data was viewable from a central location, while still maintaining a simplistic, easily navigable view of everything. This was accomplished through the use of ‘Popups’; keeping a list of simple clickable buttons, created using basic HTML and CSS, which would open a popup view on click to view or create data. This allowed the dashboard view to compact an enormous amount of data tables into a simple, navigable dashboard view.
Web Design Newcastle

A core function that had to be completed as part of the new system, was creation of certificates themselves. As part of Cal Pacific’s certification with NATA (National Association of Testing Authorities), the certificates that were given to clients had to have a specific layout.

We had created an additional ‘view’ function, which would copy data over from a specific row of table data (i.e. certificate data), and display it within a HTML form, written completely in raw HTML and CSS. This allowed for certificate creation to be done at the touch of a button, greatly improving processing time and productivity.

This function was extended beyond NATA specific certificates, and used for creation of:

Job card; a document given to technicians prior to an on site test, with details of the job, and all tasks to be completed.
Record sheet; a document which lists all customer details copied over from table data, with blank rows used for technicians to print the form, & write down test results while they are on site.
For Web Design Newcastle

Built to be scalable and versatile.

As covered earlier, the new database system was built using the WordPress framework. This allowed for a number of additional functions to be used, to further complement the database functions. Fundamentally, a large desirability for Cal Pacific was to design their system in mind with complete ownership of data & software, liberated from any ongoing hosting or subscription costs, or without the need for third party apps or software if not necessary. WordPress ERP was setup & installed locally, to replace the existing accounting software. Requested by Cal Pacific was an accounting package to be locally hosted, with zero integrations with third party providers such as banks; This was due to their lifetime membership with a larger accounting software provider to be rescinded, leaving Cal Pacific in a position to commit to a subscription, or to change providers and lose their previous accounting data. It was discussed that using the WP-ERP framework that there is a limitation that there are no third party integrations, although Cal Pacific did not use any previous third party integrations with any of their accounting providers; and some limitations would not impact their current accounting processes. WP-ERP customers, vendors & products were synchronised with WooCommerce framework through the use of a proprietary add-on Database Synchronization was implemented with the live site. As part of the project prerequisites, the database was to be locally hosted, whereas the publicly available Cal Pacific website was available on the web. The live site was built using a reliable combination of WordPress & WooCommerce framework; by creating identical data tables for the locally hosted database system, we were able to set up database synchronisation, to import new orders placed via the live site, automatically into the locally hosted database site; saving manual input of new orders when it came time to report financials & process customer orders. WordFence was used, as site security. This is a popular plugin available from the broader community of WordPress developers; protecting the site against malicious cyber attacks, unauthorised logins, and blocking suspicious IP addresses. Yubikey OTP Manager was installed on WordPress as an additional layer of security for Cal Pacific. This allows the use of a Yubikey to be used as a 2FA (2 factor authentication) method, without the use of any cloud-based, or smartphone based 2FA security. Yubikey as a 2FA method is consistently becoming more popular, as the OTP (one time password) can only be generated by a specific Yubikey, which itself as a physical key, cannot be compromised.

Built to be future proof.

Using the WordPress framework, meant that the only installed software required on any device accessing the database, was a web browser – nothing else.

Built to be used cross-platform; As a HTML-based database, any operating system can access and use the new database system. As well as Windows, Android & iOS operating systems, it can also be used by all open-source operating systems such as Linux Mint, Ubuntu, Tails, Fedora, ArchLinux or any unique flavour of Linux; Eliminating the dependence on a single operating system.

Built to be powerful; Unlike the previous Excel-based system, in which only a single user could access and edit data, the WordPress system can be used by any number of simultaneous users adding, updating or deleting records. The limitation of just how much data processing can be done on a locally-hosted WordPress system, is dictated by the hardware used by the local system (RAM, CPU processing power, hard drive capacity).
Web Design business in Newcastle

Is your business requiring a new Data system like Cal Pacific? Contact Us today! today!Newcastle Web Design

Get a copy of our E-book for free HERE

Web Design Newcastle

Built to be scalable and versatile.

Built to be future proof.

More In Technology News

Telegram’s New Era Merges Chat and Web: In-App Browser, Mini App Store, and More

Political Scientist Wins Injunction Against University of Oregon Officials Over Social Media Censorship

Top 7 Open Source Remote Desktop Software

Contact Data Fidelity

Take control of the internet