A dictionary is a reference tool that provides information about words and phrases. You can think of a dictionary as a collection of “metadata” about each word: its part of speech, meaning (or meanings), etymology (how it arrived in its modern form), origins in Greek, Latin, or some other language), when it first appeared in written form, examples of proper usage, and cross-references to synonyms and antonyms.
Similarly, in database science, a data dictionary is the collection of basic metadata that describes the columns in a data table:
– The technical column name (that is, the unique name that the database system uses to distinguish the column from the others)
– A label (understandable by humans)
– Data type (text, integer, floating-point value, Boolean, and so on)
– The field length/display format (for example, whether to show date values as “5/13/2001” or “13-05-01”)
More generally, a data dictionary is a collection of descriptive information regarding each data object or other item in a data model. (A data model is a representation, often in diagram form, of the various data objects in a database system and their relationships with one another.) So a data dictionary goes beyond the table level to serve as a reference for an entire database system.
A data dictionary can include information including:
– The valid values for a given column
– Which column in each table contains unique row identifiers (and thus can be referenced by columns in other tables)
– A free-text description of each data object that provides more information than a simple name or label
A data dictionary is most useful to database designers and programmers who need to understand the structure and relationships in a database in order to write code to build applications. It is also extremely useful to the database experts and programmers who come along later and need to make changes and enhancements to a database or its related applications.
Beyond that, BI (business intelligence) professionals often need them as a reference when building data warehouses, reports, and dashboards. Data dictionaries are rarely seen or used by end users.