Out of Vietnam, Part 2

posted by cjh, 15 November 2007

I talked about how we build systems by composing them from elements, not by decomposing monolithic “problem statements”. The elements always depict either states, or transitions between states. These two angles are the information perspective and the process perspective. Process steps always transit between legal states, so the set of legal states must be defined first. It’s not that information is pre-eminent, but it does tend to lead rather than lag the rest of the design. So here I’m focusing on the information part of our design.

Now, we need to build up an overall aggregate picture of what things our system can describe, and what it needs to know about those things. The picture is made of many small elements, and many small constraints on the ways they interact. The sum of these small things forms our conceptual model - they reflect the way we and our clients think about their problem, not its solution. To store them however, we need to group them together for efficient management. That’s what we’re doing when we’re building a database design - writing down all the things that the system needs to know, in a way that will be efficient to manage. There are two goals here: manage all the elements and their interactions without losing track of any, and produce an aggregate structure that is efficient. These goals work against one another.

When we’re done, if we’ve done a good job, we have a normalised database design. “Normalised” basically just means that it provides only one way to represent any of the elementary facts, so that you can’t have two versions that disagree. But there’s another property of normalised data that causes problems: any one “thing” will only have one record, and all facts about that thing for which there is only one value at a given time are stored in that record. This aggregation is a fine principle for creating efficient physical storage structures, but the aggregation leaks into our code.

When we query the data using SQL, there’s one way of accessing a fact that has only one value for each thing - the column - and there’s another completely different and somewhat difficult way of accessing facts that have more than one value for each thing - the join. SQL forces the direct use of the physical database model, while at the same time hiding the true domain model which is present in the elementary form. This prevents the domain expert from properly engaging in verifying the model and ensures communication problems because of the translation and interpretation required.

Being bound to the physical model also tragically limits the agility (evolution) of applications because the physical model is always more unstable than the conceptual model. The mere number of values (or other things) related to a thing in a given fact relationship should be a minor detail, yet it completely controls the physical model. When a requirement changes in tiny ways, we can sometimes end up needing to do a major restructure of the database, potentially across many tables.

Imagine you have a table of users, and one column is the “given names” column. Your client now needs to store information about the reason your parents gave you each name… and all of a sudden you need to move the contents of that column out into a new “given names” table. Every query that fetched a user will probably have also fetched their given names, and so now needs to be rewritten. All we did was add a new fact to an otherwise complete model - why does all our code need to be checked and maybe rewritten? Ok, perhaps “given names” is an uncommon example, but this sort of thing occurs so often in relational databases that for more than twenty years, it has its own name: attribute migration. It’s an example of just one way the addition of a small item to our elementary model causes big impacts on out aggregate design.

So while relational databases are one of the preeminent achievements of computer science, they must move beyond requiring direct dependence on the physical storage structures. SQL is the problem here, because of the gross difference between accessing a column (single value) and another table via a join.

Replacing SQL by a language that has this property of uniformity of reference must be the top priority if the industry is to more forward in solving this critical problem. There is a way out of Vietnam… but only after we replace SQL. Tune in next time for a first peek at the language that can do this, the Constellation Query Language.