The da Vinci Code for Relational Theory

website@infoadvisors.com — Mon, 05 Jun 2006 21:38:00 GMT

From our Book Study of Chris Date's Database in Depth:

I thought that this was a good point in Chapter 1, about why we need to study the foundation upon which our profession is based:

The point about principles is this: they endure. By contrast, products and technologies (and the SQL language, come to that) change all the time—but principles don’t. For example, suppose you know Oracle; in fact, suppose you’re an expert on Oracle. But if Oracle is all you know, then your knowledge is not necessarily transferable to, say, a DB2 or SQL Server environment (it might even get in the way of your making progress in that new environment). But if you know the underlying principles—in other words, if you know the relational model—then you have knowledge and skills that will be transferable: knowledge and skills that you’ll be able to apply in every environment and that will never be obsolete.

So we've all probably studied a bit about normalization, perhaps some about surrogate keys, primary keys, and the specific syntax of our DBMS's implementation of SQL, but have we studied those tool and vendor independent topics that allow our skills to be transferable?

I thought that I had a clear distinction in my mind until I sat down one day to work in Oracle. I needed to get some sample data out of an Oracle database and I normally do this by building a query, then limiting it to the TOP 100 rows. Guess what? TOP 100 works just fine in SQL Server, but Oracle doesn't have a clue what to do with that syntax. It seemed so straight forward to me...

Date goes on to say:

Although it’s certainly possible to use SQL relationally (for the most part, at any rate), sometimes you’ll find—because existing implementations are so far from perfect—that there are severe performance penalties for doing so...in which case you might more or less be forced into doing something not "truly relational" (like writing a query in some weird and unnatural way in order to get the implementation to use an index). However, I believe very firmly that you should always make such compromises and trade-offs from a position of conceptual strength. That is:

• You should understand what you’re doing when you do have to make such a compromise.

• You should know what the theoretically correct situation is, and you should have very good reasons for departing from it.

• You should document those reasons, too, so that if they go away at some future time (for example, because a new release of the product you’re using does a better job in some respect), then it might be possible to back off from the original compromise.

The following quote—which is attributed to Leonardo da Vinci (1452–1519) and is thus some 500 years old!—sums up the situation admirably:

Those who are enamored of practice without theory are like a pilot who goes into a ship without rudder or compass and never has any certainty where he is going. Practice should always be based on a sound knowledge of theory.

(OK, I [Date] added the italics.)

I think that is a brilliant quote, and this position (Practice should always be based on a sound knowledge of theory.) forms the beginning of every professional career, except for IT.

Where are your foundations? In a three ring binder that you threw out to make way for the newest set of language references for you DBMS? In a help file? Still looking for them?

Karen Lopez: Musings on Data, Process, and Architecture

The da Vinci Code for Relational Theory