Managing Reference Data in Enterprise Databases
- 1st Edition - August 28, 2000
- Latest edition
- Author: Malcolm Chisholm
- Language: English
Reference data plays a key role in your business databases and must be free from defects of any kind. So why is it so hard to find information on this critical topic? Recogn… Read more
Purchase options
Reference data plays a key role in your business databases and must be free from defects of any kind. So why is it so hard to find information on this critical topic? Recognizing the dangers of taking reference data for granted, Managing Reference Data in Enterprise Databases gives you precisely what you've been seeking: A complete guide to the implementation and management of reference data of all kinds.
This book begins with a thorough definition of reference data, then proceeds with a detailed examination of all reference data issues, fully describing uses, common difficulties, and practical solutions. Whether you're a database manager, architect, administrator, programmer, or analyst, be sure to keep this easy-to-use reference close at hand.
* Solves special challenges associated with maintaining reference data.
* Addresses a wide range of reference data issues, including acronyms, redundancy, mapping, life cycles, multiple languages, and querying.
* Describes how reference data interacts with other system components, what problems can arise, and how to mitigate these problems.
* Offers examples of standard reference data types and matrices for evaluating management methods.
* Provides a number of standard reference data tables and more specialized material to help you deal with reference data, via a companion Web site
* Addresses a wide range of reference data issues, including acronyms, redundancy, mapping, life cycles, multiple languages, and querying.
* Describes how reference data interacts with other system components, what problems can arise, and how to mitigate these problems.
* Offers examples of standard reference data types and matrices for evaluating management methods.
* Provides a number of standard reference data tables and more specialized material to help you deal with reference data, via a companion Web site
Database professionals.
Introduction
Audience
Organization
Visit Our Web Site
Chapter 1 - What is Reference Data
A Definition of Reference Data
Common Data
External Data
Reference Data in the Literature
Conclusion
Chapter 2 - A Few Database Design Concepts
Attributes and Entities
Relationships
Subtype Relationships
Conclusion
Chapter 3 - The Diversity of Reference Data
Referencing the World Beyond the Enterprise with Codes and Descriptions
Type Codes
Status Codes
Constant Values
Global Data
Classification Schemes
"Buckets"
What is Not Reference Data
Indicators and Flags
Conclusion
Chapter 4 - Characteristics of Reference Data
Rate of Change
Scope
Volume
Risk
Metadata and Meaning
When Reference Data Values are Known
Discrete sets
Independence
Source of Update
Conclusion
Chapter 5 - Assigning Values to Codes
Descriptions without Acronyms
Descriptions with Acronyms
Using Sequence Numbers to Represent Descriptions
Information Float
Providing Data to External Parties
Multilingual Issues
Conclusion
Chapter 6 - Relationships within Reference Data
Extending Code and Value Tables
Implications of Relationships between Reference Data Tables
Hierarchies of Reference Data
Conclusion
Chapter 7 - Redundancy of Reference Data
Redundancy of Codes and Descriptions
Redundancy across Databases
Synchronization of Data
Replication of Maintenance Functionality
Different Implementation
Global Redundancy
Conclusion
Chapter 8 - The Reference Data Life Cycle
Current and Historical Reference Data
Changing Codes and Descriptions
Succession of Codes and Descriptions
Dates, Times, and Time Zones
Correlating Transactions to Effective Reference Data
Succession
Business Issues Related to Succession
Succession and Sequence Numbers
Conclusion
Chapter 9 - Monitoring the Usage of Reference Data
Which Columns Are Codes Used In?
Which Codes Are Being Used?
Usage of Constant Values
Conclusion
Chapter 10 - Mapping Reference Data
The Need for Mapping
Semantic Analysis
Different Coding Schemes
Format Differences
Value Differences
Undertaking a Mapping Exercise
Conclusion
Chapter 11 - Understanding the Content of Reference Data
Entity and Attribute Definitions
Definition of Values
Distributing Reference Data Metadata
Conclusion
Chapter 12 - Programming and Reference Data
"Hard-Coded" Reference Data
Efficient Use of Reference Data
Using Reference Data as Inputs
Other Issues with the Sequence of Reference Data
Using Reference Data as Outputs
Updating Reference Data from Transactions
Conclusion
Chapter 13 - Implementing Reference Data in a Database
Fully Normalized Design
Benefit 1: Minimize Effects of Change
Benefit 2: Easier Database Navigation
Benefit 3: Dimensions of Data
Controlled Redundancy of Descriptions
Fusing Codes and Descriptions
Reference Data in Data Warehouses and Marts
Physical Implementation of Reference Data Tables
Conclusion
Chapter 14 - Populating Reference Data Tables
Production vs. Test Reference Data
Avoidance of Null Values
Specifying a Value for Unknown Data
Specifying a Value for "None"
Specifying a Value for "Other"
Specifying a Value for "Not Applicable"
Specifying a Value for "All"
Conclusion
Chapter 15 - External Sources of Reference Data
ISO: The International Organization for Standardization
ISO-4217: Currency Codes
ISO-3166: Country Codes
An Example of a Related Standard: Road Vehicle Distinguishing Signs
Standard and Poor's Ratings
Conclusion
Chapter 16 - Multilingual Issues
Adding Columns for Translated Text
Making Language Part of the Key
Sizes of Descriptions
Missing Translations
Translating Acronyms
Tables of Texts and Messages
Getting Things Translated
Conclusion
Chapter 17 - How Is Reference Data Different to Other Data?
Why Treat Reference Data Differently?
Conclusion
Chapter 18 - Using Reference Data for Queries
Traditional Database Queries
Queries Using Reference Data
Level 1: Identify Reference Tables
Level 2: Identify Related Transaction Structure Tables
Level 3: Identify Where the Value is Used
How Can It Be Done?
Conclusion
Chapter 19 - Implementing the Management of Reference Data
Data Ownership and Data Stewardship
Reference Data and Data Stewardship
The Role of the Data Steward for Reference Data
Who wants to be a Reference Data Steward?
A Reference Data Repository
Distributing Reference Data
Legacy Systems
Understanding the Target Systems
Manual Distribution of Reference Data
Electronic Distribution of Reference Data
Managing an Electronic Reference Data Distribution Project
The Goals for Reference Data Administration
Senior Management Commitment
Common Data
External Data
Conclusion
Annex 1: ISO 4217 Currency Codes
Annex 2: ISO 3166 Country Codes
Annex 3: Distinguishing Signs of vehicles in International Traffic
Annex 4: Crosswalk Table for ISO 3166 Country Codes and Distinguishing Signs of Vehicles in International Traffic
Annex 5: Standard and Poor's Long-Term Issue Credit Ratings
Annex 6: Standard and Poor's Long Term Issuer Credit Ratings
Annex 7: Matrices for Evaluating Reference Data Management Within an Enterprise
Table 1: Criteria for Assessing An Enterprise's Reference Data Strategy
Table 2: How to decide if a Database Table is a Reference Data Table
Table 3: Criteria for Assessing Life Cycle Management in a Reference Data Table
Table 4: Criteria For Examining Columns in a Non-Reference Data Database Table
Table 5: Criteria for Assessing Metadata for a Reference Data Table
Table 6: Criteria for Assessing Classification Schemes
Table 7: Criteria for Assessing Management of Reference Data during A Systems Development Project
Glossary
Bibliography
- Edition: 1
- Latest edition
- Published: August 28, 2000
- Language: English
MC
Malcolm Chisholm
Malcolm Chisholm holds an M.A. from the University of Oxford, a Ph.D.from the University of Bristol, and has over 20 years of experience in information technology. His expertise has allowed him to work in various industries focusing on systems development and data administration. Recently he has worked with the United Nations Development Program and Deloitte and Touche.
Affiliations and expertise
President, Askget.com, Holmdel, NJ