MariaDB Views and Column Comments: Alternative Approaches for Documentation

Here's why:

• Views are virtual tables based on queries from underlying base tables. They don't have a physical schema like base tables.
• The CREATE VIEW statement in MariaDB doesn't include an option for column comments.

While you can't add comments directly to view columns, there are alternative approaches:

1. Comments in the View Definition: You can include comments within the SELECT statement that defines the view. However, these comments are treated as part of the query and might be optimized away by MariaDB.

2. Base Table Comments: If the view's columns originate from base tables, you can add comments to those columns directly in the base table definition. This provides comments that are visible when querying the view.

3. Documentation: Consider creating separate documentation to explain the purpose of the view and its columns. This can be a more reliable way to maintain comments alongside your database structure.

Attempted Comment in View Definition (Not Recommended):

CREATE VIEW `user_summary` AS
SELECT `user_id`, `username` -- This comment might be optimized away
FROM `users`;

This approach includes a comment within the SELECT statement, but there's no guarantee MariaDB will preserve it.

Base Table Comments (Recommended):

ALTER TABLE `users`
  MODIFY `user_id` INT NOT NULL AUTO_INCREMENT COMMENT 'Unique identifier for the user',
  MODIFY `username` VARCHAR(50) NOT NULL COMMENT 'Username for login';

CREATE VIEW `user_summary` AS
SELECT `user_id`, `username`
FROM `users`;

Here, comments are added directly to the users table definition using the ALTER TABLE statement. When you query the user_summary view, these comments will be associated with the respective columns.

Separate Documentation (Reliable Alternative):

You can create a separate document or use a tool to store comments about your views and their columns. This method offers more flexibility and ensures comments are maintained independently of the database structure.

This approach doesn't involve any code, but here's an example:

• Document: Create a document that describes the user_summary view, including details about:
  • Purpose of the view
  • Definition of the view (SELECT statement)
  • Explanation of each column and its meaning (including comments)

1. User-Defined Functions (UDFs):

While not a direct comment, you can create a UDF that retrieves comments for specific columns based on pre-defined logic. This UDF could access a separate table storing comments or use a configuration file.

Example:

• Create a table view_comments to store comments for view columns (view_name, column_name, comment)
• Develop a UDF that takes the view name and column name as arguments and retrieves the comment from the view_comments table.

This approach requires additional development effort but offers flexibility for managing comments.

1. Extended Metadata Tools:

Some database administration tools like MySQL Workbench offer functionalities for storing extended metadata associated with database objects. These tools might allow attaching comments to views or columns within views.

Note: This functionality depends on the specific tool and its capabilities.

1. Information Schema Views:

MariaDB provides information schema views like INFORMATION_SCHEMA.COLUMNS that contain information about database tables and their columns. While not directly adding comments, you can write custom queries that join these views with your separate comment storage table (similar to UDF approach) to retrieve comments alongside view column information.

• Create a table view_comments (as in UDF example).
• Write a query that joins INFORMATION_SCHEMA.COLUMNS with view_comments to retrieve column names and comments for a specific view.