Create Document Templates in a SQL Server Database Table

By: Sebastiao Pereira   |   Updated: 2024-07-08   |   Comments (2)   |   Related: > TSQL

Problem

In some industries, there is a series of repeated content stored about a specific topic, i.e., contracts, terms and conditions, legal agreements, lease agreements, bills, etc., with only minor differences. With these documents, there is a need to just replace specific keywords. What is the most efficient way to maintain consistency, data accuracy, and optimize storage?

Solution

Using templates to standardize content is a good way to enhance the quality, consistency, and conciseness of the information, as well as boost productivity, simplify repeated tasks, save time, and reduce the amount of storage required.

Standardized content is commonly used in many areas, including:

• Business Contracts Templates: "This service contract between the Service Provider [PROVIDER_NAME] and Client [CLIENT_NAME], …."
• Terms and Conditions Templates: "[WEBSITE_NAME] Terms and Conditions (these "Terms" or these "Terms and Conditions") contained in this Agreement shall govern…"
• Social Media Policies: "All official use of social media on behalf of [ORGANIZATION_NAME] must be approved by [APPROPRIATE_PERSON] prior to …"
• Land Lease Agreement Templates: "[LANDLORD_NAME], with an address of [LANDLORD_ADDRESS], hereinafter referred as the…"
• as well as other areas like Legal Agreements and Bills.

Example Use Case

To demonstrate the application of templates in SQL Server, I opted to showcase a significant topic within the healthcare domain: drug interactions. This phenomenon refers to the reaction between two or more drugs, potentially altering how the drug works or causing unwanted reactions. Prior to prescribing new medication, it's imperative for medical practitioners to ascertain the patient's current medication regimen to anticipate any significant drug interactions.

For this example, let's focus on how to use content templates in a pharmacological context. The drug label, issued by the Federal Drug Administration (FDA in the United States), reserves the item 7 to describe DRUG INTERACTIONS. For example, in the case of IBUPROFEN, there is a statement that says:

• "The concomitant use of ibuprofen and anticoagulants have an increased risk of serious bleeding."

So, if we create tables to store the drug interactions, we should repeat this sentence for each anticoagulant and ibuprofen. We can standardize this sentence as:

• "The concomitant use of {0} and {1} have an increased risk of serious bleeding."

And, if we need to, we can have templates in another language to translate this sentence. For example, to Brazilian Portuguese (pt-BR):

• "O uso concomitante de {0} e {1} aumenta o risco de sangramento grave"

Why should we do this extra work?

In the real world, if we wrote a database using the contents as it is, 1.4 million records of drug interactions will be stored in 564 MB, and if we do the same, using content templates, we can save the same information in only 33 MB. Also, in this database, there are 31,416 records that use this template ("The concomitant use of {0} and {1} have an increased risk of serious bleeding."). Suppose that, for any reason, we have to change the sentence to "The risk or severity of bleeding can be increased when {0} is combined with {1}." We need to update only one record.

Let's see an example of how to do it.

Creating the Tables

The first step is to store the drugs. I will assign integer values for each drug identification (DrugId). There are a lot of official identifiers that can be used here. For example, one identifier could be the CAS number, a unique identification number assigned by the Chemical Abstracts Service (CAS) in the United States to every chemical substance described in the open scientific literature.

