[PYTHON DB-SIG] Objects and Relational Databases - Part 1

Joel Shprentz shprentz@bdm.com
Wed, 10 Jan 1996 14:21:08 -0500

This and the following message are forwarded from the Coad Letter mailing
list, which is sent monthly from Object International.  In these two
messages, Peter Coad discusses how problem domain objects should interact
with data management objects.  The discussion extends the data management
appendix to Coad's object modelling book, which was published last year.

Joel Shprentz              (202) 863-3121
BDM Federal, Inc.          shprentz@bdm.com
1501 BDM Way
McLean, VA 22102
------------------------- Forwarded Message Follows --------------------------
Return-Path: <owner-coad-letter@oi.com>
From: <owner-coad-letter@oi.com>
Date: Tue, 9 Jan 96 09:12:21 CST
To: coad-letter@oi.com
Subject: 23a--Data Management--"Objects and RDB" Q&A (Part 1)
Sender: owner-coad-letter@oi.com
Reply-To: owner-coad-letter@oi.com

The Coad Letter
Issue 23a
Category: Data Management
Title: "Objects and RDB" Q&A (Part 1)
Date: Tuesday, January 9, 1996

Dear Friend,

Happy New Year! And welcome to this special two-part issue of The Coad Letter.

This is a *very* technical issue, aimed squarely at developers who are
developing object apps that include relational databases. Presented in Q&A 
format, it builds upon the material contained in the book, "Object Models: 
Strategies, Patterns, and Applications."

This will be an *awesome* new year. More, later!


Peter Coad

                        "Objects and RDB:
        App Architecture and Other Practical Considerations"

                    Peter Coad and David North
              (with added insights from Ken Ritchie)
                           January 1996

This special report builds upon the concepts presented in the book,
"Object Models: Strategies, Patterns, and Applications." Familiarity
with that book (notably the data management concepts in Chapter 1 and
in Appendix C) is a prerequisite to benefiting from the question-and-
answer format that follows.

   DB =  database
   DM =  data management
   HI =  human interaction
   PD =  problem domain
   RDB = relational database
   ODB = object database

Q. What's the impact on PD classes and PD objects, when using an RDB?

   Every PD class with persistent objects inherits from the
   PersistentPD class:

      |inDataBase  |
      |changed     |
      |dmObject    |
      |isInDB      |
      |hasChanged  |
      |save        |
      |refresh     |
      |delete      |
      |new (c)     |
   specialization classes...

      isInDB: a DM object invokes this service, asking a PD object if
         it is already in the database -- and deciding whether to "insert"
         or "update"
      hasChanged: a DM object invokes this service, asking a PD object
         if it has changed (to avoid unnecessary saving to the database)
      save: this service invokes the "save" service in DM object that is
         responsible for objects in this class
      refresh: this service invokes the "refresh" service in DM object that is
         responsible for objects in this class
      delete: this service invokes the "delete" service in DM object that is
         responsible for objects in this class
      new (c): this is a class (c) service. it needs to set "inDataBase" to
         false. and it needs to send the newly created object to the
         DM object for objects in that PD class.

Q. Okay, then, so what about the DM component? What's needed there?

   A DMserver class: a DMserver object takes care of behavior across the 
collection of all of the DM objects.

   Each persistent PD class has a corresponding DM class; and there is just
   one DM object in that class.

       -------------------               ----------
      |     DMserver      |             |   DM     |
      |-------------------|n            |----------|
      |dbConnection       |-------------|pdObjects |
      |-------------------|             |----------|
      |getDMobjectForClass|             |save      |
      |openConnection     |             |load      |
      |closeConnection    |             |loadAll   |
       -------------------              |delete    |
                                        |asDBfloat |
                                        | ...      |
                                    specialization classes...

      getDMobjectForClass: given a class name or identifier as an argument,
         this class returns the corresponding DM object
      openConnection: opens a connection to an RDB
      closeConnection: closes a connection to an RDB
      save: given a PD object as an argument, this service invokes:
         pdObject.hasChanged -- to see if a change has occurred
         pdObject.inDataBase -- to determine whether an "insert" or "update"
            is appropriate
         finally, it saves the result to an RDB.
      load: given a key, this service searches objects already in memory,
         and otherwise loads values, creates an object, and initializes it.
      loadAll: given a table name, for the entire table: load values, create
         and intialize objects.
      delete: given a PD object as an argument, this service deletes
         it from an RDB.
      asDBstring, asDBfloat, ...: data type conversion services, for moving
         between programming language data types and RDB data types.
   Additional note
      for save, load, loadAll, and delete: specific SQL commands
            are detailed in each class that is a specialization of DM.

Q. How do you work with HI-PD-DM objects?

