Make Your Database Self-Documenting

You've probably heard of a "self-documenting database", in this article, we'll explain what it is, why it's useful and how you can make your database self-documenting.

When you look at a database schema, you'll notice it can tell you a lot about an application.

You can see the tables, columns, data types, primary keys and foreign keys. You can inspect the triggers, indexes and stored routines. With a little knowledge of the application, you can often work out how the pieces fit together.

But there is a difference between knowing what is there and knowing why it is there.

Consider these two columns from our example webshop order database for Firebird:

/*
  Run this script on a database.
*/

/*******************************************************************************
 * Domains
 * -------
 * Extracted at 3/12/2013 1:05:19 PM
 ******************************************************************************/

CREATE DOMAIN TEXT AS 
 BLOB SUB_TYPE 1 SEGMENT SIZE 80
;
/*******************************************************************************
 * Tables
 * ------
 * Extracted at 3/12/2013 1:05:19 PM
 ******************************************************************************/

CREATE TABLE CART 
(
  CARTID                   CHAR(    38) NOT NULL,
  CUSTID                INTEGER         ,
  CARTDATE            TIMESTAMP         NOT NULL,
 CONSTRAINT PK_CART PRIMARY KEY (CARTID)
);
CREATE TABLE CART_PRODUCTS 
(
  CARTID                    CHAR(    38) NOT NULL,
  CARTLINE               INTEGER         NOT NULL,
  PRODUCTID              INTEGER         NOT NULL,
  QUANTITY               INTEGER         NOT NULL,
 CONSTRAINT PK_CART_PRODUCTS PRIMARY KEY (CARTID, CARTLINE)
);
CREATE TABLE CUSTOMERS 
(
  CUSTID                   INTEGER         NOT NULL,
  CUST_NAME                VARCHAR(   200) NOT NULL,
  CUST_EMAIL               VARCHAR(   200) NOT NULL,
  ADDRESS                  VARCHAR(   200) ,
  POSTCODE                 VARCHAR(    10) ,
  COUNTRYCODE                 CHAR(     2) ,
  PHONENUMBER              VARCHAR(    20) ,
  REGDATE                TIMESTAMP,
 CONSTRAINT PK_CUSTOMERS PRIMARY KEY (CUSTID)
);
CREATE TABLE ORDERS 
(
  ORDERID                             INTEGER         NOT NULL,
  CUSTID                              INTEGER         NOT NULL,
  ORDER_DATE                        TIMESTAMP         NOT NULL,
  EXPECTED_DELIVERY_DATE                 DATE,
  CUSTOMER_NOTES                         TEXT ,
 CONSTRAINT PK_ORDERS PRIMARY KEY (ORDERID)
);
CREATE TABLE ORDER_LINES 
(
  ORDERID                       INTEGER         NOT NULL,
  ORDERLINEID                  SMALLINT         NOT NULL,
  PRODUCTID                     INTEGER,
  QUANTITY                     SMALLINT         NOT NULL,
  PRICE_PER_ITEM                DECIMAL( 10, 2),
  ITEM_DESCRIPTION              VARCHAR(   200) NOT NULL,
 CONSTRAINT PK_ORDER_LINES PRIMARY KEY (ORDERID, ORDERLINEID)
);
CREATE TABLE PRODUCTS 
(
  PRODID                  INTEGER         NOT NULL,
  PROD_NAME               VARCHAR(   200) NOT NULL,
  PROD_IMAGE                 BLOB SUB_TYPE 0 SEGMENT SIZE 80,
  PRICE                   DECIMAL( 10, 2),
  WEIGHT                  DECIMAL( 10, 2),
 CONSTRAINT PK_PRODUCTS PRIMARY KEY (PRODID)
);
/*******************************************************************************
 * Foreign Key Constraints
 * -----------------------
 * Extracted at 3/12/2013 1:05:19 PM
 ******************************************************************************/

ALTER TABLE CART ADD CONSTRAINT FK_CART_CUSTOMERS 
  FOREIGN KEY (CUSTID) REFERENCES CUSTOMERS
  (CUSTID) 
  ON DELETE CASCADE
  ON UPDATE NO ACTION
;

ALTER TABLE CART_PRODUCTS ADD CONSTRAINT FK_CART_PRODUCTS_PRODUCTS 
  FOREIGN KEY (PRODUCTID) REFERENCES PRODUCTS
  (PRODID) 
  ON DELETE CASCADE
  ON UPDATE NO ACTION
;

ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_CUSTOMERS 
  FOREIGN KEY (CUSTID) REFERENCES CUSTOMERS
  (CUSTID) 
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
;

ALTER TABLE ORDER_LINES ADD CONSTRAINT FK_ORDER_LINES_ORDERS 
  FOREIGN KEY (ORDERID) REFERENCES ORDERS
  (ORDERID) 
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
;

ALTER TABLE ORDER_LINES ADD CONSTRAINT FK_ORDER_LINES_PRODUCTS 
  FOREIGN KEY (PRODUCTID) REFERENCES PRODUCTS
  (PRODID) 
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
;

PRODUCTS.PRICE
ORDER_LINES.PRICE_PER_ITEM

Both contain a price. Why are there two prices? The database structure doesn't tell us.

Perhaps they are redundant. Perhaps one is obsolete. Perhaps they have subtly different meanings.

Or perhaps the second price is there for a very good reason: PRODUCTS.PRICE is the current selling price, while ORDER_LINES.PRICE_PER_ITEM is the price that was actually charged when the order was placed.

That distinction is important. It is also invisible from the column definitions.

This is where database documentation becomes more than a separate document or data dictionary. A database can carry descriptions of its own objects, making the database itself a source of information about its design.

In other words, we can make the database self-documenting.

The problem with documentation outside the database

Most development teams have some form of database documentation.

It might be a Word document, a spreadsheet, an internal wiki, a diagram, or a collection of design notes.

These documents can be useful, but there is an obvious problem: they are separate from the database.

Imagine that someone creates this table:

CREATE TABLE ORDER_LINES
(
    ORDERID           INTEGER NOT NULL,
    ORDERLINEID       SMALLINT NOT NULL,
    PRODUCTID         INTEGER,
    QUANTITY          SMALLINT NOT NULL,
    PRICE_PER_ITEM    DECIMAL(10, 2),
    ITEM_DESCRIPTION  VARCHAR(200) NOT NULL,
    CONSTRAINT PK_ORDER_LINES PRIMARY KEY (ORDERID, ORDERLINEID)
);

The table tells us the data type of PRICE_PER_ITEM.

It doesn't tell us why that price is stored here rather than retrieved from PRODUCTS.

A developer joining the project several years later has to find the answer somewhere else.

Perhaps it is in the documentation. Perhaps it is in the application source code. Perhaps it is in an old design document.

Or perhaps nobody remembers.

That last possibility is particularly common with databases that have been around for a long time.

The database itself, however, is still there. So why not put the explanation there too?

Adding meaning to the schema

Different database systems provide different ways to attach descriptions to database objects. For the examples in this article, we'll use Firebird and its COMMENT ON statement.

For example:

COMMENT ON TABLE PRODUCTS IS
'Products available for sale.';

And:

COMMENT ON COLUMN PRODUCTS.PRICE IS
'Current selling price of the product.';

This is useful, but the real value becomes apparent when the description explains something that cannot be inferred from the name.

For example:

COMMENT ON COLUMN PRODUCTS.PRICE IS
'Current selling price of the product. Historical order prices are
stored separately in ORDER_LINES.PRICE_PER_ITEM.';

COMMENT ON COLUMN ORDER_LINES.PRICE_PER_ITEM IS
'Unit price charged when the order was placed. This is a historical
snapshot and must not be replaced by the current PRODUCTS.PRICE.';

Now the database explains itself.

A developer looking at ORDER_LINES.PRICE_PER_ITEM no longer has to guess why the column exists.

The schema is telling them:

• PRODUCTS.PRICE represents the current price.
• ORDER_LINES.PRICE_PER_ITEM represents the historical price.
• The historical value must remain unchanged when the product price changes.

