Introduction

As part of the CQRS in Azure MSDN blog series, a ledger style table was created to manage an inventory.  The concept was to provide an insert optimized table that prevented the inventory level to drop below zero.  This approach is referred to as Insert Only and is common in accounting solutions.

The source can be found on MSDN Samples: here.

SQL Schema

The scenario involves managing the inventory of a pie shop.  For this there are three tables:
  • Ingredients - Maintains the ingredients used in making pies
  • Inventories - Maintains the different locations where ingredients are stored
  • InventoryEntries - Maintains the ingredients at a particular inventory location

Insert Only Table

Insert only tables are popular in accounting or ledger solutions as it supports both auditing and history by preventing updates or delete operations.  The advantage is access can be optimized for write operation and a clear history of all activity is captured.  The disadvantage is the final state is determined by performing a full read of the table and often requires another persistent view or table to provide aggregated information.  

Note: Blockchain implementations use insert only tables to guarantee state among distributed repositories.

In relation to the pie shop, the InventoryEntries table will contain both increases and decreases to an inventory.  For example, imagine WarehouseA received three shipments and sent one shipment.  This would be recorded in the table as follows:
WarehouseA    Floor      3   
WarehouseA   Floor    2  
WarehouseA  Floor  -4   
WarehouseA  Floor    2  

The end result would be WarehouseA has 3 amounts of Floor (4+2-4+2).

DB Migrations

The initial DBMigration creates the schema including a constraint to prevent the inventory from ever dropping below 0 and a function for determining the current quantity.
  
The creation of the three tables is shown below:
CreateTable(
    "ledger.Ingredients",
    c => new
        {
            IngredientId = c.Int(nullable: false, identity: true),
            Name = c.String(),
        })
    .PrimaryKey(t => t.IngredientId);
             
CreateTable(
    "ledger.Inventories",
    c => new
        {
            InventoryId = c.Int(nullable: false, identity: true),
            Name = c.String(),
        })
    .PrimaryKey(t => t.InventoryId);
             
CreateTable(
    "ledger.InventoryEntries",
    c => new
        {
            InventoryEntryId = c.Int(nullable: false, identity: true),
            InventoryId = c.Int(nullable: false),
            IngredientId = c.Int(nullable: false),
            Quantity = c.Int(nullable: false),
        })
    .PrimaryKey(t => t.InventoryEntryId)
    .ForeignKey("ledger.Ingredients", t => t.IngredientId, cascadeDelete: true)
    .ForeignKey("ledger.Inventories", t => t.InventoryId, cascadeDelete: true)
    .Index(t => t.InventoryId)
    .Index(t => t.IngredientId);

The function was created using the DBMigration.Sql() method and returns the sum of an ingredient at a specific inventory location:
Sql("CREATE FUNCTION [ledger].[fn_CurrentQuantity](@InventoryId int, @IngredientId int) " +
    "RETURNS int AS BEGIN " +
    "DECLARE @ResultVar int " +
    "SELECT @ResultVar = isnull(SUM(Quantity), 0) FROM InventoryEntries where @InventoryId = InventoryId and @IngredientId = IngredientId " +
    "RETURN @ResultVar END ");

The constraint is placed against the table and prevents the inventory from dropping below 0:
Sql("ALTER TABLE [ledger].[InventoryEntries] ADD CONSTRAINT CK_Quantity CHECK (([ledger].[fn_CurrentQuantity]([InventoryId],[IngredientId]))>=(0) and Quantity != 0)");

Summary

This wiki illustrates an example of adding constraints and functions to Entity Framework database migrations.