A. Here is an example.

   Begin with HI.
   Use a selection window (lists employees; buttons: add/update/close).
   And use a maintenance window (data entry fields for an
      individual employee; buttons: save/cancel).

   Now consider what happens when someone wants to add an employee.

   - someone pushes the "add" button on an employee selection window
   - the button invokes emplSelWindow.doAdd
   - here is what happens next:

 --------------     ---------------     ------     --------
|EmplSelWindow |   |EmplMaintWindow|   | Empl |   | EmplDM |   
|--------------|   |---------------|   |------|   |--------|
|doAdd         |   |               |   |      |   |        |
|-->addEmpl    |   |               |   |      |   |        | ( ; empl)
|              |---|---------------|-->|new   |   |        | ( ; empl)
|              |-->|openOn         |   |      |   |        | (empl ; empl)
|IF            |   |               |   |      |   |        | //save
|-->connectEmpl|   |               |   |      |   |        | (company ; )
|-->saveEmpl   |   |               |   |      |   |        | (empl ; )
|              |---|---------------|-->|save  |   |        | ( ; )
|              |---|---------------|---|------|-->|saveThis| (empl ; )
|ENDIF         |   |               |   |      |   |        |
 --------------     ---------------     ------     --------

Q. How about another example, where a PD object might interact
   with a DM object, other than the one it directly corresponds

A. How about this: get department for department number.

 ---------------     --------     -------------     --------------
|EmplMaintWindow|   |Employee|   |  Company    |   | DepartmentDM |   
|---------------|   |--------|   |-------------|   |--------------|
|deptFieldExit  |   |        |   |             |   |              |(num ; )
|               |-->|setDept |   |             |   |              |(num ; )
|               |   |        |-->|getDeptForNum|   |              |(num ; dept)
|               |   |        |   |             |-->|loadForDeptNum|(num ; dept)
 ---------------     --------     -------------     --------------

   The departmentDM object checks for the object in memory; if it is
   not in memory, the departmentDM object then:
      - invokes an SQL command to get the values it needs
      - sends a message to the department class, to create and initialize
      the needed department object
      - returns the department object to the sender.

Q. Are there any more responsibilities for the PersistentPD class,
   meaning, the class that all PD classes (with persistent objects)
   inherit from?

   Here is  the current list that we have:
   - attributes: inDataBase, changes
   - services: isInDB, hasChanges, save, refresh, delete,
      getDMobject, new (c)
   where (c) indicates a class attribute or service

A. [Peter] Add these attributes:
   - correspDM    -- the corresponding DM object
   - dmServer (c) -- the one DM server object

   [David] You may want to keep track of the old key values (the
   key values used the last time it was saved to the DB) so that
   you can update the correct row if you allow someone to change
   a value of an attribute that's a part of the key.

   That's all I think of, but there probably will be more you
   will find as you implement it and want to make it smarter.

   [Ken] Yes, I would *organize* the rows by OID (using object ID
   as "primary" key). Secondary index paths can be created, updated
   and destroyed at will--all without disturbing either the object
   store on disk or cached objects in memory.  OID's are usually

   You can add object versioning by extending the organizing OID
   with a version ID, and organizing tables on OID+VID. Be careful 
   not to include the VID in object associations, unless you don't
   mind the extra overhead for updates. Time series versioning can
   use a clock stamp in place of the VID. Normal views select latest
   VID for each OID used as a foreign key.

Q. How do you keep the design document for the DM objects?
   If in the object model, do you write the connections in?

A. [Peter] Yes, DM objects appear in the object model, in the data
   management component. DM objects appear in scenario views, too.

   Regarding object connections between DM and PD objects: show
   the object connections with text (as attributes), rather than
   with graphics (sometimes "short words are worth many pictures").

   [David]  Yes, if you are building specific DM classes for each
   PD class. They will have some generic services (not shown)
   but they will have some specific services that will be shown.

Q. If you work out all of the interactions with the PD objects,
   you might find that it requires several SQL searches. Here is
   an example: calculate gender mix for each internal organization
   within a company.

   However, if you use a select statement using a join, then you
   can get back all of the data for all of the organizations in
   one table. For example,
      employee -- assignment -- company.

   You might do a join, getting a table with employees and
   respective company names. In this case, using a join seems
   like a good choice, for efficiency's sake.

   How would you fit this into the object model (which PD object,
   which DM object)?

