Where Should Domain Logic Live?
Enterprise application architecture has long drawn a line between domain logic — the business rules that govern how data is used — and the data source logic that retrieves and persists that data. For applications backed by relational databases, this usually means keeping business rules out of SQL. That separation is sensible from a maintainability standpoint, but it can be costly. SQL has powerful querying capabilities that can perform much of the selection and aggregation that would otherwise require shuffling data into application memory. Ignoring that power entirely can hurt performance, and sometimes it hurts maintainability too.
Consider a simple business rule: a customer qualifies for the "Cuillen" discount in a given month if they placed at least one order in that month exceeding $5000 in Talisker products. Two orders of $3000 each don't qualify; only a single large order counts. The task is to determine, for a specific customer, which months over the last year were qualifying ones.
Three Ways to Answer a Query
There are broadly three styles for implementing this logic, each with its own trade-offs. We'll use Ruby for the examples, but the principles apply to any language with database access.
Transaction Script
The transaction script pattern is a procedural approach: pull in all the data the request might need, then loop through it in memory to compute the answer. In this case, the script fetches the customer's orders and months, then applies the rule.
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? carry the domain logic. They rely on finder methods that issue SQL queries to pull data from 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
This is straightforward but inefficient: it issues one query for the customer, one for their orders, and another for each order's line items — a classic 2+N query problem. The structure is simple, but every similar piece of domain logic would have to repeat the same pattern of loads and loops.
Domain Model
A more object-oriented approach builds an in-memory domain model that mirrors the database tables. Finder classes load the data once, and the domain objects carry the business logic. Loading code and logic are separated, so any other operations on the same entities reuse the same loading infrastructure.
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
The loaded classes are plain domain objects:
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 actual discount logic is compact:
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 requires more initial code than the transaction script, but the load logic is reusable. If the application has many different business rules operating on the same orders and customers, that setup cost amortizes. The domain logic itself doesn't need to know how data is fetched. Still, the naive version suffers the same N+1 query problem as before.
Rich SQL
Both previous approaches treat the database as little more than a dumb storage container. But SQL can do far more than simple selects and filters. With a single statement, the entire Cuillen determination can be pushed into the database:
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
This query is more complex than typical CRUD-style statements — many application developers would be uncomfortable writing it — but it's still modest by SQL standards. It offloads the filtering and summation to the database engine, which is exactly what the SQL is designed for.
Performance: The Obvious Question First
Performance is usually the first concern raised, though it shouldn't be the only driver. A disciplined approach is to write clean, maintainable code first, then profile to find bottlenecks and optimize only those hot spots. Still, it's worth looking at the numbers here: on a modest laptop, the complex SQL query ran about twenty times faster than the naive in-memory versions. Realistic server conditions could shrink that gap, but the SQL version will very likely remain an order of magnitude faster.
The main reason is traffic. The in-memory approaches issue a query per order; with a thousand orders per customer in the test database, that's a lot of round trips. The complex SQL only moves a handful of result rows back to the client, while the in-memory approaches drag thousands of rows across the wire.
The in-memory code can be improved. The transaction script can be rewritten to fetch everything in one join, cutting the query count dramatically:
SQL = <<-END_SQL
SELECT * from orders o
INNER JOIN lineItems li ON li.orderID = o.orderID
INNER JOIN customers c ON c.customerID = o.customerID
WHERE c.name = ?
END_SQL
def cuillen_months customer_name
orders = {}
$dbh.select_all(SQL, customer_name) do |row|
process_row(row, orders)
end
result = []
orders.each_value do |o|
result << o.date.month if o.talisker_cost > 5000.dollars
end
return result.uniq
end
def process_row row, orders
orderID = row['orderID']
orders[orderID] = Order.new(row['date']) unless orders[orderID]
if 'Talisker' == row['product']
orders[orderID].talisker_cost += row['cost'].dollars
end
end
class Order
attr_accessor :date, :talisker_cost
def initialize date
@date, @talisker_cost = date, 0.dollars
end
end
This runs about three times faster than the original. The domain model benefits even more from a single-loading change: only the finder method needs to change, and the domain logic remains untouched.
class CustomerMapper
SQL = <<-END_SQL
SELECT c.customerID,
c.NAME as NAME,
o.orderID,
o.date as date,
li.lineNumber as lineNumber,
li.product as product,
li.cost as cost
FROM customers c
INNER JOIN orders o ON o.customerID = c.customerID
INNER JOIN lineItems li ON o.orderID = li.orderID
WHERE c.name = ?
END_SQL
def find name
result = nil
om = OrderMapper.new
lm = LineItemMapper.new
$dbh.execute (SQL, name) do |sth|
sth.each do |row|
result = load(row) if result == nil
unless result.order(row['orderID'])
result.add_order(om.load(row))
end
result.order(row['orderID']).add_line_item(lm.load(row))
end
end
return result
end
One small caveat: to get decent performance from the domain model, the customer's orders were stored in a hash keyed by month rather than an array. That change was self-contained and didn't affect the discount logic itself.
This illustrates a key difference between the two in-memory styles. For a transaction script, a query restructuring surfaces as a substantial rewrite of the whole script; if several other scripts relied on the same data, each would need its own fix. The domain model localizes the change to the loading layer. That separation pays off only if the application has enough domain logic to spread the cost across.
Even after the improvements, the in-memory versions finished about six times slower than the rich SQL query in the test. The reason is inherent: the database does the filtering and summing in SQL, then sends only the answer; the application code must ship all candidate rows to memory first.
Making the Trade-Off Deliberately
Performance alone rarely settles architecture decisions. A common and defensible strategy is to default to an in-memory domain model for readability and evolution, then replace hot spots with richer SQL when profiling demands it. That hybrid approach acknowledges that while this example plays to the database's strengths — heavy selection and aggregation — many queries won't show such dramatic differences.
Multi-user environments can further shift the equation. Lock contention and concurrency have their own behaviors that are hard to predict from single-user benchmarks. Any serious performance work should be validated under a realistic multi-user load, not just a solo laptop run.
Weighing SQL vs. In-Memory Logic
For systems expected to live a long life, change is a certainty. This makes modifiability a primary concern when deciding where to place domain logic. Many teams choose in-memory business logic precisely because it is easier to alter than logic embedded in SQL queries. While SQL is powerful, it has limits; certain operations like calculating a dataset's median require convoluted code, and others are impossible without vendor-specific extensions, which threatens portability.
Another point in favor of in-memory logic is handling pending work. It's often necessary to apply business rules to data before it is committed, and session data awaiting validation shouldn't be constrained by the same rules as finalized records. Loading such transient state directly into a database can create friction.
Understandability and Team Skills
Many application developers view SQL as a specialized language they'd rather avoid, and some frameworks advertise that they eliminate the need to write SQL. While complex SQL can be elegant to some, its idioms are often cryptic to others. A good gauge is to review different implementations and see which one a developer can follow and modify most easily. In many cases, a domain model version is clearest because data access is separated from the logic. SQL is often preferred over an in-memory transaction script by those comfortable with it.
Team composition is a significant factor here. If most developers lack deep SQL knowledge, it's a reason to keep domain logic in memory. Alternatively, investing in SQL training can shift this balance. Architecture decisions are often shaped by the people who must maintain the system.
Managing Duplication
The DRY principle—avoiding duplication—is a powerful design guide. Consider a requirement to list a customer's orders for a month, showing the order ID, date, total cost, and whether each qualifies for a specific plan, all sorted by total cost. In a domain object approach, you'd add a method to the Order class to compute the total:
class Order...
def total_cost
result = 0.dollars
line_items.each {|line| result += line.cost}
return result
end
Listing the orders then becomes a straightforward task:
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
The equivalent in a single SQL statement requires a correlated subquery, which many find intimidating:
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
Beyond comprehension, this SQL version duplicates logic from the initial query that returns only the qualifying months. With domain objects, changing the plan's definition means altering the cuillen? method, and all callers are updated automatically.
SQL can also avoid duplication, by using a view. For instance, a view named Orders2 can be defined as:
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
This view then serves both the monthly list and the month-selection queries, centralizing the business logic:
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
This technique is seldom discussed in SQL literature, despite its utility. In many organizations, cultural and procedural splits between application and database developers hinder its adoption—application teams may be barred from defining views, or DBAs may refuse to create views for a single application. Yet SQL design deserves the same care as application code.
Encapsulation and Change
Encapsulation in software design means hiding data structures behind an interface of procedure calls, enabling changes to the underlying structure without rippling across the system. For databases, effective encapsulation allows schema changes without a painful, application-wide editing session. A common way to achieve this is by layering, separating domain logic from data source logic.
The domain model pattern exemplifies this by working only on in-memory objects, keeping how data is loaded completely separate. Transaction scripts offer some encapsulation through their find methods but expose more of the database structure via returned result sets. In the database world, views can provide encapsulation: if a table changes, a view can be created to maintain the old interface. Updates pose a bigger challenge, which is why many shops wrap all data manipulation in stored procedures.
Encapsulation also helps differentiate between accessing data and defining business logic. SQL can blur these lines, but some separation is possible. The view used to avoid duplication can be split into a data-source-focused component and a business-logic-focused component. A data source view might look 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
This can then be built upon by a higher-level view, such as one that indicates eligibility:
SELECT orderID, date, customerID, name, total_cost,
CASE WHEN taliskerCost > 5000 THEN 'Y' ELSE 'N' END AS isCuillen
FROM dbo.OrdersTal
This same ideology applies to loading data into a domain model. To solve performance hot-spots, you can replace a complex query for in-memory objects with this kind of data source view. Line items can be loaded on demand via a Lazy Load pattern, while summary data is fetched through the view. This retains the performance benefits of SQL without scattering domain rules into queries.
Still, views and stored procedures only provide limited encapsulation. In many enterprise settings, data originates from multiple relational databases, legacy systems, files, and external applications—and XML's growth is increasing the influence of network-shared flat files. In such environments, true encapsulation requires a dedicated layer within the application, which further implies that domain logic should reside in memory.
Portability and Testing
Full SQL portability has always been more theoretical than practical. Standard SQL exists, but every vendor has its quirks. While you can write SQL that is mostly portable, doing so means forgoing many advanced features. In today's market, the database landscape is consolidated into a few major camps, and many organizations have deep, long-term investments in one. If switching vendors is genuinely unlikely, it makes sense to leverage your database's proprietary strengths. Portability is a more pressing concern for product vendors that must support multiple databases, in which case keeping logic out of SQL is a safer path.
Testability is often an overlooked design requirement until Test Driven Development (TDD) brought it to the forefront. SQL code—views and stored procedures—frequently lacks both tests and version control. This doesn't have to be the case. Tools from the xUnit family can test database code, and practices like test databases can provide a supportive testing environment. But for performance-intensive tests, invoking business logic in memory is often much faster, especially when the data access layer is designed to be swappable with a Service Stub.
Deciding on a Strategy
The core decision hinges on your data landscape. If your data is scattered across many non-SQL sources, you should build an in-memory data source layer for encapsulation and keep your domain logic in memory. SQL's strengths don't even apply when a significant portion of your data doesn't live in a relational database.
The dilemma is most interesting when your data resides in a single logical database. Then, the primary considerations are language choice (SQL versus your application's language) and code runtime (in the database or in memory). Team comfort with SQL is a decisive factor. If you plan to put significant logic into SQL, be prepared to abandon portability and embrace your vendor's extensions. If you need to support multiple platforms, keep logic out of SQL.
Modifiability and understandability should guide your initial choice, but performance issues can supersede these. If an in-memory approach suffers from hot-spots that SQL can solve, implement those queries as optimized data source queries—as outlined above. This keeps the database as a data provider while minimizing the amount of domain logic embedded in it.
