This article is focussed on clever database object naming from both development and SQL unit testing point of view.
This article also highlights the importance of naming database objects going through different development transitions including the SQL unit testing phase due to the agile nature of requirements.
The purpose is to understand the long term positive effect of clever naming of your database object right from the beginning and most importantly during the SQL unit testing when you are up against not so fixed business requirements.
About Naming database objects
Let us first talk about naming database objects in general.
Typical development scenario
A typical database development scenario (also known as conventional database development style) is as follows:
- You create a new database (if it does not already exists)
- You create a database object (based on internal or external business requirements)
- You write code for the database object (to meet internal or external business requirements)
- You create a SQL unit test for the database object (to ensure the object is working properly)
- You run the SQL unit test for the database object (to validate business requirements embedded in the database object)
Special development scenario
A special database development scenario (such as test-driven database development) has the following steps:
- You create a new database (if it does not already exists)
- You create a SQL unit test for a potential database object (to meet internal or external requirements)
- You run a SQL unit test for the potential database object (which must fail to comply with test-driven database development)
- You create the potential database object (to work properly to pass the unit test)
- You run the SQL unit test (to validate the internal or external requirements)
What about naming a database object
If we look at both typical and special database development scenarios it seems it does not even matter how we name the object.
The truth is, it matters a lot as you proceed further and this is what I am going to explain in this article in the context of SQL unit testing.
The time you think of the potential object (in test-driven database development) or the actual object (in conventional database development) to be created based on business or internal requirements you must think of naming the object cleverly because the database object is going to be referenced throughout its lifetime by that name unless refactoring requirement to rename the object arrives.
Introducing SQL Unit Testing Object
Please remember that I am coining a new term SQL Unit Testing Object to be interchangeably used with database object where by SQL unit testing object I particularly mean the database object which must be unit tested to validate it is functioning properly.
How you should name the object
Please consider the following things when naming your database or SQL unit testing object:
- The name of the object should not be based on the type of the object
- The name of the object should not be confusing
- The name of the object should be standardized
- The name of the object should reflect its purpose
- The name of the object should not be verbose (too long)
- The object name should be based on considering SQL unit testing in mind
The sixth point in the above list is the most important point.
Let us understand database object naming in the form of two scenarios where each scenario represents a business requirement that keeps on changing with time.
Clever naming of SQL Unit Testing Objects
Pre-requisites
There are some pre-requisites before we proceed further to understanding database object naming from development and SQL unit testing perspective.
T-SQL and TSQLt familiarity
This article assumes that the readers are well familiar with T-SQL scripting and tSQLt which is a very well-known database unit testing framework for SQL Server.
Setup sample database (Toyshop)
This article also assumes that a sample database named Toyshop has been created with the following tables:
- Toy
- ToySale
Please use the following script to setup the sample database Toyshop:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
-- Create toyshop database CREATE DATABASE Toyshop; GO -- Create Toy and ToySale Tables USE Toyshop CREATE TABLE [dbo].[Toy] ( [ToyId] INT NOT NULL, [Name] VARCHAR(40) NOT NULL, [Price] DECIMAL(10,2) NOT NULL, [Detail] VARCHAR(400) NULL, CONSTRAINT [PK_Toy] PRIMARY KEY ([ToyId]) ); GO CREATE TABLE [dbo].[ToySale] ( [ToySaleId] INT NOT NULL, [ToyId] INT NOT NULL, [Date] DATETIME2 NOT NULL, [Quantity] INT NOT NULL, [Revenue] DECIMAL(10,2) NOT NULL, CONSTRAINT [PK_ToySale] PRIMARY KEY ([ToySaleId]), CONSTRAINT [FK_ToySale_ToTable] FOREIGN KEY ([ToyId]) REFERENCES [Toy]([ToyId]) ); GO |
Setup tSQLt unit testing framework
You need to install tSQLt unit testing framework in order to follow the examples in this article. You can check
tSQLt.org to download tSQLt unit testing framework.
Please refer to Conventional SQL Unit Testing with tSQLt in Simple Words article for a better understanding on how to install tSQLt framework if you have not installed it before.
Open tSQLt.class.sql file in SSMS (SQL Server Management Studio) and Run tSQL.class.sql script against the sample database Toyshop.
Conventional SQL unit testing
We are using conventional SQL unit testing in this article in order to focus entirely on the objectives (naming SQL unit testing object rather than choosing the best unit testing methodology) although I strongly recommend test-driven database development (TDDD).
Scenario 1: First business requirement
The first case is when you receive the business requirement for the first time.
Business requirement
“The end user should be able to see a sales report for all the toys with their names which have been sold.”
Potential SQL unit testing or database object
As per conventional SQL unit testing or test-driven database development in order to meet the business requirement, you have to come up with a potential SQL unit testing object (database object) capable of meeting the business specification.
Naming SQL unit testing object
This is the time when you have to cleverly choose the name of the database or SQL unit testing object.
Hold on! Before choosing the name of SQL unit testing object do you really need to decide the type of object?
For example, is the potential object going to be SQL function or SQL stored procedure?
The key is to forget the type first and simply name your SQL unit testing object to denote its purpose only which must not give clue to its type.
We are naming the object as ToySalesReport due to the following reasons:
- The object name clearly speaks out its purpose and the purpose must map to an internal or external business or system requirement
- The object name is not bound to a specific type such as stored procedure or function which is beneficial in the long run
- The object name is easy to understand and does not require additional documentation to explain which business requirement it is intended to meet
Type of SQL unit testing object
Let us follow the principle of least privilege here in the context of choosing the type of SQL unit testing object. This means to think and ask do you really need a stored procedure here or not? The answer is No.
A stored procedure is not required because this requirement can be easily fulfilled by a simple SQL view.
Create ToySalesReport (SQL View)
|
1 2 3 4 5 6 |
-- Create ToySalesReport (view) CREATE VIEW ToySalesReport AS SELECT [s].[ToySaleId],[t].[Name], [s].[Date], [s].[Revenue] FROM ToySale s INNER JOIN Toy t on s.ToyId=s.ToyId |
The object has been created so we are going to create a SQL unit test next.
Create ToySalesReportTests class
In order to write tSQLt unit tests, we have to create a test class first.
Please create ToySalesReportTests schema (test class) as follows:
|
1 2 3 4 5 6 7 8 |
USE Toyshop GO -- Creating unit test class ArticleTests CREATE SCHEMA [ToySalesReportTests] Authorization dbo GO EXECUTE sp_addextendedproperty @name='tSQLt.TestClass',@value=1,@level0type='SCHEMA',@level0name='ToySalesReportTests' |
Create SQL unit test to check object functionality
Once the test class is created, the next step in SQL unit testing from tSQLt framework perspective is to create a unit test to check if the object is functioning properly (in conventional SQL unit testing).
Create SQL unit test to check object is functioning properly (meeting business requirement) as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
CREATE PROCEDURE [ToySalesReportTests].[test to check object shows sales report] AS -- Assemble EXEC tSQLt.FakeTable @TableName='dbo.Toy'--,@Identity=1 -- Fake Toy table EXEC tSQLt.FakeTable @TableName='dbo.ToySale'--,@Identity=1 -- Fake ToySale table INSERT INTO Toy (ToyId,Name,Price) VALUES (1,'Remote Control Car',10.00) INSERT INTO ToySale (ToySaleId,ToyId,Date,Quantity,Revenue) VALUES (1,1,'01 Jan 2018',1,10.00) Create TABLE ToySalesReportTests.Expected -- Create expected table ( [ToySaleId] INT NOT NULL, [Name] VARCHAR(40) NOT NULL, [Date] DATETIME2 NOT NULL, [Revenue] DECIMAL(10,2) NOT NULL ) INSERT INTO ToySalesReportTests.Expected -- Insert data into expected table (ToySaleId,Name,Date,Revenue) VALUES (1,'Remote Control Car','01 Jan 2018',10.00) -- Act SELECT * INTO ToySalesReportTests.Actual FROM dbo.ToySalesReport -- Run object put the records from Author table into Actual table -- Assert (compare expected table with actual table results) EXEC tSQLt.AssertEqualsTable @Expected='ToySalesReportTests.Expected',@Actual='ToySalesReportTests.Actual' |
Run SQL unit test
Run the tSQLt unit test to see the results:
|
1 2 |
-- Running tSQLt all unit tests EXEC tSQLt.RunAll |
The SQL unit test has passed.
Think of this SQL unit testing example from clever naming of potential object point of view as well.
Things are going to be clearer as we move on to the next scenario.
Scenario 2: Change in business requirement
As you know that SQL unit test written in tSQLt in the previous scenario has passed which ensures that ToySalesReport object meets the business requirement.
Let’s say after some time there is a change in business requirement and we are asked to meet the updated business requirement.
Business requirement change
“The end user should be able to see a sales report for all the toys with their names for a specified year which have been sold.”
Clever naming
This is how clever naming of the SQL unit testing object (ToySalesReport) helps because we are not going to introduce a new object with another name to meet new business requirements rather we are going to keep the same object.
Update ToySalesReport (changing the view into procedure)
We are simply going to convert the SQL view into a SQL stored procedure so that the required sales data for a specified year can be displayed as per business requirements.
Please write and run the following script to update ToySalesReport object to meet the changes in the business requirement:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Drop ToySalesReport (SQL view) DROP VIEW dbo.ToySalesReport ; GO -- Create ToySalesReport (SQL procedure) CREATE PROCEDURE dbo.ToySalesReport @SalesYear INT AS SET NOCOUNT ON SELECT [s].[ToySaleId],[t].[Name], [s].[Date], [s].[Revenue] FROM ToySale s INNER JOIN Toy t on s.ToyId=s.ToyId WHERE Year(s.Date)=@SalesYear |
Update SQL unit test (Year based reporting)
We are updating SQL unit test to meet year based reporting requirement as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
ALTER PROCEDURE [ToySalesReportTests].[test to check object shows sales report] AS -- Assemble EXEC tSQLt.FakeTable @TableName='dbo.Toy'--,@Identity=1 -- Fake Toy table EXEC tSQLt.FakeTable @TableName='dbo.ToySale'--,@Identity=1 -- Fake ToySale table INSERT INTO Toy (ToyId,Name,Price) VALUES (1,'Remote Control Car',10.00) INSERT INTO ToySale (ToySaleId,ToyId,Date,Quantity,Revenue) VALUES (1,1,'01 Jan 2018',1,10.00), (1,1,'01 Jan 2019',1,10.00) Create TABLE ToySalesReportTests.Expected -- Create expected table ( [ToySaleId] INT NOT NULL, [Name] VARCHAR(40) NOT NULL, [Date] DATETIME2 NOT NULL, [Revenue] DECIMAL(10,2) NOT NULL ) INSERT INTO ToySalesReportTests.Expected -- Insert data into expected table (ToySaleId,Name,Date,Revenue) VALUES (1,'Remote Control Car','01 Jan 2019',10.00) Create TABLE ToySalesReportTests.Actual -- Create actual table ( [ToySaleId] INT NOT NULL, [Name] VARCHAR(40) NOT NULL, [Date] DATETIME2 NOT NULL, [Revenue] DECIMAL(10,2) NOT NULL ) -- Act INSERT INTO ToySalesReportTests.Actual -- put the results into actual table EXEC dbo.ToySalesReport 2019 -- call the object with the year 2019 -- Assert (compare expected table with actual table results) EXEC tSQLt.AssertEqualsTable @Expected='ToySalesReportTests.Expected',@Actual='ToySalesReportTests.Actual' |
Run SQL unit test
We need to run the updated SQL unit test:
|
1 2 |
-- Running tSQLt all unit tests EXEC tSQLt.RunAll |
Congratulations! The SQL unit test has passed again.
We have not changed the SQL unit testing object name at all rather we updated the SQL unit test after converting the view into a stored procedure and everything seems to be working well.
This is just one simple example of clever naming of SQL unit testing object which lets us easily adapt to changing business requirements with time and if you keep your standard naming like this you can even cover slightly complicated scenarios without ever changing the names of the database or SQL unit testing objects.
Further Reading
I strongly recommend the beginners and database professionals who would like to proceed further with SQL unit testing using tSQLt to go through the following articles:
- tSQLt – A Forgotten Treasure in Database Unit Testing
- Conventional SQL Unit Testing with tSQLt in Simple Words
- Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
- 10 Most Common SQL Unit Testing Mistakes
You can also refer to the below articles, written by my fellow author, Esat Erkec:
- SQL unit testing with the tSQLt framework for beginners
- How to use fake tables in SQL unit testing?
- SQL unit testing best practices
Table of contents
He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award.
He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data.
His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).
He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential.
View all posts by Haroon Ashraf
- How to create and query the Python PostgreSQL database - August 15, 2024
- SQL Machine Learning in simple words - May 15, 2023
- MySQL Cluster in simple words - February 23, 2023