Domain Logic and SQL (2003)

Figure 1: The database schema for the example (UML notation)

Transaction Script

Transaction script is the pattern name I coined for a procedural style of handling a request inP of EAA. In this case the procedure reads in all the data it might need and then does the selection and manipulation in-memory to figure out which months are needed.

def cuillen_months name
  customerID = find_customerID_named(name)
  result = []
  find_orders(customerID).each do |row| 
    result << row['date'].month if cuillen?(row['orderID'])
  end
  return result.uniq
end

def cuillen? orderID
  talisker_total = 0.dollars
  find_line_items_for_orderID(orderID).each do |row|
    talisker_total += row['cost'].dollars if 'Talisker' == row['product']
  end
  return (talisker_total > 5000.dollars)
end

The two methods, cuillen_months and cuillen?, contain the domain logic. They use a number of "finder" methods that issue queries to the database.

def find_customerID_named name
  sql = 'SELECT * from customers where name = ?'
  return $dbh.select_one(sql, name)['customerID']
end

def find_orders customerID
  result = []
  sql = 'SELECT * FROM orders WHERE customerID = ?'
  $dbh.execute(sql, customerID) do |sth|
    result = sth.collect{|row| row.dup}
  end
  return result
end

def find_line_items_for_orderID orderID
  result = []
  sql = 'SELECT * FROM lineItems l WHERE orderID = ?'
  $dbh.execute(sql, orderID) do |sth|
    result = sth.collect{|row| row.dup}
  end
  return result
end

In many ways this is a very simple minded approach, in particular it's very inefficient in its use of SQL - requiring multiple queries to pull back the data (2 + N where N is the number of orders). Don't worry about that too much at the moment, I'll talk about how to improve that later. Concentrate instead on essence of the approach: read in all the data you have to consider, then loop through and select what you need.

(As an aside, the domain logic above is done that way to make it easy to read - but it isn't what I feel is idiomatic Ruby. I'd prefer the method below which makes more use of Ruby's powerful blocks and collection methods. This code will look odd to many people, but Smalltalkers should enjoy it.)

def cuillen_months2 name
  customerID = find_customerID_named(name)
  qualifying_orders = find_orders(customerID).select {|row| cuillen?(row['orderID'])}
  return (qualifying_orders.collect {|row| row['date'].month}).uniq
end

Domain Model

For the second starting point, we'll consider a classical object-oriented domain model. In this case we create in-memory objects, which in this case mirror the database tables (in real systems they usually aren't exact mirrors.) A set of finder objects loads these objects from the database, once we have the objects in memory, we then run the logic on them.

We'll start with the finders. They slam queries against the database and create the objects.

class CustomerMapper
  def find name
    result = nil
    sql = 'SELECT * FROM customers WHERE name = ?'
    return load($dbh.select_one(sql, name)) 
  end
  def load row
    result = Customer.new(row['customerID'], row['NAME'])
    result.orders = OrderMapper.new.find_for_customer result
    return result
  end
end

class OrderMapper
  def find_for_customer aCustomer
    result = []
    sql = "SELECT * FROM orders WHERE customerID = ?" 
    $dbh.select_all(sql, aCustomer.db_id) {|row| result << load(row)}
    load_line_items result
    return result
  end
  def load row
    result = Order.new(row['orderID'], row['date'])
    return result  
  end
  def load_line_items orders
    #Cannot load with load(row) as connection gets busy
    orders.each do
      |anOrder| anOrder.line_items = LineItemMapper.new.find_for_order anOrder
    end
  end
end

class LineItemMapper
  def find_for_order order
    result = []
    sql = "select * from lineItems where orderID = ?"
    $dbh.select_all(sql, order.db_id) {|row| result << load(row)}
    return result
  end
  def load row
    return LineItem.new(row['lineNumber'], row['product'], row['cost'].to_i.dollars)
  end
end

These load methods load the following classes

class Customer...
  attr_accessor :name, :db_id, :orders
  def initialize db_id, name
    @db_id, @name = db_id, name
  end

