Archive for Data Management
What is Encryption and Why Use It?
Posted by: | CommentsEncryption is the process of transforming information using an algorithm (called a cipher) to make it unreadable to anyone except those possessing a special key to unlock the information. The unlocking process reverses the encryption and is know as decryption.
Encryption has long been used by militaries, diplomats and governments to facilitate secret communication, and it is now commonly used to protect information within many kinds of civilian systems. According to a 2007 report by the Computer Security Institute, 71% of the companies surveyed utilize encryption for some of their data in transit while 53% utilized encryption for some of their data in storage.
In recent years the use of encryption has increased substantially as more and more information has become accessible through the internet both while at rest on servers and while in transmission from one network to another. The internet is a wonderful resource that allows us to communicate instantly with anyone, virtually anywhere—but it has also allowed access to cyber criminals and electronic hackers who can steal millions of dollars in seconds.
While it is still safest to keep sensitive information on removable storage media such as portable external hard drives and flash memory drives, these can be lost, and this is not a practical security solution in many situations.
Encryption is particularly important for protecting credit card numbers, Social Security numbers, passwords, personal identification numbers (PINs), client lists, business plans and corporate intelligence. However, for many it is also important for concealing political dissent as well as opposition in the workplace.
The best known encryption is Pretty Good Privacy (PGP) developed by Philip R. Zimmermann, Jr. Originally designed as a human rights tool, PGP was published for free on the Internet in 1991. It is now owed by PGP Corporation (a part of Symantec).
There is no known method which will allow someone to break PGP encryption, and it has been described by computer security specialist Bruce Schneier as “the closest you’re likely to get to military-grade encryption”. Nevertheless, early versions of PGP have been found to have theoretical vulnerabilities, so new versions have been developed or recommended.
PGP and similar products follow the OpenPGP standard for encrypting and decrypting data. Defined by the OpenPGP Working Group of the Internet Engineering Task Force (IETF) in Proposed Standard RFC 4880, OpenPGP is today the most widely used eMail encryption standard in the world.
In most cases the impact of unwanted disclosure of information is negligible—but NOT ALWAYS! Do not discount encryption as too difficult, time consuming or expensive. It is not and in some cases is required by law. There is a good chance one day you will be glad you utilize it.
Keep Database Content Consistent
Posted by: | CommentsDatabases and spreadsheets are great for organizing information, but to get the most out of them, content must be entered in a consistent manner. Too often data entry results in a mishmash of styles and formatting. For example, one record may have the name entered first name first while the next has last name first. Or some telephone numbers may be entered with parenthesis delimiting the area code while others employ a hyphen or slash. Etc.
It is important for database managers, usually in conjunction with interested associates, to develop rules for entering information into their tables. Inconsistencies make it difficult filter, sort, query, merge and process the database records and can even lead to data corruption and security vulnerabilities. Data entry rules can take the form of a written style guide, preprogrammed validation routines or a combination of both.
STYLE GUIDE
A style guide (sometimes called a style manual) is a written set of standards for entering content. It is meant to foster uniformity in style and formatting by providing rules indicating how each data element is to be handled. These elements can include names, addresses, telephone numbers, dates of birth and any other fields and field sets that would benefit from constancy. Below is an example of part of a style guide:
| Data Element | Style Requirements | Example |
|---|---|---|
| Customer name | Include. Enter first name first. Exclude title. Punctuate initials and suffix abbreviations. | George E. Jones, Jr. |
| Home telephone | Include. Format: (###) ###-####. | (800) 609-9231 |
| Fax |
Include, if applicable. Format: (###) ###-####. |
(818) 480-4391 |
| Web address | Include with http://. | http://www.peacockdata.com |
A style guide does not have to be exhaustive, only comprehensive enough to advance sufficient standardization. Depending on the situation, one or two pages is often enough, while other circumstances may require a book-sized document. In any case, style and formatting rules must be clear and readable to be effective, and the inclusion of examples has proven to be extremely helpful for end users.
VALIDATION ROUTINES
In addition to or instead of written guides, style and formatting rules are often enforced with validation routines built directly into the interfaces used for data entry. These make sure information is valid, reasonable and secure while at the same time further reduces the opportunity for user error.
Some validation routines are automated so input is restricted at the time of data entry. The most common of these is an input mask which sets a template or pattern for a field or field set that automatically formats entered content. Input masks are particularly suitable for telephone numbers, ZIP and postal codes, times and dates.
Other validation routines are programs set to run when an end user tries to exit a field or save a record. These are similar to (or often exactly like) scripts that check content entered in web forms. Some routines are simple and may only guard against invalid characters and missing information, while others may be very complex and even check spelling and grammar.
• • Data entry rules and preprogrammed validation routines are an important part of data management, and taking time to tackle this part of the development process will inevitably reap generous rewards.
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 |
Regularly Review Database Systems
Posted by: | Comments
Maintaining a successful database infrastructure requires regular review to establish what is going right and where problems may be lurking. This should consist of close consideration of all aspects of the database system, including: hardware & software, administration & input, documentation, staff & training and disaster recovery.
The frequency of database system reviews is dependent on the size of a company or organization and the complexity of the system. At a minimum, we recommend quarterly reviews, but many situations warrant more frequent action.
Prior to conducting any database system review it is important to establish a set of benchmarks and checklists to compare against the findings.
HARDWARE & SOFTWARE: A database system review should begin with an assessment of the applications, computers, workstations, network servers and other devices that underlie and run the system. Decide if they are meeting the expectations and requirements of end users and if they are doing so efficiently. Needs change and technologies grow rapidly, and keeping on top of the machinery is essential in a competitive environment.
ADMINISTRATION & INPUT: Take a long look at how the database system is administered and the input into the tables. Determine if there has been unauthorized augmentation of a database, particularly rogue changes, or if unapproved or non-standard practices and tools are utilized. This will help maintain the system’s physical and logical models as well as prevent costly downtime and gaps in performance.
DOCUMENTATION: The fun often stops for tech people when they have to hang up their programming tools and put their work down on paper. Consequently, documentation is often lacking for database systems. Make sure this is not the case because end use of the system is highly affected. Documentation should include a data dictionary and reflect the current physical and logical state of the infrastructure as well as be understandable to the less tech savvy.
STAFF & TRAINING: The people part of the review is very important because a database system is only as good as those that run and use it. It is important to align duties properly, and the staff needs to have the necessary expertise and training to adequately leverage the technology and be equipped to handle new complexities in the infrastructure. Investment in this area can reap large rewards.
DISASTER RECOVERY: Last but definitely not least, asses the database system in terms of its ability to recover from a disaster. Backups need to be performed regularly and properly stored, and it is vital this includes offsite backup. Additionally, make sure there is an adequate plan for unforeseen complications and worst-case scenarios and that the system’s immunization against viruses, worms and other web-based attacks is at full strength. This is particularly important when there are substantial changes to the database infrastructure.
• • Database management can become overwhelming as requirements escalate and the volume of data mushrooms. Regular review of a database system is essential to preserve the return on investment, meet objectives and insure long-term success.
Be Kind—Don’t Pollute Telephone Fields
Posted by: | CommentsOne of the most exasperating things about processing telephone data is all the junk that often gets added next to the numbers. Little notes like “cell”, “parent’s phone”, “call before 8 p.m.” and “disconnected” can wreak havoc when the information is processed with telephone update services or sent through merge/purge, as well as when utilized in-house.
While here at Peacock Data we clean up telephone fields before processing, many service provides do not. Having the extra information included is particularly destructive when trying to verify telephone numbers or running a reverse append. Often these numbers are flagged as invalid and not properly processed.
Merge/purge can also be harmfully affected if telephone information is used in the match criteria and matches are missed due to the “database pollution”.
The little notes can be a problem in-house as well. They show up when printing telephone lists and when supplying data to your call center. And they can also cause issues when performing queries on the tables.
If notes about telephone numbers are necessary, a separate field should be provided and utilized by the end user. And if the problem persists, database managers can limit the size of the telephone fields so there is not enough room for the notes.
Most importantly, lessons in database etiquette, including a list of do’s and don’ts, should be included in training for anyone who accesses the database tables.
A company or organization’s data resources are among its most important assets. Following a few simple rules when accessing them can greatly help maintain their value and extend their effectiveness.
Be kind—don’t pollute!
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.
Glossary of Data Management & Technical Terms
Posted by: | Comments
Boolean Logic, Checksum, COBOL, Data Dictionary, Foreign Key, Fuzzy Logic, Information Needs Analysis, Information Warehouse, Netware, Object Oriented Programming, ODBC, Pixel, Primary Key, RDBMS, SQL, Token Ring, Topology. . . .
Are you unfamiliar with any of these technology terms? Or do you occasionally come across a data management term you do not know?
If so, we have a new free resource for you: try our Glossary of Data Management & Technical Terms for quick lookups. You may want to include it in your bookmarks.
