Making a data dictionary

A data dictionary is a document that catalogs the organization, contents and conventions of a database or collection of databases. It lists in written form all the databases, tables, views, fields and data definitions and often information about the table layouts, the relationships between tables and other details about the database schema.

Making a data dictionaryIt does not contain the actual data from the database system, only information necessary to manage and utilize it. It is also not an instruction manual, though a data dictionary is often included as part of an instruction manual.

There is no universal standard as to the level of detail in a data dictionary. What is included is dependent on the audience and the complexity of the database infrastructure. System administrators and programmers will usually have a highly detailed document, sometimes complete with visual depictions, while end users may only have the basics.

Below is an example of a data dictionary for a bookkeeping database with three tables. It shows the kinds of information typically included in a data dictionary, however, it is not meant to be all-inclusive. Other columns that might be provided could show if a field takes null values and the precise points where each field begins and ends. If scientific or technical information is involved, a column indicating normative ranges may be useful. The possibilities are myriad.

A data dictionary is an important part of database system documentation. Devoting the resources needed for a quality document will help insure fewer problems and significantly aid in productivity.

EXAMPLE DATA DICTIONARY FOR A BOOKKEEPING DATABASE

Number of Tables: 3

Table: name of the table. Field: name of the field. Rel: Table relationship key (if any); PK = primary key, FK = foreign key; see Foreign Key Relationships. Type: field data type. Width: field width. Dec: number of decimal points (if any). Description: data definition of the field contents.

Foreign Key Relationships: (1) points to Customers table Id field. (2) points to Sales table Invoice field.

Table Field Rel Type Width Dec Description
CUSTOMERS ID PK Character 10   Customer ID number
NAME   Character 25   Customer name
CUST_TYPE   Character 1   Customer type (key):

A = Active
I = Inactive
P = Prospect
TERMS   Character 1   Payment terms (key):

N = Net Due
P = Prepaid
SALES INVOICE PK Character 4   Invoice number
CUST_ID FK (1) Character 10   Customer ID number
SAL_DATE   Date 8   Date of sale
SAL_AMOUNT   Numeric 10 2 Amount of sale
RECEIPTS ID PK Character 10   Unique ID number
INV_NUM FK (2) Character 4   Invoice number
REC_DATE   Date 8   Date of receipt
REC_AMOUNT   Numeric 10 2 Amount of receipt

Leave a Reply