That is much more useful than simply documenting that PRICE_PER_ITEM is "the price per item."

Why the distinction matters

Suppose a product costs €10 when a customer places an order.

The order line stores:

PRICE_PER_ITEM = 10.00

Later, the product price increases to €12.

The product table now contains:

PRODUCTS.PRICE = 12.00

But the old order still needs to show:

ORDER_LINES.PRICE_PER_ITEM = 10.00

Otherwise, the historical order would suddenly appear to have been charged €12.

This is a common database design pattern: the database deliberately stores a snapshot of information because the current value may change later.

The column name alone doesn't explain that. The comment does.

That is exactly the kind of information we should put into database metadata.

Document the things that are not obvious

This suggests an important rule for database documentation:

Don't document what the database already tells you. Document what you would otherwise have to ask someone.

For example, this is not particularly useful:

COMMENT ON COLUMN PRODUCTS.PRICE IS
'Product price.';

The column is already called PRICE.

A better description explains its role:

COMMENT ON COLUMN PRODUCTS.PRICE IS
'Current selling price of the product. Historical order prices are
stored separately in ORDER_LINES.PRICE_PER_ITEM.';

Likewise, this:

COMMENT ON COLUMN ORDER_LINES.PRICE_PER_ITEM IS
'Price per item.';

adds very little.

This is considerably more useful:

COMMENT ON COLUMN ORDER_LINES.PRICE_PER_ITEM IS
'Unit price charged when the order was placed. This is a historical
snapshot and must not be replaced by the current PRODUCTS.PRICE.';

The second description captures business meaning, not just technical meaning.

Tables can explain themselves too

The same principle applies to tables.

Consider:

CREATE TABLE CART_PRODUCTS
(
    CARTID      CHAR(38) NOT NULL,
    CARTLINE    INTEGER NOT NULL,
    PRODUCTID   INTEGER NOT NULL,
    QUANTITY    INTEGER NOT NULL,
    CONSTRAINT PK_CART_PRODUCTS PRIMARY KEY (CARTID, CARTLINE)
);

The name tells us this table has something to do with products in carts.

But a description can tell us more:

COMMENT ON TABLE CART_PRODUCTS IS
'Products currently contained in a shopping cart. Each row represents
one product line.';

We can also document a less obvious column:

COMMENT ON COLUMN CART_PRODUCTS.CARTLINE IS
'Display position of the product within the cart. The value is unique
only within a cart.';

The primary key already tells us that (CARTID, CARTLINE) is unique.

The comment explains the meaning of that uniqueness.

Again, the goal isn't to repeat the schema. It is to preserve knowledge about the schema.

Historical data is particularly worth documenting

The order example illustrates another useful area for documentation: historical data.

Suppose we have:

COMMENT ON COLUMN ORDER_LINES.ITEM_DESCRIPTION IS
'Product description captured when the order was placed. It is retained
so historical orders continue to show the description presented to the
customer, even if the product is subsequently renamed.';

Why is this valuable?

Because someone examining the database might reasonably wonder why an order line contains a product description when the product table already has PROD_NAME.

The answer is that they represent different things.

PRODUCTS.PROD_NAME is the current product name.

ORDER_LINES.ITEM_DESCRIPTION is the historical description.

The duplication is intentional.

The comment makes that clear.

Without it, someone unfamiliar with the application might conclude that the column is redundant.

Document units and conventions

Another good use for comments is information that the SQL data type cannot express.

For example:

WEIGHT DECIMAL(10, 2)

What does that number mean?

Kilograms? Grams? Pounds?

Does it include packaging?

The data type cannot tell us.

A description can:

COMMENT ON COLUMN PRODUCTS.WEIGHT IS
'Product shipping weight in kilograms, excluding packaging.';

Now an otherwise ambiguous number has a precise meaning.

This is particularly useful when dealing with measurements, codes, statuses, percentages, monetary values and dates.

The database may know that something is a DECIMAL, INTEGER or VARCHAR.

It doesn't necessarily know the business convention represented by that value.

Domains are another opportunity

Reusable database types are also good candidates for documentation.

The example database contains a Firebird domain:

CREATE DOMAIN TEXT AS
BLOB SUB_TYPE 1 SEGMENT SIZE 80;

We could describe why this domain exists:

COMMENT ON DOMAIN TEXT IS
'Long textual content stored as a text BLOB. Used for multi-line
user-entered text that is not limited to VARCHAR length.';

The domain now communicates more than its technical definition.

This becomes even more valuable with domains representing business concepts.

For example:

CREATE DOMAIN COUNTRY_CODE AS CHAR(2);

could be accompanied by:

COMMENT ON DOMAIN COUNTRY_CODE IS
'Two-character ISO 3166-1 alpha-2 country code.';

Anyone inspecting the domain immediately knows the convention being used.

Triggers are especially good candidates

Triggers can be difficult to understand simply by looking at their names.

Imagine:

CREATE TRIGGER TR_ORDER_LINES_BI
FOR ORDER_LINES
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    /* implementation */
END

The trigger name tells us very little about its purpose.

A comment can explain why it exists:

COMMENT ON TRIGGER TR_ORDER_LINES_BI IS
'Captures the historical product price and description when a new
order line is created.';

Now someone investigating the database knows that the trigger is related to the historical information stored on the order line.

They can still inspect the trigger source code to see exactly how it works, but they don't have to reverse-engineer its purpose first.

It can also be useful to mention important trigger behaviour in the table description itself. For example:

COMMENT ON TABLE ORDER_LINES IS
'Items belonging to an order. The PRICE_PER_ITEM and ITEM_DESCRIPTION
columns contain historical values captured when the order was created.
A BEFORE INSERT trigger populates these values from the product.';

This is particularly useful when the trigger implements behaviour that is important for understanding how the table should be used.

The table description gives someone looking at the table an immediate overview, while the trigger description can explain the specific trigger in more detail.

Self-documentation is not about writing more comments

It is tempting to interpret the idea of a self-documenting database as "put a comment on everything."

That isn't the point.

A database with hundreds of comments saying things like:

'Customer ID'
'Customer name'
'Product price'
'Order date'

isn't necessarily better documented.

Those comments simply repeat information that is already obvious.

Good database documentation captures information that would otherwise be difficult to discover.

For example:

COMMENT ON COLUMN ORDERS.EXPECTED_DELIVERY_DATE IS
'Delivery date currently promised to the customer. This is an estimate
and may change during fulfilment.';

The name tells us that it is an expected delivery date.

The comment tells us that it is:

• a promise to the customer;
• an estimate;
• and something that may change.

That is useful.

The database as a data dictionary

Many organisations maintain a separate data dictionary containing information such as:

There is nothing wrong with maintaining such a document.

It may be necessary for business users or for formal documentation.

But the database itself already knows much of the information in that document.

It knows the tables. It knows the columns. It knows the data types. It knows the relationships.

And with object descriptions, it can also know the explanations.

This makes the database a kind of living data dictionary.

More importantly, the documentation is attached to the object it describes. If you inspect the database, the explanation is there.

The important part is the "why"

A database schema is already a form of documentation.

A primary key tells us something about identity. A foreign key tells us something about relationships. A NOT NULL constraint tells us something about required data. A data type tells us something about the shape of a value.

But there are many things that the schema cannot express so easily.

Why is a value stored? Why is it duplicated? What unit does it use? Is it current or historical? What business rule does a trigger implement?

These are the questions that database descriptions can answer.

That is why the most useful database comments often explain why, rather than what.

Conclusion

A well-designed database doesn't just store data. It captures part of the application's knowledge.

Database object descriptions provide a simple way to preserve some of that knowledge alongside the schema itself. The most valuable descriptions explain the things that cannot be understood from table and column definitions alone: business rules, historical values, units, conventions, and the reasons behind seemingly unusual design decisions.

The goal isn't to document every object or repeat what the schema already tells us.

It is to make the database easier to understand for the next developer who has to work with it.

Put the explanation as close as possible to the thing it explains.

And of course, Database Workbench allows you to enter object descriptions with ease, make sure to check it out.