Archive for Database Design
Making a Data Dictionary
Posted by: | CommentsA 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 |
Start at the Finish Line
Posted by: | CommentsWhen planning a new data management projects, results will be better, costs lower and headaches lessened if you consider what your objectives are before you begin. Always start by thinking about what you want to do with your data both now and down the road.
The worst kind of database system is one put together piecemeal as new demands arise. At some point it becomes more of a hindrance that a help. Many become monsters that seem to have a life of their own.
Before you design your databases, tables and user interfaces and decide on purchases, consider all the kinds of data you want to track and the best and most resourceful way of doing so. But to do this you need to gather some information first.
Talk to the end users who will utilize your data. Find out what they need and how they will be using it. Just as important, determine what they would like to do in the future and what has frustrated them most about data in the past.
Also talk to those who will be entering information and the techs who will be working directly with the database system. Find out what will make them more efficient and what has previously held them back.
Finally talk to the vendors who will be processing your data and supplying equipment and third-party lists. Ask them what you can do to help them achieve the best results for you and reduce costs without sacrificing quality.
All of this will affect what tables you design, what fields they will contain, what relationships there will be between them and how end-users will access the information. It will also affect what equipment and lists you acquire, when you buy them and who you hire to make it all work.
Your new data management project should not be planned until after gathering the insight needed to establish what the end results should be. Once you have seen the view from the finish line, you will be much better equipped to create a database system that will get you where you want to be days, months and years from now.
