Database Documentation Standards: Mastering SQL Comments

Previously in this course, we covered establishing naming conventions to ensure our tables and columns follow a consistent, readable structure. While good names are a great first step, they rarely capture the full intent of a business rule or the specific format of a data field.

In this lesson, we move from structural naming to active documentation. We will use PostgreSQL’s built-in metadata system to ensure that anyone querying your database—or your future self—understands exactly what each component represents.

Why Database Documentation Matters

In complex systems, the database schema is the source of truth. However, raw SQL rarely explains why a column exists or what unit of measurement it uses.

Without documentation, you rely on external spreadsheets or wiki pages that inevitably fall out of sync with your actual code. By using SQL comments directly within the database, you keep the definition and its explanation in the same place. This improves maintainability, as the database itself acts as its own manual.

Using COMMENT ON in PostgreSQL

PostgreSQL provides the COMMENT ON command, which allows you to attach text strings to database objects. This metadata is stored in the system catalogs and can be retrieved by database clients like pgAdmin or by running specific metadata queries.

Documenting Tables

Table-level comments should explain the business purpose of the table. Think of this as the "summary" that appears in a data dictionary.

SQL
COMMENT ON TABLE customers IS 'Stores individual user profile information and contact details for the store application.';

Documenting Columns

Column-level comments are even more critical. Use them to clarify data types, units (e.g., USD vs. EUR), or constraints that aren't immediately obvious from the column name alone.

SQL
COMMENT ON COLUMN customers.email IS 'The primary contact email address. Must be unique for account login.';
COMMENT ON COLUMN customers.created_at IS 'Timestamp in UTC indicating when the customer record was first created.';

Worked Example: Documenting the Store Schema

Let’s apply this to our running project. We have a products table; let’s document it properly to ensure clarity for other developers joining the project.

SQL
-- First, document the table itself
COMMENT ON TABLE products IS 'Inventory items available for purchase in the store.';

-- Then, document individual columns
COMMENT ON COLUMN products.id IS 'Primary key, using unique identifier for stock keeping.';
COMMENT ON COLUMN products.price IS 'Unit price of the product in USD, stored as a decimal to avoid rounding errors.';
COMMENT ON COLUMN products.stock_quantity IS 'Current inventory count. Must be non-negative.';

To verify that your comments were applied correctly, you can query the pg_description system table, or simply check the "Properties" or "Comments" tab in your GUI tool (like pgAdmin).

Hands-on Exercise

Open your terminal or pgAdmin tool and perform the following steps on your orders table:

1. Add a table-level comment to your orders table explaining that it tracks customer transactions.
2. Add a column-level comment to your order_date column specifying that it records the transaction time in UTC.
3. Verify your work by inspecting the table schema in your interface.

Hint: Remember that you can always use the \d+ command in psql to view these descriptions directly in your terminal.

Common Pitfalls

• Writing "Code" in Comments: Don't document the obvious. COMMENT ON COLUMN users.id IS 'The ID of the user' is noise. Use comments to explain the business logic or constraints that the database engine doesn't enforce on its own.
• Forgetting to Update: When you ALTER TABLE to add or change a column, update the comment as well. Documentation that contradicts the schema is worse than no documentation at all.
• Length Constraints: While PostgreSQL allows long strings for comments, keep them concise and readable. A paragraph should be the absolute maximum; usually, one or two sentences suffice.

FAQ

Are these comments visible in my application code? No. These comments exist only within the PostgreSQL database metadata. They are primarily for developers and DBAs using SQL clients.

Can I use these for API documentation? Some tools can extract these comments to auto-generate API documentation or data dictionaries, which is a massive productivity booster in professional environments—similar to the professional documentation standards we see in other software layers.

How do I remove a comment? You can remove a comment by setting it to NULL: COMMENT ON TABLE products IS NULL;

Recap

Database documentation is the bridge between a functional schema and a maintainable system. By using COMMENT ON, you embed business context directly into your PostgreSQL environment. This habit reduces onboarding time for new team members and prevents the "mystery column" phenomenon where no one remembers why a field exists.

Up next: We will perform a comprehensive review of our store application, validating our relationships and constraints to ensure our schema is production-ready.