class Order...
  attr_accessor :date, :db_id, :line_items
  def initialize (id, date)
    @db_id, @date, @line_items = id, date, []
  end

class LineItem...
  attr_reader :line_number, :product, :cost
  def initialize line_number, product, cost
    @line_number, @product, @cost = line_number, product, cost
  end

The logic of determining the cuillen months can be described in a couple of methods.

class Customer...
  def cuillenMonths
    result = []
    orders.each do |o|
      result << o.date.month if o.cuillen?
    end
    return result.uniq
  end

class Order...
  def cuillen?
    discountableAmount = 0.dollars
    line_items.each do |line| 
      discountableAmount += line.cost if 'Talisker' == line.product
    end
    return discountableAmount > 5000.dollars
  end

This solution is longer than the transaction script version. However it's worth pointing out that the logic to load the objects and the actual domain logic are more separated. Any other processing on this set of domain objects would use the same load logic. So if we were doing a lot of different bits of domain logic the effort of the load logic would be amortized across all the domain logic, which would make it less of an issue. That cost can be even further reduced by techniques such asMetadata Mapping.

Again there's a lot of SQL queries (2 + number of orders).

Logic in SQL

With both of the first two, the database is used pretty much as a storage mechanism. All we've done is asked for all the records from a particular table with some very simple filtering. SQL is a very powerful query language and can do much more than the simple filtering that these examples use.

Using SQL to its full extent we can do all the work in SQL

def discount_months customerID
  sql = <<-END_SQL
  SELECT DISTINCT MONTH(o.date) AS month
    FROM lineItems l 
      INNER JOIN orders o ON l.orderID = o.orderID 
      INNER JOIN customers c ON o.customerID = c.customerID
    WHERE (c.name = ?) AND (l.product = 'Talisker')
    GROUP BY o.orderID, o.date, c.NAME
    HAVING (SUM(l.cost) > 5000)
  END_SQL
  result = []
  $dbh.select_all(sql, customerID) {|row| result << row['month']}
  return result
end

Although I've referred to this as a complex query, it's only complex in comparison to the simple select and where clause queries of the earlier examples. SQL queries can get far more complicated than this one, although many application developers would shy away from even a query as minimally complex as this.

Understandability

SQL is often seen as a special language, one that's not something that application developers should need to deal with. Indeed many database frameworks like to say how by using them you avoid needing to deal with SQL. I've always found that a somewhat strange argument, since I've always been fairly comfortable with moderately complex SQL. However many developers find SQL harder to deal with than traditional languages, and a number of SQL idioms are hard to fathom to all but SQL experts.

A good test for you is to look at the three solutions, and see which makes the domain logic easiest to follow and thus modify. I find the domain model version, which is just a couple of methods, the easiest to follow; in large part because the data access is separated out. Next I prefer the SQL version over the in-memory transaction script. But I'm sure other readers would have other preferences.

If most of a team is less comfortable with SQL, then that's a reason to keep domain logic away from SQL. (It's also a reason to consider training more people in SQL - at least to an intermediate level.) This is one of those situations where you have to take into account the make up of your team - people do affect architectural decisions.

Avoiding Duplication

One of the simplest, yet most powerful, design principles I've come across is avoiding duplication - formulated by the Pragmatic Programmers as the DRY (Don't Repeat Yourself) principle.

To think about the DRY principle for this case, let's consider another requirement for this application - a list of orders for a customer on a particular month showing the orderID, date, total cost and whether this order is a qualifying order for the Cuillen plan. All of this sorted by the total cost.

Using the domain object approach to handling this query we need to add a method to the order to calculate the total cost.

class Order...
  def total_cost
    result = 0.dollars
    line_items.each {|line| result += line.cost}
    return result
  end

With that in place then it's easy to print the order list

class Customer
  def order_list month
    result = ''
    selected_orders = orders.select {|o| month == o.date.month}
    selected_orders.sort! {|o1, o2| o2.total_cost <=> o1.total_cost}
    selected_orders.each do |o|
      result << sprintf("%10d %20s %10s %3s\n",  
	o.db_id, o.date, o.total_cost, o.discount?)
    end
    return result
  end

