In which the reader, having been intellectually prepared with a review of set theory, gets their first glimpse at relational database design.

To qualify as a table in a relational database, Chris Date states that it must have a primary key. I agree. While a DBMS like Sql Server will allow you to insert a set of rows into a ‘table’, its not really a relational table. Not everybody is hip to this concept, and I have seen vendor developed systems with hundreds of tables that have no primary key defined. These sorts of systems are not able to take advantage of the benefits relational theory provides, but for many entrepeneurs, the easiest way to make money selling software is to pay as little as possible to developers.
A ‘relational’ table must contain the ability to uniquely identifiy a single row in the table. A column or combination of columns must be unique for each row. Occasionally a table may contain more than one set of columns that can uniquely specify a row. These are called candidate keys, and one must be selected to be a primary key.

Ensuring that each table has a primary key is what I refer to as “Primary key discipline”. As far as what comprises the primary key, there are generally two types of keys—natural and artificial. Which type works best for you depends on the individual situation.

A natural key is one that is composed of column(s) that exist in the data in the row. For instance, in one of our favorite examples consider the invoice that has multiple product sales on it. An invoice table will have the invoice number, and using that for the primary key produces a natural type primary key. I have encountered situations where all but one of the columns makes a row unique, and that last column might contain a quantity or other attribute. Wide natural keys may have performance implications when queries are executed, but its extremely important to understand the logical aspect of this first, and once that is all mastered, one may lift up the hood and look at query performance.

When a primary key has more than one column, its referred to as a composite key.

An artificial key is composed of a column or columns added to the existing columns ‘data’ of a table. The most common implementation of this is an incrementing integer assigned at the time a row is inserted. In SQL Server, this is provided by an “identity” type column that takes care of the incrementing value for you. Another value for an artificial key may be a GUID, a Globally Unique Identifier. These are many bytes wide, and in a circumstance where there are millions of rows, the smaller size of an integer might be more performant. I believe the nature of the artificial key is such that designing a scheme where a table has more than one artificial key seems to defeat the advantage of a single small key that an artificial key can provide.

While I used to much prefer a natural key when one or two columns would do the trick, artifical keys can make certain things simpler. For instance, when a key is 7 columns wide using it in queries can be cumbersome. For instance the code to determine when one composite key is greater than another, and the values have a hierarchy, such as year, month, day, product sequence number require a lot of code to determine which is greater. Dates are normally treated as a single column to simplify this type of thing, but the example helps us think of a composite key with a hierarachy of values. For instance, if the year is greater, the values for month are not important. If the year is the same, the month needs to be considered, and so on down the hierarchy.

An artificial key allows attributes to be changed without the key changing. For instance, I believe that in general Azure cloud objects are guid-indexed. Because of this, I can rename an entra group without it being a breaking change. All references to the group, such as the list of groups an employee belongs to, automatically display the new name. Were they key the group name, changing it would be a breaking change to other components of the database that refer to the group by name.

Another concept, perhaps advanced, and certainly nuanced, is whether table is “ledgered” This word comes from the olden days when companies used paper accounting ledgers. If there was an error in an accounting entry, or an adjustment to it, a correcting entry is added to the ledger rather than changing the original entry. A good definition of a ledgered table is one where inserted rows are never changed. Knowing this definition created an awareness of the concept and allows precise and compact communication. I might ask a developer, “Is the table ledgered?” When a table is ledgered, one can create an incremental copy of the data by simply keeping track of the previous “high water mark”—the highest key value that was copied in the previous extract process. Datetime may also work as a high water mark, depending on the nature of the data.

I once worked with some financial analysts who worked with data we copied from a large vendor database (no primary keys there) into their own sql server where they queried it. This was financial data—an accounting ledger in fact. To copy the entirety of the vendor table into the analysts server took the better part of a day. So, we set up a scheme of incremental loads. Well, turns out the vendor mostly did not go back and change entries, but on occasion they did. So it was sort of mostly ledgered. We found the incrementally loaded data was good enough so we did daily incrementals and every few weeks we would set aside some time and run the full load which brought all the changed entries up to date.

There is a lot of wisdom here, and it probably warrants multiple readings for complete understanding. The main thing to know on first brush is that primary key discipline is essential to rigorous and systematic relational database design.

In which the author illuminates a final set theoretic concept which brings the reader perilously close to the act of relational database design.

Many of the concepts I discuss in blogs involve common sense things that we are all familiar with, but the discussion adds some precision to the thinking, and some nomenclature that eases discussion. With relational database design, most of these concepts are grounded in mathematics.

When it comes to deciding which things get their own table, cardinality is one of the easiest conceptual tools to use. Cardinality is a mathematical word for something simple, the number of objects in a set. Cardinality is sometimes indicated by the vertical bar |. For example the cardinality of {1,2,3} is 3; otherwise expressed as |{1,2,3}| = 3. Why learn a new word for something we already know? Its for precision and brevity of expression. The word itself sets the context for what is being conveyed. For a developer, there are very few discussions that use the word ‘cardinality’ which do not refer to relational database design.

A brief example to assist your understanding. Let’s pretend we are discussing an extremely simple property tax system. We start to model it and come up with a ‘property’ table. The elements of this table represent land parcels that exist in the real world. Then we want to add building information to the table, so we add a building value attribute. After some discussion, we learn that some property parcels can have more than one building.

At this point we can say “The number of properties does not equal the number of buildings.” Its more elegant to say “The cardinality of the properties does not equal the cardinality of buildings.”
And knowing of unequal cardinalities, you generally know that you need two tables. One table cannot accurately depict the information. Thus ends the lesson for today…

In which the author discloses a pet peeve and lays out the minimum necessary understanding of set theory for query writing and relational database design.

While the concepts had antecedents in the form of thoughts and papers written by mathematical thinkers, the generally accepted origin of relational database theory is a paper written by a mathematician or engineer at IBM, E. F. Codd, and published in the Communications of the Association of Computing Machinery in June of 1970. Entitled “A Relational Model of Data for Large Shared Data Banks”. codd.pdf

The paper is written in mathematical language and not very accessible to a non-mathematician. Fortunately the concepts are laid out in simpler fashion in subsequent publications such as the one by Chris Date that I previously mentioned, and I imagine in hundreds of computer science textbooks by now.

In the olden days, I attended a relational database session in which LargeFinCo brought in an expert to give the development staff one day’s worth of relational database theory. He started out by explaining that it was called ‘relational database theory’ because the tables had relationships between them. I prepared to object, but another programmer beat me to it, and explained that is not the reason its called relational, and in fact the relation is a mathematical analogue to a table. Its a minor point, really a point of style, but perhaps something to whip out at some point during a heated debate over a relational database design, or to embarrass an instructor with only a superficial understanding of his course material—which we ought not do as that would be mean.

Codd was a mathematician, and a “relation” is a mathematical object with many attributes and applications. For the purposes of our discussion, a relation can be thought of as a set of name-value pairs which can form the basis of something that can be represented as a table. A precise mathematical description of a database table can be expressed as “a set of n-tuples”, but we will get to that later.

The foundational building block of relational database theory is the mathematical concept of the set. While I proudly display the book Naive Set Theory by Halmos in my library, one need not go to such extremes. I do commend you to some fuller explanations of set theory available on the interweb and found by your favorite search engine. There are a few things you must memorize and understand at an intuitive level to work with relational databases—I believe that a full enough explanation would run less than 10 pages. Most of these things people learn by working with this stuff, but I believe a more formal grounding brings out some nuance that one might not pick up on the job.

Here are the concepts that I believe are fundamental to relational database theory, and extremely general in application to many aspects of computer technology:

Definition of a set: Mathematically a set may be any arbitrary collection of designated objects. For instance the set of integers greater than 3, or the letters in the english alphabet. When I talk about sets here, I will always be referring to sets of identical mathematical objects. This will be explained as we go through it.

Union of sets: The set of elements contained in two sets with duplicates eliminated. For instance the union of {a,b,c,d} and {d,e,f} will be {a,b,c,d,e,f}. The letter d is in both sets but only included once in the resulting set. Note that set operations are “closed on sets’, that is sets are input to the operation and the output is a set.

