How to Document Database Objects with Annotations

Every database has them. A set of columns — or even whole tables — that no one can explain why they exist. Any documentation about them (if it ever existed) is long lost in your corporate wiki.

To help you understand your schema, it would be better to record notes inside the database, ideally right alongside the columns and tables. This would allow you to view these details in your tools or by querying the data dictionary.

Most database systems allow you to do this with object comments. For example, in Oracle Database, you can do this with the COMMENT command:

This information then appears in the corresponding object’s comment dictionary views:

This is a great way to help other developers and database administrators (DBAs) understand your schemas.

But comments are just a string. This makes them awkward to use if you want to store many properties about an object.

For example, it’s common for database tables to include an INSERT_DATETIME column. These columns should all be:

• System generated: The database should supply the values using column defaults.
• Mandatory: No nulls allowed.
• Insert only: These values should never be updated.

This information relates to your intention for using the data: It’s not just about why these columns exist but how to interact with them. Recording these details helps future maintainers understand how to use these data in apps.

You could store these properties alongside other notes in the comments. The question is which format do you use? JSON? XML? Delimited key-value pairs?

Whichever you choose, you need all future maintainers to stick to the same format. This becomes challenging for long-living systems. It would be better if the database itself enforced some structure on the comments.

Oracle Database 23c addressed this by adding usage annotations.

Better Database Documentation with Annotations

Annotations are keys with optional values. You can use them to record any metadata you want about the object in question.

For example, this annotates the INSERT_DATETIME column with the properties listed above:

You may wonder why allowed_writes uses a JSON array, even though there’s only one value. Why not just use the string “insert”?

You’ll likely want to apply this annotation to many columns. These may also support updates or deletes. Making them all arrays makes them easier to process compared to a mix of arrays and plain strings.

Once you’ve added the annotations, you can view them in the *_usage_annotations views:

These views show annotations for all object types. This means they’re all accessible in one location, which makes it simple to access annotations programmatically to generate separate documentation if you want.

The key-value structure of annotations makes them more powerful than comments.

But there’s still a challenge when you’re using them for common values. Most or all the tables in a schema will have an INSERT_DATETIME column. These should all have identical annotations. Trying to keep all these in sync over time is tough!

Instead of copying the annotations to every INSERT_DATETIME column, it would be better to define them once in a shared object. Then apply this object to all the columns that need it. This ensures they all have the same properties.

The question is: how?

Standardize Annotations with Domains

Oracle Database 23c addressed this by adding usage domains. These build on domains in the SQL standard. They extend existing data types with optional properties including:

• Constraints
• Defaults
• Annotations
• …

Use the CREATE DOMAIN statement to make one. This creates an INSERT_DATETIME domain with the annotations above:

You can then apply this domain to columns when creating or altering tables. Do this by specifying the domain either instead of the data type or using the domain clause after the data type. This copies the annotations from the domain to the table columns:

To link a domain to a column, both must have the same base data type. You can only associate the INSERT_DATETIME domain with timestamp columns, not date, number, varchar2, etc.

With this centralized definition, you can be confident all columns using a domain have the same annotations. This applies even if you want to change them in the future.

For example, you may want to specify that you allow filtering and sorting operations on insert timestamps, meaning it’s safe to use these columns in the WHERE and ORDER BY clauses.

You can alter the domain to add these annotations:

The database automatically applies these to the corresponding database columns:

Document Data Intention

Database schemas can be complex. Understanding the purpose of every part can be tricky. By documenting this in the schema itself, you help everyone know the how and why of each bit.

Comments are a widely used way to record these details. Their unstructured nature limits their usefulness. The key-value structure of annotations in Oracle Database 23c gives you more power.

In Oracle Database 23c, you can also define domains. These give you a single point of definition for common data values, such as insert timestamps.

Combining domains and annotations gives you a simple way to describe data. Together they form a new class of SQL statement: data intention language (DIL).

As schemas grow, using these DIL statements will be key to helping other developers understand both why and how to use it.

Help your fellow developers and DBAs: Document your database with domains and annotations!