I have an application I am building that needs to be malleable. It is a data-driven application. It will have users, contacts, organizations, and many other objects represented as a database table. One goal of this project is to allow for extension. Some customers are going to want to add a field to an object that our tables don’t include. We want to handle this end to end. It seems the perfect use of a property value table.

It would be pretty easy to create an Addendum table for each object.

dbo.Organization
dbo.OrganizationAddendum
dbo.User
dbo.UserAddendum

While that is OK, it requires additional work every time a table is created. What if a Partner writes a plugin and adds an object in the database? Well, unless the Partner creates an addendum table, this won’t really work.

Is there a way to solve this so any object in the database can have Addendum data?

I came up with this table.

CREATE TABLE [dbo].[LD_Addendum](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Table] [nvarchar](100) NOT NULL,
	[TableId] [int] NOT NULL,
	[Property] [nvarchar](255) NOT NULL,
	[Value] [nvarchar](255) NOT NULL,
	[CreateDate] [datetime2](7) NOT NULL,
	[LastUpdated] [datetime2](7) NULL,
	[CreatedBy] [int] NOT NULL,
	[LastUpdatedBy] [int] NULL,
 CONSTRAINT [PK_Addendum_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])

Then in the Web interface, I could have single template control that works for any object. Whatever object I am editing, be it user, contact, organization or other, the control would exist. If a partner adds a plugin with a new object, the control would exist. Seems easy enough, right?

The problem comes in with some of the features that we would like to be automatically handled on the database side:

1. Table should have Id column
2. Table should have four main fields
   • Table <– Database table to add addendum data for
   • TableId <– The row of in the table that addendum is for
   • Property <– The property of the addendum
   • Value <– the value of the addendum data
3. Table should have the four auditing fields in IAuditTable
   • CreateDate
   • CreatedBy
   • LastUpdated
   • LastUpdatedBy
4. Only one Property of the same name should exist per table and id. Easily done with a Unique constraint.
5. Table should have a constraint that enforces that table must exist.
   I found a way to do this: I created User-defined Function (UDF) and check constraint that uses the UDF.

   CREATE FUNCTION [dbo].[TableExists](@TableName NVARCHAR(255))
   RETURNS bit
   AS
   BEGIN
   RETURN ((SELECT COUNT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName))
   END
   

6. Table should have a constraint that enforces that TableId must exist in the Table
   1. Not supported – CLR UDFin C#? Or handle this in code?
7. The row should delete when Table is deleted. Similar to ON DELETE CASCADE.
   • Not supported – CLR UDFin C#? Or handle this in code?
8. The row should delete when a row from a table that matches Table and Id is deleted. Similar to ON DELETE CASCADE.
   • Not supported – CLR UDFin C#? Or handle this in code?

Perhaps we ignore the missing features from database side and handle them with code?

Or perhaps another database system other than Microsoft SQL Server (such as Postgresql) could do this?

Scaling

Assuming I got this to work, I see one main problem: Table size. However, I am not sure this is an issue. Tables can quite large, millions of rows. If this table got too big, then we could investigate why, analyze the table, and perhaps move a property value from the Addendum table to an actual column in a real table. This should replace the ability to create a plugin with an additional table, but it should make it so few plugins are needed as there is more extensibility out of the box.

Also, we found that default values often alleviate addendum tables. For example, imagine adding an addendum item to an organization, ContactIntervalInDays. Say a company is supposed to contact their customers every 90 days. However, some customers might require more or less contact. However, the default is 90. Instead of adding 90 to all customers, you set a default. If ContactIntervalInDays is not in the Addendum table, then use 90, otherwise use the value.

Anyway, it seems like an Addendum table is something that most projects and solutions, such as CRMs, Shopping Carts, ERPs, etc. should implement. It won’t solve the most complex issues with extending a product, but it would perhaps solve many of them. The more complex extension can continue to be added via a well-designed plugin architecture.

Unfortunately, this simplistic solution is not supported. The recommendation is to have 1 addendum table for every regular table. Ugh! That doesn’t scale and is not maintainable long term.

Still, I went ahead and requested this feature from the SQL team.