Professional Database Design – Abstraction Layers and More

Introduction

A properly designed database should ensure and maintain the accuracy and integrity of the data and information as well as accommodate your data processing and reporting needs by providing access to the data in useful ways. Constraints, views, function, and stored procedures are all recommended features of a professional database design because they are essential components of a good database design.

Why constraints, views, functions and stored procedures are recommended features of a professional database design

Adding constraints and abstraction layers, such as views, functions, and stored procedures to a database is part of a professional database design because they help maintain the integrity of the data and allow the underlying structure of the database to change over time with minimal disruption to how users would use the database.

Constraints

Database designers and admins use constraints to help keep the data consistent in the database. Keeping the data consistent is important because it makes the database more practical and reliable. For instance, a unique constraint on a column in a table can ensure that all rows in that column is unique and a check constraint can ensure that the values in a table are in the proper format. Having constraints increases the integrity of the database.

Views

Views are, arguably, one of the best features of relational database design. The basic purpose of a view is to present the data. Using views to store complex queries can help encapsulate complex select and join operations. Views also acts as an abstraction layer by allowing you to choose what data to show and to control access to the data.

Functions

In a way, functions are very similar to views. They both are used to save select statements. However, unlike views, function can use parameters to change the results of the query as it executes. With functions, you can return a single (scalar) value as an expression and you can use the output directly in the select clause.

Stored Procedures

Another recommended feature of a good database design is stored procedure. Stored procedures are similar to views in that they can save complex reporting SQL statements. Likewise, stored procedures are similar to functions in that they allow for filtering parameters to be included in their definition. The major difference is that stored procedures allow you to store not only select statements, but also insert, update, and delete statements as well.  

How transaction statements are used in a stored procedure

Transaction statements are used in a stored procedure to insert, update, and delete data in a database. The formal transaction statements when processing transactions are Begin, Commit, and Rollback. You begin a transaction by using Begin Transaction. A Rollback Transaction statement allow you to undo a change only if the transaction has not yet been completed – by using the Commit Transaction statement.

Figure 1

Figure 1 above shows an example of how an insert transaction statement is used in a stored procedure with the addition of try and catch blocks and error handling.

Summary

Constraints, views, functions, and stored procedures are four recommended features of a professional database design. Constraints are used to keep the data consistent. Views. functions, and stored procedures are used as abstraction layers to save and encapsulate complex select statements, restrict access to data, retrieve specific data from the database, and perform transactions.