Intersection of sets: The set of elements both sets have in common. For instance the union of {a,b,c,d} and {d,e,f} will be {d}.

Subtraction of sets. The removal from one set the elements of the set that exist in another set. For instance subtraction of {d,e,f} from {a,b,c,d} will be {a,b,c}. This can also be expressed as identifying the intersection of two sets and removing the intersection from the target set.

Multiplication of sets: “Cartesian product”. Create a set containing all possible combination of the elements in two sets. For example, the multiplication of {1,2,3} and {a,b} results in {1a, 1b, 2a, 2b,3a,3b}. Multiplication of large sets yields very large results.

Partition of a set: Identifying subsets of a set such that a union of the subsets yields the entire set, and an intersection of the subsets yields an empty set. One common partitioning scheme is the partitioning of the continental United States into individual states and the District of Columbia which is a federal animal and not a state, strictly speaking.

These concepts and set operations are ubiquitous in SQL query writing and relational database design.

In which the author reflects on some of the better aspects of a generally misspent youth.

It must have been 1995 and I was working as a developer in LargeFinCo* primarily as a mainframe COBOL coder. I had gotten pretty good at using the IBM IMS database, which in computer science terms is a navigational database—one basically navigates in an inverted tree structure. The database administrators were promoting the use of DB2 which is IBM’s relational database product.

I was conservative, technologically, and skeptical of all new things from IBM due their track record of releasing buggy or short lived products. I resisted the use of DB2. A friend expressed his excitement about DB2 and relational databases in general, and at some point I thought “Maybe there are some new database concepts I ought to learn about”. I purchased a copy of Chris Date’s book “An introduction to Database Systems”, and this small act, as can sometimes happen in life, changed the trajectory of my career.

In a high school math class, perhaps in 1976, I recall set operations being discussed while a Venn diagram was displayed on the chalkboard. Little did I know that day or two on set theory was a glimpse at my destiny. So, back to LargeFinCo in 1995—Date’s book had the two qualities I like to see in a technology book—it was authoritative and comprehensive. It turned out I had a knack for writing SQL, the relational database query language, and quickly became a go-to guy at work for people with difficult SQL questions—and that remains true today. At some point, perhaps a few years later, I was exposed to another relational product, Sybase SQL Server. This provided another glimpse into my future as that product evolved into Microsoft SQL Server.

LargeFinCo, in the 1990s, had another process which proved indispensable to me—Database Design meetings. As a coder on a project which would use DB2 or Sybase, or even perhaps IMS back then, developers, system users, and database administrators had a series of meetings in a room with lots of whiteboards. The DBAs would take the system users through a series of questions about their data, and prepare relational entity relationship diagrams which formed the database design they would implement. I note that at the time, they used crow’s foot notation to indicate cardinality among tables, and perhaps bonding to it as the first approach I saw, as any baby animal bonds to the first thing it sees upon birth, I still use crows foot notation today, some 30 years later.

As with many computer design discussions, there were disputes among the participants about various points of the design, and at times these devolved into raised voices and anger—myself not least among them… It turned out that in an environment where the smallest nuances of design were endlessly debated, I received a comprehensive understanding of the design concepts—brand new to me then—which have formed the foundation of all of my design work since then. Reflecting on the decades, I have seen that the fundamentals of relational database design have not changed much from Chris Date’s articulation of them, and the process of meeting with system users to document how their data can be properly contained within relational database tables, have changed very little since then. The moral of this story is that once you learn the fundamentals, you are good to go.

I have many system implementations under my belt, and one thing I noticed pretty early on with relational databases was that once properly designed and implemented, the systems using these databases tended to be stable and the data accurate. When improperly designed or implemented, I saw systems which were unstable and required constant attention from programmers, and had multiple data quality problems. One indicator of an improperly designed relational database is that developers and report writers use ‘select distinct’ frequently in their queries, even in the multiple levels of a complex query.

Relational database theory is beautiful to those who find certain mathematical concepts beautiful, and it is fundamentally a mathematical creature. The theory is determinate and not complicated, once the fundamentals are mastered. Once I got past the initial ‘boot camp’ learning process, I have found it enjoyable and satisfying to build a solid and reliable system.