Defining the same query using a single SQL statement requires a correlated sub-query - which some people find daunting.

def order_list customerName, month
  sql = <<-END_SQL
     SELECT o.orderID, o.date, sum(li.cost) as totalCost,
            CASE WHEN
              (SELECT SUM(li.cost)
                 FROM lineitems li
                 WHERE li.product = 'Talisker' 
                   AND o.orderID = li.orderID) > 5000 
               THEN 'Y' 
               ELSE 'N' 
            END AS isCuillen
       FROM  dbo.CUSTOMERS c 
         INNER JOIN dbo.orders o ON c.customerID = o.customerID 
         INNER JOIN lineItems li ON o.orderID = li.orderID
       WHERE (c.name = ?) 
         AND (MONTH(o.date) = ?) 
       GROUP by o.orderID, o.date
       ORDER BY totalCost desc 
  END_SQL
  result = ""
  $dbh.select_all(sql, customerName, month) do |row|
      result << sprintf("%10d %20s %10s %3s\n", 
			row['orderID'], 
			row['date'], 
			row['totalCost'],
			row['isCuillen'])
  end
  return result
end

Different people will differ about which of these two is the easiest to understand. But the issue I'm chewing on here is that of duplication. This query duplicates logic from the original query that just gives the months. The domain object approach does not have this duplication - should I wish to change the definition for the cuillen plan, all I have to do is to alter the definition of cuillen? and all uses are updated.

Now it isn't fair to trash SQL on the duplication issue - because you can avoid duplication in the rich SQL approach as well. The trick, as database aficionados must be gasping to point out, is to use a view.

I can define a view, for simplicity called Orders2 based on the following query.

SELECT  TOP 100 PERCENT 
               o.orderID, c.name, c.customerID, o.date, 
               SUM(li.cost) AS totalCost, 
               CASE WHEN
                     (SELECT SUM(li2.cost)
                        FROM lineitems li2
                        WHERE li2.product = 'Talisker' 
                          AND o.orderID = li2.orderID) > 5000 
                  THEN 'Y' 
                  ELSE 'N' 
               END AS isCuillen
   FROM dbo.orders o 
     INNER JOIN dbo.lineItems li ON o.orderID = li.orderID 
     INNER JOIN dbo.CUSTOMERS c ON o.customerID = c.customerID
   GROUP BY o.orderID, c.name, c.customerID, o.date
   ORDER BY totalCost DESC

I can now use this view for both getting the months and for producing the order list

def cuillen_months_view customerID
  sql = "SELECT DISTINCT month(date) FROM orders2 WHERE name = ? AND isCuillen = 'Y'"
  result = []
  $dbh.select_all(sql, customerID) {|row| result << row[0]}
  return result
end

def order_list_from_view customerName, month
  result = ''
  sql = "SELECT * FROM Orders2 WHERE name = ? AND month(date) = ?"
  $dbh.select_all(SQL, customerName, month) do |row|
      result << sprintf("%10d %10s %10s\n", 
			row['orderID'], 
			row['date'], 
			row['isCuillen'])
  end
  return result
end

The view simplifies both the queries and puts the key business logic into a single place.

It seems that people rarely discuss using views like this to avoid duplication. Books I've seen on SQL don't seem to discuss doing this kind of thing. In some environments this is difficult because of the organizational and cultural splits between database and application developers. Often application developers aren't allowed to define views and database developers form a bottleneck that discourages application developers from getting views like this done. DBAs may even refuse to build views that are only needed by a single application. But my opinion is that SQL deserves as much care to design as anything else.

Encapsulation

Encapsulation is a well known principle of object-oriented design, and it's one that I think applies well to general software design. Essentially it says that a program should be divided into modules that hide data structures behind an interface of procedure calls. The purpose of this is to allow you to change the underlying data structure without causing a large ripple effect across the system.

In this case, the question is how can we encapsulate the database? A good encapsulation scheme would allow us to alter the database schema without causing a painful round of editing across an application.

For enterprise applications, a common form of encapsulation is a layering, where we strive to separate domain logic from data source logic. That way the code that works on business logic isn't affected when we alter the database design.

