MASS Database Services

This Blog covers the Database Services that MASS offers:

 

1. Table/field/mapping updates
2. Data interfaces
3. Database views
4. Triggers / Stored Procedures
5. Archiving/Backup

 

The services we offer are for Oracle and Microsoft SQL Server

 

 

 

 

 

 

 

  

1. Table/field/mapping updates

The database schema contains the tables, fields and mappings for all the applications using the system. We can create and add additional tables and fields to suit the requirements of your system. The mapping of these tables and fields are very important, as this defines how the data is displayed in the applications. This includes the headings, data types, enumerated values and the relationships to other tables. We can supply these changes as a series of database scripts which can be run directly on your database.

 

 

2. Data interfaces

Connectivity to other data sources allows background or transactional data to be transferred into or out of other systems. The advantage of a data interface is that it saves time and provides validated data. Stored Procedures can transfer vast amounts of data quickly and efficiently in the background. Examples include importing Employee information from an Active Directory or exporting Work Request reports.

 

 

3. Database views

A database view is a virtual table that can contain data from actual tables using an SQL statement. A view contains rows and columns, just like a real table. It allows multiple tables to be linked or referenced. An example of this is where the user joins active and archived data together to allow a report to be run across both tables seamlessly. The view can also contain additional calculated fields, for example, the number of days since an active Work Request was requested. Again, we can supply these changes as a series of database scripts which can be run directly on your database.

 

 

Advantages of views

 

  • Security
Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorised to see, thus restricting the user's access to stored data.

 

  • Query Simplicity

A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view.

 

  • Structural simplicity

Views can give a user a "personalised" view of the database structure, presenting the database as a set of virtual tables that make sense for that user.

 

  • Consistency

A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.

 

  • Data Integrity

If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.

 

  • Logical data independence

A view can make the application and database tables- to a certain extent- independent. If there is no view, the application must be based on a table. With the view, the program can be established in view of the above, to view the program with a database table to be separated.

 

 

4. Triggers / Stored Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused repeatedly.

 

You can pass parameters to a stored procedure so that the stored procedure can act based on the parameter value(s) that is passed. An example of a Stored Procedure is to archive data between a specific date range.

 

The benefits of using stored procedures in SQL Server rather than application code stored locally on client computers include:

 

  • They allow modular programming.
  • They allow faster execution.
  • They can reduce network traffic.
  • They can be used as a security mechanism.


A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server. Triggers execute when a user tries to modify data through an event such as INSERT, UPDATE, or DELETE statements on a table or view. An example of a trigger is to calculate a value after an insert or update to another table, such as totalling all the costs of a Work Request. A SQL trigger is a special type of stored procedure. It is special because it is not called directly (unlike a stored procedure). The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table, whereas a stored procedure must be called explicitly.

 

The advantages of using SQL triggers:

 

  • Provide an alternative way to check the integrity of data.
  • Can catch errors in business logic in the database layer.
  • Provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked automatically before or after a change is made to the data in the tables.
  • Very useful to audit the changes of data in tables.


Triggers and Stored Procedures run directly on the database so are one of the most efficient and fastest forms of processing data.

 

We can supply these as a series of scripts that can be applied to your database.

 

5. Archiving/Backup

 

Archiving is a very important process for maintaining an efficient and fast performing database. Transactional data such as Work Requests and their associated child records should routinely be archived to historical tables to allow the applications to perform in an efficient way. The archiving process can involve either moving data from one table to another within the same database, or moving data that is no longer actively used to a separate storage device for long-term retention. The main advantage of archiving is that it improves the overall performance of your applications as there are fewer records to process.

 

Scheduling an automatic job to regularly back up your database is also a very important function for keeping your data safe.

Backing up your database, running test restore procedures on your backups, and storing copies of backups in a safe, off-site location protects you from potentially catastrophic data loss. Backing up is the only way to protect your data.

 

With valid backups of a database, you can recover your data from many failures, such as:


  • Media failure.
  • User errors, for example, dropping a table by mistake.
  • Hardware failures, for example, a damaged disc drive or permanent loss of a server.
  • Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another for testing or UAT purposes, setting up database mirroring, and archiving.

 

If you would like any more information regarding this blogs topic, please don't hesitate in contacting the MASS Technical Services Team. We are available on 0118 977 8560 or email us at news@mass-plc.com to find out more.

 

YOU CAN DOWNLOAD OUR MASS SERVICES BOOKLET HERE 

 

Andrew Taylor

 



BACK TO BLOG


What’s new in ARCHIBUS v24.1: Platform Enhancements 19/06/2019

We have finally come to the final instalment of our ‘series’ exploring what’s new in ARCHIBUS v24.1. Today in this blog we will summarise ARCHIBUS Platform Enhancements, and we’ll be cove...
read more view all blog posts

GET IN TOUCH