CREATE TABLE [dbo].[Drugs](
   [DrugId] [int] NOT NULL,
   [DrugLanguage] [char](5) NOT NULL,
   [DrugName] [nvarchar](300) NULL,
 CONSTRAINT [PK_Drugs] PRIMARY KEY CLUSTERED 
(
   [DrugId] ASC,
   [DrugLanguage] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[Drugs] ADD  CONSTRAINT [DF_Drugs_DrugLanguage]  DEFAULT ('en-US') FOR [DrugLanguage]
GO

The second table is to store the standard content for drug interactions:

CREATE TABLE [dbo].[InteractionsStd](
   [InteractionStdId] [int] NOT NULL,
   [InteractionStdLang] [char](5) NOT NULL,
   [InteractionStd] [nvarchar](max) NULL,
 CONSTRAINT [PK_InteractionsStd] PRIMARY KEY CLUSTERED 
(
   [InteractionStdId] ASC,
   [InteractionStdLang] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[InteractionsStd] ADD  CONSTRAINT [DF_InteractionsStd_InteractionStdLang]  DEFAULT ('en-US') FOR [InteractionStdLang]
GO

And the final and principal table is to store the drug interactions:

CREATE TABLE [dbo].[InteractionsDrug](
   [InteractionId] [int] IDENTITY(1,1) NOT NULL,
   [drugId1] [int] NOT NULL,
   [drugId2] [int] NOT NULL,
   [InteractionStdId] [int] NOT NULL,
 CONSTRAINT [PK_InteractionsDrug] PRIMARY KEY CLUSTERED 
(
   [InteractionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Creating a View

This view uses the template for drug interaction and replaces the drugs according to the language specified.

CREATE VIEW [dbo].[vDrugInteractions]
AS
SELECT Drug.InteractionId
      ,Drug.drugId1
      ,Drug.drugId2
      ,Drug.InteractionStdId
      ,Drugs1.DrugName AS Drug0
      ,Drugs2.DrugName AS Drug1
      ,Std.InteractionStdLang AS Lang
      ,REPLACE(REPLACE(Std.InteractionStd, '{0}',Drugs1.DrugName), '{1}', Drugs2.DrugName) AS DrugInteraction
   FROM dbo.Drugs AS Drugs2 INNER JOIN
       dbo.InteractionsStd AS Std INNER JOIN
       dbo.InteractionsDrug AS Drug ON Std.InteractionStdId = Drug.InteractionStdId ON Drugs2.DrugId = Drug.drugId2 AND Drugs2.DrugLanguage = Std.InteractionStdLang INNER JOIN
       dbo.Drugs AS Drugs1 ON Drug.drugId1 = Drugs1.DrugId AND Std.InteractionStdLang = Drugs1.DrugLanguage
GO

Inserting Some Data

For the drugs table:

INSERT INTO [dbo].[Drugs] VALUES (1,'en-US','Ibuprofen');
INSERT INTO [dbo].[Drugs] VALUES (1,'pt-BR','Ibuprofeno');
INSERT INTO [dbo].[Drugs] VALUES (2,'en-US','Warfarin');
INSERT INTO [dbo].[Drugs] VALUES (2,'pt-BR','Varfarina');
INSERT INTO [dbo].[Drugs] VALUES (3,'en-US','Apixaban');
INSERT INTO [dbo].[Drugs] VALUES (3,'pt-BR','Apixabano');
INSERT INTO [dbo].[Drugs] VALUES (4,'en-US','Methotrexate');
INSERT INTO [dbo].[Drugs] VALUES (4,'pt-BR','Metrotexato');

For the standard interactions table, we have to extract the interactions mentioned in item 7 of the FDA label for ibuprofen, replacing the name of each drug with the numbers surrounded by curly brackets, {0} and {1}, and also include the first one for cases with no drug interactions found:

INSERT INTO [dbo].[InteractionsStd] VALUES (0,'en-US','There is no record in our database of drug interaction between {0} and {1}');
INSERT INTO [dbo].[InteractionsStd] VALUES (0,'pt-BR','Não há registro no nosso banco de dados de interação medicamentosa entre {0} e {1}');
INSERT INTO [dbo].[InteractionsStd] VALUES (1,'en-US','The concomitant use of {0} and {1} have an increased risk of serious bleeding');
INSERT INTO [dbo].[InteractionsStd] VALUES (1,'pt-BR','O uso concomitante de {0} e {1} aumenta o risco de sangramento grave');
INSERT INTO [dbo].[InteractionsStd] VALUES (2,'en-US','Concomitant use of {0} and {1} may increase the risk for {1} toxicity');
INSERT INTO [dbo].[InteractionsStd] VALUES (2,'pt-BR','O uso concomitante de {0} e {1} pode aumentar o risco de toxicidade de {1}');

Now, we need to insert the real data for drug interactions for Ibuprofen (DrugId 1), and once both for Warfarin (DrugId 2) and Apixaban (DrugId 3) which are anticoagulants, both inherit the InteractionStdId 1. For the Methotrexate, this one has its own InteractionStdId 2. Check out the code below:

INSERT INTO  [dbo].[InteractionsDrug]
       ([drugId1]
      ,[drugId2]
      ,[InteractionStdId])
     VALUES (1,2,1)
      ,(1,3,1)
      ,(1,4,2);
GO

Drug Interactions Stored Procedure

To verify if two drugs have drug interactions recorded, use the following store procedure:

/****** Object:  StoredProcedure [dbo].[uspDrugInteractionsChk]    Script Date: 15/05/2024 11:19:09 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
-- =============================================
-- Author:      SCP - mssqltips.com
-- Create date: 20240515
-- Description: Drug Interactions Check
-- =============================================
CREATE PROCEDURE [dbo].[uspDrugInteractionsChk] 
             @Drug1 nvarchar(300)
            ,@Drug2 nvarchar(300)
AS
BEGIN
   SET NOCOUNT ON;
 
   DECLARE @DrugId1 int = 
      (SELECT  [DrugId]
         FROM  [dbo].[Drugs]
         WHERE  [DrugName] = @Drug1);
 
   DECLARE @DrugId2 int = 
      (SELECT  [DrugId]
         FROM  [dbo].[Drugs]
         WHERE  [DrugName] = @Drug2);
 
   IF EXISTS 
      (SELECT  1
         FROM  [dbo].[InteractionsDrug]
         WHERE ([drugId1] = @DrugId1 AND
             [drugId2] = @DrugId2) OR
            ([drugId1] = @DrugId2 AND
             [drugId2] = @DrugId1))
      SELECT     [Language]
            ,[DrugInteraction]
         FROM  [dbo].[vDrugInteractions]
         WHERE ([drugId1] = @DrugId1 AND
             [drugId2] = @DrugId2) OR
            ([drugId1] = @DrugId2 AND
             [drugId2] = @DrugId1);
   ELSE
      SELECT     [InteractionStdLang] AS [Language]
            ,REPLACE(REPLACE([InteractionStd],'{0}',@Drug1),'{1}',@Drug2) AS DrugInteraction
         FROM  [dbo].[InteractionsStd]
         WHERE  [InteractionStdId] = 0;
END
GO

Checking Drug Interactions

Let's review the data for a patient who takes Ibuprofen routinely.

Example 1

If the doctor is planning to prescribe Methotrexate, the return message is:

Example 2

If the doctor is planning to prescribe Warfarin, the return message is:

Example 3

If the doctor is planning to prescribe Tocopherol (Vitamin E), the return message is:

Next Steps

• If you have a plan to create a template for repeated content, I suggest you use these rules as a guideline:
  • Identify key components.
  • Keep it flexible to adapt to different scenarios or contexts.
  • Maintain consistency.
  • Focus on clarity and readability and consider reusability.
  • Test and iterate.
  • Document and organize your template.
  • Consider the user's perspective and needs.
• The most important thing about using templates is to check if its intended purpose is effective for its intended audience.

About the author

Sebastiao Pereira has over 38 years of healthcare experience, including software development expertise with databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips