A data dictionary is an integral part of a database. It holds information about the database and the data that it stores, i.e., the meta-data. Any well designed database will surely include a data dictionary as it gives database administrators and other users easy access to the type of data that they should expect to see in every table, row, and column of the database, without actually accessing the database.
Since a database is meant to be built and used by multiple users, making sure that everyone is aware of the types of data each field will accept becomes a challenge, especially when there is a lack of consistency when assigning data types to fields. A data dictionary is a simple yet effective add-on to ensure data consistency.
Some of the typical components of a data dictionary entry are:
• Name of the table
• Name of the fields in each table
• Data type of the field (integer, date, text…)
• Brief description of the expected data for each field
• Length of the field
• Default value for that field
• Is the field Nullable or Not Nullable?
• Constraints that apply to each field, if any
Not all of these fields (and many others) will apply to every single entry in the data dictionary. For example, if the entry were about the root description of the table, it might not require any information regarding fields. Some data dictionaries also include location details, such as each field’s current location, where it actually came from, and details of the physical location such as the IP address or DNS of the server.
Format and StorageThere exists no standard format for creating a data dictionary. Meta-data differs from table to table. Some database administrators prefer to create simple text files, while others use diagrams and flow charts to display all their information. The only prerequisite for a data dictionary is that it should be easily searchable.
Again, the only applicable rule for data dictionary storage is that it should be at a convenient location that is easily accessible to all database users. The types of files used to store data dictionaries range from text files, xml files, spreadsheets, an additional table in the database itself, to handwritten notes. It is the database administrator’s duty to make sure that this document is always up to date, accurate, and easily accessible.