Data Dictionary: a thought

Data Dictionary (DD) is usually created by the development lead or the database analyst during or after the creation of the Data Model. The most popular format of a DD is using a spreadsheet or in rare case text document. Some people also uses the database's Data Dictionary infrastructure (Oracle has a Data Dictionary infrastructure where you can enter Definitions for Tables and Columns as comments in a system table. The second most important thing after the creation of the definition is the change log. Most of the time I've seen this has been ignored or given less importance in a project. But to me this is a single most important thing the Data Model designer should emphasize apart from the Data Dictionary definitions. Because even though we think of a initial Data Model as invincible for next application changes but, in practical life, it is not.

I've seen and used another spreadsheet to maintain the change logs which is asynchronous and not integrated with the Data Model designing activities. Now the question is how we can enforce the change logs with the Data Model activities. Here is a proposed solution:

We can have a Data Model for the Data Dictionary. Below is a pseudo script of the DDL

TABLE DD_TABLE (
TABLE_NAME -- may be prefixed with schema to uniquely identify
TABLE_DEFINITION
TABLE_STATUS -- Active, Inactive, Decommissioned, Future_Use etc.
CREATE_COMMENT
CREATED_BY
CREATED_DATE
LAST_UPDATED_BY
LAST_UPDATED_DATE
)

TABLE DD_TABLE_HISTORY (
TABLE_NAME (FK from DD_TABLE)
OLD_VALUE
NEW_VALUE -- redundant information for reporting purpose
CHANGE_COMMENT
CHANGED_BY
CHANGED_DATE
)


TABLE DD_COLUMN (
TABLE_NAME (FK of DATA_DICTIONARY_TABLE)
COLUMN_NAME
TABLE_STATUS -- Active, Inactive, Decommissioned, Future_Use etc.
CREATED_BY
CRATED_DATE
LAST_UPDATED_BY
LAST_UPDATED_DATE
LAST_UPDATE
)

TABLE DD_COLUMN_HISTORY (
TABLE_NAME (FK from DD_TABLE)
COLUMN_NAME (FK from DD_COLUMN)
OLD_VALUE
NEW_VALUE -- redundant information for reporting purpose
CHANGE_COMMENT
CHANGED_BY
CHANGED_DATE
)

Triggers can be used to enforce to have default log entry into the history tables unless otherwise entered by the Data Model designer. What do you think about it?