A. [Peter and David]  Ask each "internal organization" object such
   a question -- and get the responses you need. If you wanted to do this
   for all internal organizations, you could ask an object that knows
   all of the internal organizations. Yes, a company object.

   - A company messages companyDM (getGenderCountsByIntOrg).

   If a join is needed (as it is in this example):
   - The companyDM object does the join, with an SQL statement.
        To do a join, it must know all of the objects
        that are involved in that join.

   If just a select is needed
   - The companyDM object messages the dmServer, asking for
      the employeeDM object.
   - The companyDM object messages the employeeDM object.
   - The employeeDM object does a select, with an SQL statement.

   This database interaction results in multiple values.
   You will need some way to represent these values in an
   object-oriented way, to return the result to the object
   that sent the message.

   This approach allows you to use the power of the RDB and
   still fit within the object world.

   And, scenario views take on added reality! Here, you'd replace
   an "employee" column:
      company -- employee
   with a "companyDM" column:
      company -- companyDM
   showing how you will interact with an RDB. That brings added
   reality to the model, especially for a system with 10,000
   employee objects. Very nice indeed!

   You will need to take care of such specialized queries, one-

Q. When you want some kind of report generated from
   a database, the use of a select statement using joins is
   often more efficient.

   How would you fit this into the object model (which PD object,
   which DM object)?

A. [David] You will need a strategy for generating reports. Are
   you using a tool or are you going to hand-develop each report?
   I still like using the object paradigm when creating reports. For
   many reports, it works well to have a collection of objects
   that correspond to the lines of the reports, with services in
   those objects for each line. Making the database access match
   up to this in an efficient way takes some work.

   Keeping the object model in your development means that you may
   not always make use of all of the capabilities of the RDB.

   [Peter] That is not so bad. Since when does anyone use all the
   capabilities of any tool available for their use?

   Anyway, your app could be more understandable, more resilient to
   change. That's often a worthwhile tradeoff.

Q. When you have an existing RDB, and cannot change the database
   structure: is it still meaningful to build an object model?

A. [Peter] Yes!

   Certainly, RDB and SQL make it easy and convenient to reply
   to complex value-based queries across a collection. And DM
   objects can readily take advantage of that.

   There are some meaningful benefits for building an object model:
   - Adding problem-domain understanding, without RDB clutter.
   - Discovering functionality that adds competitive advantage.
   - Adding understanding of what attributes are really needed --
      from working out dynamics with scenarios.
   - Adding understanding of functions -- from working out
      dynamics with scenarios.
   - Adding reuse impact -- reuse of both data and functions.
   - Gaining the benefit of better fit with the many tools that
      are available for building apps using objects.

   [David] Yes!

Q. How do you work with the existing tables?

   With view tables? (Some views may not support updates; so this
   is probably not enough)?

   By letting DM objects work with whatever tables already exist?

   In this context, would an existing data model be of help? If so,

A. [Peter] By letting each DM object work with one or more existing
   tables -- whatever it needs to get its job done.

   In this context, a data model of the existing tables
   is quite helpful -- while looking for additional
   PD classes and attributes, content to add in after you've
   developed an initial object model with domain experts.
   A data model of existing tables is helpful when working 
   out the details for each DM object, too.

   [David] Use them as input to the attributes you might need in
   your PD objects. Then do the mapping. If your application creates
   new objects, you will need all of the required fields in a table,
   so you can do an insert. If not, your objects will not have to
   include attributes for all of the columns in a table.

   You can use views just like mapping to tables. If the view
   is a join (other than a self-join), then you (1) can't update
   through it, and (2) need special DM objects that can create
   multiple objects from the same row.

Q. When you want to provide adhoc query capabilities within an
   application, how does that fit into an object model? Is this a
   case of human interaction objects interacting with data management
   objects? (Applications might add security measures for such adhoc

A. [Peter] Yes, this is a case where a human interaction object would
   directly interact with DM objects. Preferred approach: interact
   through an intermediary PD object, an Application object (a PD object
   that knows things about the overall application), so that
   the interaction pattern always follows HI--PD--DM. Such architectural
   consistency is quite helpful (easier to understand, easier to work
   with, easier to communicate to others).

   [David] You have two choices. You can have an adhoc application
   that really just builds SQL requests and sends them to the DM. Or
   you can build your adhoc queries around services that your PD
   objects support.

Peter Coad
  Object Intl, Inc. Education-Tools-Consulting. Founded in 1986
    "Helping teams deliver frequent, tangible, working results"
     8140 N MoPac 4-200, Austin TX 78759 USA
     1-512-795-0202 fax 1-512-795-0332
     direct line 1-919-851-5422 fax 1-919-851-5579
  FREE newsletter. Write majordomo@oi.com, message: subscribe coad-letter
      coad@oi.com   info@oi.com   http://www.oi.com/oi_home.html
  pgp: 3DBA 3BDD 57B6 04EB B730 9D06 A1E1 0550 (public key via finger)

DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org