I note over the years there have been data storage concepts that tout the advantage of not having the strict structure of proper relational design. One appears to have been named in defiance of relational database theory called ‘No SQL’. There seems to be some sort of emotional appeal to freedom from rules and minimal learning curve which makes things ‘easy’ to use. Some years ago I noted Chris Date arguing endlessly in internet forums (or pre-internet blogs) against those who advocated for inaccurate relational designs, or against the entire concept as obsolete. Yet, as concepts and products became fashionable and fell out of fashion, a properly designed relational database continues to provide a powerful and accurate storage mechanism in the right circumstances.

Certainly a relational database is not the appropriate tool for all storage processes, and at times I wonder to what degree even an entire organization lacks the skills to build a good relational database. I have seen more than one vendor product with hundreds, or even thousands, of tables with zero primary keys declared and zero enforced referential integrity.

As I reflect on a 45 year career as a programmer, and a man nearing retirement—or at least retirement age, I am proud of much of what I have learned and done. Relational database theory and SQL coding have in part paid for my house, cars, and the raising of my children. All of this based on a passing thought I had one day: “Maybe there are some new database concepts I ought to learn about”.

* In my career, I spent many years working at large financial companies which I will refer to as LargeFinCo. They had large mainframe systems with DB2, and some had Sybase SQL Server. The specific identity of these companies is, ultimately, of no importance.

In math and computer science, general concepts are those that can be used in many situations. Being familiar with these concepts in a more formal manner helps us recognize them in the wild and incorporate them into designs or coding.

We are all familiar with namespaces, but maybe not under that name, and not in a systematic manner. For instance, telephone numbers comprise a namespace. Lets think about some attributes of this namespace. We know that a telephone number must uniquely identify a single phone. We know there is some degree of hierarchy to telephone numbers. At the highest level we have a country code, and below that the area code. Decades ago, the area code designated a specific geographic area, when phones were wired into fixed locations, but with the advent of cell phones, the area code no longer has geographic significance, and simply adds three digits to the original 7 digit phone number.

This one simple example provides an essential requirement for many namespaces—the ability to uniquely identify something.

Mailing addresses comprise another familiar namespace. Given the way that mailing addresses evolved over the past 300 years, the mailing address is tightly coupled to geographic location. The mailing address has more of a hierarchical structure. For mail within America, we have the 50 states and District of Columbia (which is some sort of federal island and not considered a state).

Within states, we have cities, and within cities, we have streets and ultimately the house or building number. The mailing address namespace introduces two new concepts—the partitioning of a space, and the hierarchy. The partitioning and hierarchical structure allow us to conveniently designate a single address among hundreds of millions. It would be possible to assign something like a 10 character code to a house or building. This would be very convenient for a computer, but not for people.

More formally, a partition is a set operation whereby a set of some sort is divided into subsets. Partitioning the continental United States into states is a familiar example. In this case, the set is the land area The formal definition of a partitioned space has two interesting set based properties—the union of all partitions yields the original set. The intersection of the partitions yields a null set, or nothing. In other words, when a space is partitioned, there are no overlaps among partitions.

As a state is partitioned to city names, we see that the partitioning of a space can yield a hierarchy. The hierarchical namespace is ubiquitous, and for those of us who have used computers a bit, the nested folder structure of files on a disk drive comes to mind.

When visualizing a hierarchical namespace, the inverted tree structure is often used. At the top is the root of the namespace and the lower nodes represent partitions of the higher nodes. So, an inverted tree diagram of mailing addresses in the US would have the US as the root node, and below that, a node for each state, with city nodes partitioning the state node.

So, when thinking about namespaces in a slightly more formal manner, we have some attributes of a namespace we can think about. For example:

1) Does a namespace need to uniquely identify something?
2) Is there a hierarchical/partitioned structure to the namespace?
3) What might be the root of a namespace?
4) How many levels deep is a namespace?
5) How many unique elements can a namespace identify?

In sum, partitioning sets of things into namespaces is ubiquitous both in computer work and daily life. I hope this discussion helps you think about about namespaces while navigating the real world as well as computer activities.