The domain model version is a good example of this kind of encapsulation. The business logic works only on in-memory objects. How the data gets there is completely separated. The transaction script approach has some database encapsulation through the find methods, although the database structure is more revealed through the returning result sets.

In an application world, you achieve encapsulation through an API of procedures and objects. The SQL equivalent is to use views. If you change a table you can create a view which supports the old table. The biggest issue here is with updates, which often can't be done properly with views. This is why many shops wrap all DML with stored procedures.

Encapsulation is about more than just supporting changes to views. It's also about the difference between accessing data and defining business logic. With SQL the two can easily be blurred, yet you can still make some form of separation.

For an example, consider the view I defined above to avoid duplication in queries. That view is a single view which could be split along the lines of a data source and business logic separation. A data source view would look something like this

SELECT o.orderID, o.date, c.customerID, c.name, 
          SUM(li.cost) AS total_cost,
          (SELECT SUM(li2.cost)
             FROM lineitems li2
             WHERE li2.product = 'Talisker' AND o.orderID =li2.orderID
           ) AS taliskerCost
      FROM  dbo.CUSTOMERS c 
        INNER JOIN dbo.orders o ON c.customerID = o.customerID 
        INNER JOIN dbo.lineItems li ON li.orderID = o.orderID
      GROUP BY o.orderID, o.date, c.customerID, c.name

We can then use this view in other views that focus more on domain logic. Here's one that indicates the Cuillen eligibility

SELECT orderID, date, customerID, name, total_cost, 
             CASE WHEN taliskerCost > 5000 THEN 'Y' ELSE 'N' END AS isCuillen
        FROM dbo.OrdersTal

This kind of thinking can also be applied to cases where we're loading data into a domain model. Earlier on I talked about how performance issues with a domain model could be dealt with by taking the entire query for cuillen months and substituting it with a single SQL query. Another approach would be to use the above data source view. This would allow us to keep a higher performance while still keeping the domain logic in a domain model. The line items would only be loaded in if necessary using aLazy Load, but suitable summary information could be brought in via the view.

Using views, or indeed stored procedures, provides encapsulation only up to a point. In many enterprise applications data comes from multiple sources, not just multiple relational databases, but also legacy systems, other applications, and files. Indeed the growth of XML will probably see more data coming from flat files shared via networks. In this case full encapsulation really can only be done by a layer within the application code, which further implies that domain logic should also sit in memory.

Database Portability

One reason why many developers shy away from complex SQL is the issue of database portability. After all the promise of SQL is that it allows you to use the same standard SQL on a bevy of database platforms, allowing to change database vendors easily

In reality that's always been a bit of a fudge. In practice SQL is mostly standard but with all sort of little places to trip you up. With care, however, you can create SQL that isn't too painful to shift between database servers. But to do this you lose a lot of capabilities.

The decision about database portability ends up being particular to your project. These days it's much less of an issue than it used to be. The database market has shaken out so most places fall into one of the three major camps. Corporations often have strong commitments to whichever camp they are in. If you consider that changing databases is very unlikely due to this kind of investment, you might as well start taking advantage of the special features your database provides.

Some people still need portability, such as people who provide products that can be installed and interfaced with multiple databases. In this case there is a stronger argument against putting logic into SQL since you have to be so careful about which parts of SQL you can safely use.

Testability

Testability isn't a topic that's tended to come up enough in discussions about design. One of the benefits of Test Driven Development (TDD) is that it's rekindled the notion that testability is a vital part of design.

Common practice in SQL seems to not test. Indeed it's not uncommon to find essential views and stored procedures not even held in configuration management tools. Yet it's certainly possible to have testable SQL. The popular xunit family has a number of tools that can be used for testing within a database environment. Evolutionary database techniques such as test databases can be used to provide a testable environment very similar to what TDD programmers enjoy.

The main area that can make a difference is performance. While direct SQL is often faster in production, it can be much faster to run tests on business logic in memory if the database interface is designed in such a way that you can replace the actual database connection with aService Stub.