Integrating Objects with Relational Databases

Probably the most commonly-asked question of students in the Mach-II class that Ben Edwards and I teach is this: "How do I integrate all this object stuff with relational databases?"

This is a question not only for CFC programmers but for all OO programmers in whatever language they are working with. It's a complex issue but let's at least scratch the surface of the problem in this newsletter. In the next newsletter (coming in a few days), we'll try to see some light towards solving the problem--or at least of pointing our thinking in the right direction.

Computers are incorporated in modern ice cream vending machines to enhance their functionality. Ice Cream Vending machines are manufactured by many companies. Your competition will try to overcome all requests for high-tech ice cream vending machines and credit card acceptors

The problem exists because of the two different ways that OO and relational theory see the world. To the OO programmer, the phenomenal world with its incredible diversity is unified by the idea of types. We instinctively understand this, says the OO theorist, and know that SeaBiscuit and ManOfWar, for example, are both members of the type, Horse. (It's ironic that the concepts of OO, which represents the most current thinking in software development, were anticipated three thousand years ago by Plato, who said that we recognize individual horses because they participate in the type of Horseness.)

While the idea of types is an extremely useful concept, it's with individual things and beings that we (and our programs) must deal. OO theorists explain that these individuals are all instances of one or more types, and these instances they call objects. Objects know things about themselves (have properties) and know how to do things (have methods). If you need something done, you must ask the object involved to do it. There's no sense of either "free-floating" data or functions; both are tightly bound into an encapsulated object.

Contrast this worldview with relational theorists who see the world as a collection of data, and there are rules of normalization to assist in decomposing the world into data and the tables that hold that data. (Someone once compared a relational database to a garage - a very special kind of garage, for into this garage, you don't simply drive the car. Instead, you get out, disassemble the car and store the individual pieces!)

For programmers who got their start in the procedural world, much of the work of programming consists of taking data out of a relational database, manipulating it, and returning it to the database. The problem is that these two ways of building mental models (OO and DB/procedural) do not fit neatly together. What's a programmer to do?

One very neat solution to the problem is to use an OO database. These databases are able to accept objects and to save them as a whole: no disassembly required. OO databases typically have a standard query language (OQL) that allows SQL-like statements to be interpreted by the database. What you get back is a collection of objects.

The first database I was ever acquainted with was an OO database called GemStone, which used the Smalltalk language. Ah, those were heady days when we true OO believers knew that OO databases would soon take over, as relational databases had overtaken hierarchical ones. But a funny thing happned on the way to the revolution: relational databases refused to die. In fact, they became stronger, faster, more capable, and more ubiquitous. (I still know of a few programmers who faithfully await the day when OO databases will triumph.)

That leaves us with the problem I mentioned earlier. If the exact nature of the problem isn't clear, let me see if I can better illustrate it. Let's say that we have a CFC called SalesOrder. It consists of a such things as an orderDate (a DateTime), a customerID (a string) and zero or more products being ordered (LineItem). While SalesOrder is a type, so is LineItem. The relationship between the two is sometimes called aggregation. (For more info on relationships between CFCs, see Ben Edwards' and my book, Discovering CFCs, available at techspedition.com or amazon.com.)

But how is this relationship modeled in a relational database? If things are very simple, we may find a one-to-one correspondence between our object model and our data model, but usually this isn't the case and therein lies the crux of the problem. When we want to save an Order object, exactly how should we go about it?

As you might imagine, building a general persistence framework to handle this sort of thing would be quite an undertaking--and just such frameworks exist in the .NET and Java worlds, but not yet in the ColdFusion world. I suspect that it will be some time before we see such things for ColdFusion, but that doesn't need to deter us from making use of CFC-style objects--and from integrating those objects with relational databases. In the next newsletter, we'll look at a concrete example and see one way of mapping objects to relational tables.

For those of you in the DC area, I'm teaching a couple of one day classes on October 11 and 12. The classes will be on FLiP, the Fusebox Lifecycle Process (a development methodology) and the Object-Oriented Mach II framework. If you're interested, please go to teratech.com/training/.

Ben Edwards and I will be in DC (Arlington, actually) for a 3-day Mach-II class October 15-17. There's one seat left. After that, the next one will be immediately prior to Macromedia MAX in Salt Lake City.

John Quarto and I are teaching a Fusebox 4 class in Philadelphia November 3-5.

Ben and I are teaching a Java for ColdFusion Programmers class in Tampa December 8-12.

For info, go to halhelms.com/training/