Being a Good DNN Citizen - Referencing Database Tables

For quite a while now I have written about development best practices, methods to improve the performance of developed solutions, and a little about how to be a good citizen with other portions of the application. In this post, I will take a look at a few standard situations that I have encountered when working with developers new to the DNN Platform.

The Standard Error

As developers working with the DNN Platform we often find ourselves needing to reference other DNN tables. Maybe we need to store information for a particular Tab, Module, or even portal. The first pass SQL that I see from people all looks similar.

CREATE TABLE dbo.MyCustomTable(
    ItemId INT IDENTITY(1,1) PRIMARY KEY,
    ModuleId INT NOT NULL CONSTRAINT [FK_MyTable_Modules]
        FOREIGN KEY REFERENCES Modules (ModuleId),
    MyOtherColumn VARCHAR(MAX)
)

On the surface, this looks just fine! Ask your average SQL Server DBA and they will also agree that you are right on target. However, this introduces a HUGE problem in DNN.

The Problem

Using the above table as a reference we will find that our solution will work just fine for the first part of any testing. Users will be able to use the module, add it to pages, add content and even delete it from the page. This will all happen without any indication of a failure. However, try to then empty the recycle bin and you will get into a situation. For some reason, your module will not go away and an error will be logged to the DNN Event Viewer. Oh no! Now we have broken functionality within DNN. The exact severity of this issue will depend on which table you referenced.

The Fix & Why

This is an issue because the DNN system is not aware of your custom table when it is able to delete things. To properly integrate with DNN you must on any Foreign Key relationship additionally specify that on delete we must cascade deletions. This will allow DNN to properly remove our tables. We can modify our existing solution, slightly for proper use. The below sample shows the "best" way to handle it.

CREATE TABLE dbo.MyCustomTable(
    ItemId INT IDENTITY(1,1) PRIMARY KEY,
    ModuleId INT NOT NULL CONSTRAINT [FK_MyTable_Modules]
        FOREIGN KEY REFERENCES Modules (ModuleId) ON DELETE CASCADE,
    MyOtherColumn VARCHAR(MAX)
)

Wrap Up

Although not what your DBA might want to hear as many don't like the cascading deletion, this is the best way to integrate with DNN to ensure minimal impact. Hope this helps!