Fuzzy Data Matching with SQL

Chapter 9. Orthogonal Data

“orthogonal” (adjective)

1. of or involving right angles; at right angles

2. statistically independent

Or, as some people say, orthogonal data is like trying to cram 10 pounds (or kilos) of “stuff” in a 5-pound (or kilo) bag. We will look at what I mean by that, because there are multiple variations on the theme.

The approaches described in this chapter are for dealing with a set of problems that often arise in legacy applications such as on mainframes or some vended applications. It is popular to disparage the design of such systems and the techniques used, but it should be remembered that the constraint for these applications was often the lack of ability to make changes in the data schemas in a timely or cost-effective manner (or at all). The solutions often provided “escape hatches,” that is, data fields in the schema with looseness or complete freedom as to their contents. These could then be used by “the business” to store whatever it needs in the system at the correct level: customer, loan, experiment, whatever.

This obviously becomes an instant data governance issue! “Obviously,” that is, to us now. At the time such patterns were common because the pain point was strong enough to warrant them, despite the dangers. They were architectural decisions made to solve specific problems in a very “elegant” manner, when viewed from optimizing a limited ability to make systemic changes. Many years and in some cases decades later, the constraints faced ...