Documenting a Database: Tell the Story of each Table - A Proposal

I have often dealt with existing database system with poor to non-existent documentation.  I considered myself lucky when meta-data about the database had been supplied!  The absence of documentation made its modifications, as well as changes to the middle-tier code a slow-going process since I had to reason about the database largely on my own, trying to understand how to navigate its key structures as well as the Business Process that it modeled.

To improve this common situation, I think it will be a good idea to approach the documentation from the point of view of storytelling; that is, document the Story or Stories that the data tells.  In a previous post, I proposed telling the Story of Data using such tools as Dramatica.

In this post, I am suggesting something less ambitious: write a single paragraph - a story - that describes the relationships among the attributes of that table and how they support a Business Process (how they help run the business).

My expectation would be that knowledgeable business owners would work with IT to produce this sort of documentation.

For example, consider an "Order" table that tracks all the orders placed in a business enterprise.  This table could have the following attributes:

• OrderID (PK)
• CustomerID (FK → Customer.CustomerID)
• OrderStatus (e.g., "Pending", "Processing", "Shipped", "Delivered", "Cancelled")
• OrderDate
• TotalAmount
• ShippingMethod
• ShippingCost
• TrackingNumber
• EstimatedDeliveryDate
• CreatedAt
• UpdatedAt

A possible story could be:

"A human being whom we think of as our Customer - here identified by his CustomerID - has placed a sales order for an item - identified by an OrderID, on the date identified by OrderDate, which is in the fulfillment status identified by "OrderStatus".  Furthermore, the shipping method and shipping costs are identified by ShippingMethod and ShippingCosts fields and the estimated delivery date is stored in the EstimatedDeliveryDate.  Total order amount is TotalAmount field and the actual cost of the order minus the shipping costs is an inferred quantity given by (TotalAmount minus ShippingCost)."

Another possible story could be:

"The shipping method identified by ShippingMethod attributes is used to ship the order identified by OrderID, which has the actual cost of (TotalAmount minus ShippingCost) is used when the OrderStatus has the  value of  "Shipped"."

Depending one one's preferences, one or another of the attributes could be made to be the central character of the story - its main protagonist.