Making a Data Dictionary
ByA 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.
It 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 |
