Rene Castro
This tutorial will show you how to create a data dictionary for existing MySQL and MariaDB databases with phpMyAdmin - describe tables and columns and export them to a HTML page. PhpMyAdmin is a very popular free web console for MySQL and MariaDB databases written in PHP.
Comments in Data Dictionary
If you want your data dictionary to be useful you need to provide explanation for tables and columns. MySQL and MariaDB databases allow you to add comments to tables and columns. It is great for keeping schema descriptions.
Edit table comments
To edit table comments select the table in the sidebar navigation and then select the Operations tab. Comments are visible in Table comments field.
To edit comment type in text and click the Go button to save.
Edit column comments
To edit column comments select the table in the sidebar navigation and then go to the Structure tab. Column comments would be visible in the Comments column in the list of columns.
To edit column comments select a column by checking the box to the left of its name and click on the Change button.
This action will open a column editor where you can add or edit a comment in the Comments field. To save comment click Save button.
Please be careful not to edit any other field as those changes might impact table definition.
Export Data Dictionary
Once you provided all the descriptions it is good to export it for more convenient access. PhpMyAdmin allows you to export data dictionary to HTML.
To export data dictionary select your database, then click the Structure tab and click on the Data dictionary option below the list of tables.
This option generates the documentation in an HTML page. This is how it looks:
Data dictionary includes:
- list of tables/views and columns
- data types
- default values
- column nullability
- table and column comments
- foreign keys
- indexes and primary keys
Documentation does not include:
- triggers
- stored procedures
- user-defined functions
An alternative: Dataedo
If you want more from the data dictionary tool, have a look at Dataedo. Not only it generates much better looking and interactive HTML but also allows you to visualize schema with ER diagrams, split large database into modules, describe each data element in convenient editor and document table relationships. Here is a sample documentation:
