22
Fri, Nov
1 New Articles

Denormalizing Code Tables

General
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Last year, I worked on a law enforcement project in which a central database was created as a repository for information about all violent crimes committed in the United States. Once the database was created, sophisticated data mining techniques could be employed to find patterns in the information contained in the database for the purpose of apprehending serial criminals. The project involved various law enforcement agencies sharing data that up to now had only been kept by the National Crime Investigation Center, more commonly known as NCIC. To even attempt such an endeavor, a normalized vocabulary describing violent crimes had to be created.

Many Codes, Many Tables

The first phase of the project was developing the normalized vocabulary, which required that law enforcement personnel perform the tedious task of researching thousands of violent crimes. This research yielded thousands of descriptive traits related to violent crime.

The data in these tables was to be used by the application as taxonomy codes (also known as lookup values) for new cases being entered. The ActsDoneToBody table, for instance, contained such values as BodyFrozen and BodyBuried. When a new case was entered, the data entry clerk would check off all the taxonomy codes that matched that particular crime. The case, as entered in the system, would be highly normalized, thereby making it searchable and analyzable.

There were over a hundred of these code tables, detailing everything from firearms and vehicles to the voice and facial hair of a suspect to the activity conducted by a victim when assaulted. I have included samples from two code tables in Figure 1, page 44. The following SQL command can be used to create the FacialHair table:

CREATE TABLE dbo.FacialHair
( codIDCode varchar(255) NOT NULL ,

codCodeDesc varchar (255),

primary key (codIDCode) )

Denormalization

The second phase of the project involved the software architects. I was to create an entire solution around the normalized taxonomy codes. This involved designing and


implementing the database and the application. I was given the taxonomy code tables in Microsoft Excel format. Each Excel sheet contained a set of taxonomy codes and became a table. At this point, according to the rules of normalization, the database was correctly modeled.

I found myself unhappy with having to manage so many tables. And, every combo box was going to need its own unique select statement. Not to mention the nightmare if they decided to internationalize, in which case I would have to go through each table and add language identifier columns.

I decided to denormalize the tables. Denormalization is intentionally breaking the normal form, which typically means that some data is duplicated. A denormalized structure would resemble the table depicted in Figure 2. This table, which I called MasterCode, can be created from the following SQL code:

CREATE TABLE dbo.MasterCode
(codIDCodeTable varchar (255) NOT NULL ,

codIDCode varchar(255) NOT NULL ,

codCodeDesc varchar (255),

primary key (codIDCodeTable, codIDCode) )

Let me digress briefly to explain why the three characters cod prefix every column in the SQL listed above. You should get into the habit of assigning tables a unique three- character prefix and then prefixing every column of the table with the table prefix. If you use prefixes, you will not be forced to alias your joins because all the column names will be unique. This could save you time, especially if you later decide to write your own SQL generators (programs whose output is SQL to be used in other programs).

Figure 2 illustrates how I consolidated two code tables by adding a column called codIDCodeTable. The primary key is a composite key that includes the codIDCodeTable field and the codIDCode field. I have encountered implementations in which the primary key is simply the codIDCode field. This is advisable if you are in a position in which you can decide to give every code value a system-wide unique identifier. The reason I have not adopted this strategy in my example is that, if you are migrating from an existing system, you may not have the flexibility of changing all your lookup identifiers to unique values. Thus, the composite key will ease your migration woes!

The table is considered denormalized because there are duplicate values in the codIDCodeTable column. This violates the Second Normal Form (2NF), which applies only to tables with composite keys. Second Normal Form requires that each non-key column be fully functionally dependent on the entire key, not just a part of it. In this case, codCodeDesc is dependent only on the codIDCode field, not on the entire key.

When you denormalize, you should question your reasons for doing so. So I now pose the question, “Is this a valid denormalization?” It is a popular opinion that the only reason to denormalize data is to achieve performance improvements. It is also thought that denormalization induces additional programming costs because you have to program code to maintain the denormalization. While these statements are mostly correct, there are some exceptions, which I will explore.

By adding the codIDCodeTable column to the MasterCode table, I have indeed denormalized, but not for the purpose of improving performance. In fact, denormalization would slightly degrade performance, owing to the extra time needed to filter the MasterCode table for the desired codes. Nor will I experience additional programming costs; I will reduce them. This contradicts the idea that denormalizing decreases programmer productivity.

Before you commit to applying this technique indiscriminately, let me briefly discuss some situations in which you may not want to use it, opting instead to use multiple


Risks

code tables. If, for example, your database has only a few code tables and the likelihood for new code tables seems small, you would not benefit much in terms of productivity and maintenance by using a master code table. Or if any performance hit whatsoever would severely affect your system, you may want to consider either not employing this technique or buying more hardware. (You may want to do this anyway, if even small degradation would impair the system, since you are probably far exceeding the safe limits.)

As I mentioned, grouping all codes into one table can begin to degrade performance as the code table starts to expand, but the actual degradation becomes almost insignificant after indexing the codIDCodeTable column. If you want to further reduce performance degradation after indexing, you may want to avoid putting all your codes into one table and use a rule of thumb to decide whether a certain set of lookup values should have its own table. A colleague has adopted the rule that, if a set of lookup values has more than 51 records, it gets its own table. This allows him to include the states of the United States in the master code table. My own rule is, if a set has more than a few hundred records and it appears that more may be added over time, I create a table for it.

Because the vehicle model table for the violent crime database contained about 4,700 records and, of course, new models appear yearly, I created a VehicleModel table.

Migrating Existing Systems?

Assume that you have decided to merge all your code tables into one. In other words, you have adopted a “taxonomy consolidation initiative.” (Or at least that is what you will tell your boss if he asks why you are having so much fun!) What is the best way to modify a production system with all of its associated applications? Well, you may not have access to the application source code, which would mean that you cannot modify the applications themselves. How, then, can you make the application continue to function correctly once you start deleting code tables?

The solution is simple. First, create the MasterCode table and populate it with all the data from all the code tables. Then create views on the MasterCode table that are named the same as your existing code tables. For example, the following code creates a view named FacialHair:

CREATE VIEW dbo.FacialHair AS

Select

codIDCode As IDFacialHair,

codCodeDesc As FacialHairDesc

From dbo.MasterCode

Where codIDCodeTable = ‘FACIAL HAIR’

Before you can create this view, you must drop or rename the existing FacialHair table; otherwise, you will get a message saying that there is already an object named FacialHair in the database.

Notice that I “aliased” the column names to make the view appear identical in structure to the old FacialHair code table. This tricks the application into continuing to access the object named FacialHair; however, it will now be a view instead of a table. This strategy allows you to migrate databases to the proposed data model.

Many software development companies use this technique. In a recent engagement with Microsoft, I found innumerable instances of the master code table concept. They had used a similar table structure to store product types, customer segment types, and company types. I have seen projects in the human resources sector that use master code tables to house employee job titles, job codes, department codes, state codes, and ZIP codes.

So, what is the real benefit of denormalization? The most significant benefit is decreased time to market. You will save yourself at least several days by not having to


Rewards

create 50 tables with similar structure but varied field names. Admittedly, this savings would be reduced in the event that you are migrating from a production system, since you would have to create the corresponding views.

Furthermore, you will know exactly where all your data lies; that is to say, the maintenance will be much easier, and you will need only one screen to maintain a code table! New hires will be able to quickly understand the data model, since they will not need to know the dozens of tables that would otherwise have housed all the taxonomy values. And, finally, when you are ready to put your database on the Internet with multilanguage support, you will have fewer tables to modify.

In an ideal situation, you will have just begun to develop a new application, so you will not be faced with the onerous task of modifying an existing system. Since such a situation is not very likely, I am versed with the philosophy, “If it ain’t broke, don’t fix it!” What you will find is that, when you adopt this strategy for your code tables, you will have time to take care of all those other things that are broken!

IDFacialHair FacialHairDesc

F1 Clean Shaven F2 Van Dyke
F3 Fu Manchu F4 Full Beard
F5 Fuzz
F6 Goatee
F7 Lower Lip
F8 Mustache
F9 Sideburns F10 Unshaven

IDVoice VoiceDesc

V1 Disguised V2 Soft
V3 High Pitch V4 Loud
V5 Low Pitch V6 Medium V7 Monotone V8 Nasal
V9 Pleasant V10 Raspy

Figure 1: The normalized database contained many small tables.


CodIDCodeTable codIDCode CodCodeDesc

FACIAL HAIR F1 Clean Shaven FACIAL HAIR F2 Van Dyke
...

VOICE V1 Disguised VOICE V2 Soft
...

Figure 2: In a denormalized database, many code tables can be consolidated into one.


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: