Business Logic in SQL vs. Business Logic in Domain Classes, Part 2

Let's look at how to modify the entities to handle, through polymorphism, a case where employees are classified as contract or permanent and the tax-calculation logic differs only for contract employees. To do this, we change the Employee class to an abstract class and create subclasses for contract employees (ContractEmployee) and permanent employees (PermanentEmployee). Common logic is handled in the Employee class, and the parts that differ, such as the tax-calculation logic, are overridden and implemented in each subclass.

Employee class (abstract class)

public abstract class Employee {
    protected Long id;
    protected Double baseSalary;
    protected Double finalSalary;
    // Other common attributes

    public Employee(Long id, Double baseSalary) {
        this.id = id;
        this.baseSalary = baseSalary;
    }

    // Basic payroll calculation logic (common)
    public void calculatePayroll() {
        double taxDeduction = calculateTaxDeduction();
        finalSalary = baseSalary - taxDeduction;
    }

    // Tax calculation is implemented in subclasses
    protected abstract double calculateTaxDeduction();

    // Getters and Setters omitted
}

ContractEmployee class

public class ContractEmployee extends Employee {

    public ContractEmployee(Long id, Double baseSalary) {
        super(id, baseSalary);
    }

    @Override
    protected double calculateTaxDeduction() {
        // Tax calculation logic for contract employees
        return baseSalary * 0.05; // Example: 5% of base salary
    }
}

PermanentEmployee class

public class PermanentEmployee extends Employee {

    public PermanentEmployee(Long id, Double baseSalary) {
        super(id, baseSalary);
    }

    @Override
    protected double calculateTaxDeduction() {
        // Tax calculation logic for permanent employees
        return baseSalary * 0.1; // Example: 10% of base salary
    }
}

With this structure, all employees (Employee) share the basic payroll-calculation logic, while the tax-calculation logic for contract and permanent employees can be implemented differently. Using polymorphism, we define the calculateTaxDeduction method as an abstract method and implement it concretely in each subclass, thereby managing the differences in tax-calculation logic.

This approach improves code reusability and maintainability, and provides the flexibility to easily add or change the tax-calculation logic for each employee type.

Implementing polymorphism directly in Stored Procedures differs somewhat from the object-oriented polymorphism supported by programming languages. However, even in procedural languages such as SQL and PL/SQL, there are ways to achieve a similar effect through conditional statements, functions, and subprograms. These techniques help improve code reusability and make maintenance easier.

The conditional-statement approach

This approach simply uses conditional statements inside the Stored Procedure to handle the differences in tax-calculation logic between contract and permanent employees. It does not directly implement the principle of polymorphism, but it can produce a similar effect by providing different logic paths.

CREATE OR REPLACE PROCEDURE calculate_employee_tax(employee_id IN NUMBER) AS
    employee_type CHAR(1);
    base_salary NUMBER;
    tax_deduction NUMBER;
BEGIN
    -- Look up employee type and base salary
    SELECT e.type, e.base_salary INTO employee_type, base_salary
    FROM employees e WHERE e.id = employee_id;

    -- Calculate tax according to employee type
    IF employee_type = 'C' THEN  -- Contract employee
        tax_deduction := base_salary * 0.05;
    ELSIF employee_type = 'P' THEN  -- Permanent employee
        tax_deduction := base_salary * 0.1;
    ELSE
        tax_deduction := 0;
    END IF;

    -- Follow-up processing such as updating the tax deduction
    UPDATE employees
    SET tax = tax_deduction
    WHERE id = employee_id;

    COMMIT;
END;
/

The dynamic-SQL approach

Using dynamic SQL, you can achieve a polymorphism-like effect by calling a different procedure depending on the employee type. This approach is more flexible, but it can increase complexity and the potential for errors.

CREATE OR REPLACE PROCEDURE calculate_tax_for_employee(employee_id IN NUMBER) AS
    employee_type CHAR(1);
    tax_calculation_sql VARCHAR2(1000);
BEGIN
    -- Look up employee type
    SELECT e.type INTO employee_type
    FROM employees e WHERE e.id = employee_id;

    -- Call different tax calculation logic depending on type
    IF employee_type = 'C' THEN
        tax_calculation_sql := 'CALL calculate_tax_contract(:1)';
    ELSIF employee_type = 'P' THEN
        tax_calculation_sql := 'CALL calculate_tax_permanent(:1)';
    ELSE
        RAISE_APPLICATION_ERROR(-20001, 'Unknown Employee Type');
    END IF;

    -- Execute dynamic SQL
    EXECUTE IMMEDIATE tax_calculation_sql USING employee_id;
END;
/

These techniques can achieve a polymorphism-like effect by providing different processing paths based on conditions inside the Stored Procedure. However, they cannot fully replace the direct, declarative polymorphism offered by object-oriented programming languages. The appropriate technique should be chosen according to the situation and requirements.

The conditional-statement approach

In this approach, the tax-calculation logic for each employee type is implemented with IF-ELSEIF conditional statements inside a single Stored Procedure. Because all the logic is embedded in one procedure, that procedure must be modified whenever the logic changes. This approach is simple and intuitive to implement, but it has the following drawbacks:

Extensibility: When a new employee type is added or the tax-calculation logic changes, the procedure must be modified, which can increase complexity.
Separation of concerns: Because all tax-calculation logic lives in a single procedure, a change to one tax calculation can affect other parts, and concerns are not well separated.

The dynamic-SQL approach

In the dynamic-SQL approach, a different procedure is called dynamically depending on the employee type. This allows the tax-calculation logic for each type to be managed in a separate procedure, promoting separation of concerns. The main benefits of the dynamic-SQL approach are:

Extensibility: When a new employee type is added or the tax-calculation logic for a specific type changes, only the tax-calculation procedure for that type needs to be modified. This improves extensibility and maintainability.
Separation of concerns: By separating each tax calculation into its own procedure, changes to one piece of logic have minimal impact on the others. This increases the modularity of the code and makes it easier to modify or extend specific logic.

In conclusion, the dynamic-SQL approach applies the separation-of-concerns principle more effectively, making it easier to manage and modify each tax-calculation logic independently. Through separation of concerns, one of the key principles of software design, it contributes to greater maintainability and extensibility of the system.

The following table compares the key differences between implementing business logic with Stored Procedures and with Java objects. It evaluates the two approaches in terms of leveraging polymorphism and applying Separation of Concerns (SoC).

Standard	Stored Procedure	Java objects	Notes
Polymorphism	Limited. Pseudo-polymorphism via dynamic SQL or conditional statements.	High. Direct polymorphism through inheritance and interfaces.	Java's object-oriented nature can make it better suited to handling complex business logic.
Separation of concerns	Possible but complex to manage. Extra work is needed to separate and manage each piece of logic.	High. Concerns are easily separated at the class and method level.	In Java, classes and packages make it easier to separate concerns clearly and manage responsibilities.
Extensibility	Medium. Adding new logic may require modifying procedures.	High. Easily extended by adding new classes or extending existing ones.	The Java object model is more flexible to extend and makes it easy to follow the Open/Closed Principle.
Maintainability	Low. Testing and deploying logic changes can be difficult.	High. Easy maintenance through unit tests and CI/CD.	The Java environment makes refactoring and unit testing easy, with rich support from IDEs and a wide range of tools.
Ease of testing	Low. Requires a separate database setup, and isolated tests are difficult.	High. Isolated tests are easy with unit tests and mock objects.	Java supports isolated testing and TDD using JUnit, Mockito, and similar tools.
Database independence	Low. Dependent on the SQL syntax and features of a specific database.	High. Database-independent development through JDBC, JPA, and similar.	Java applications make it relatively easy to switch databases, and database-access code is highly reusable.
Performance optimization and tuning	High. Runs inside the database server, reducing network overhead.	Medium. Performance optimization may be needed when using an ORM.	Because Stored Procedures work closely with the database, they can offer performance advantages in certain cases.

The OCP (Open/Closed Principle) is an object-oriented design principle stating that software entities (classes, modules, functions, etc.) should be open for extension but closed for modification. In other words, it should be possible to extend a system's functionality without changing existing code.

A case that violates OCP

With a Stored Procedure: If the tax calculation logic for contract and permanent employees is handled by conditional statements inside a single Stored Procedure, that procedure has to be modified every time a new employee type is added. For example, if a freelancer employee type is introduced and needs its own tax calculation logic, yet another conditional branch must be added to the existing Stored Procedure. This approach violates OCP: it requires changes to existing code and undermines the extensibility of the system.

When OCP is respected

With a Java object model: Consider the case described above, where Employee is defined as an abstract class and ContractEmployee and PermanentEmployee implement it. In this structure, when a new employee type (say, a freelancer) needs to be added, you simply create a new class that extends Employee (for example, FreelanceEmployee) and implement the tax calculation logic inside it. New functionality can be added to the system without touching the existing code (Employee, ContractEmployee, PermanentEmployee). This approach follows the OCP principle well: the system can be extended without modifying existing code, which improves maintainability and extensibility.

Conclusion

A design that follows the OCP principle can greatly improve the flexibility, extensibility, and maintainability of a system. With a Java object model, it is easy to apply OCP by leveraging fundamental object-oriented principles such as polymorphism and inheritance. A Stored Procedure-based approach, on the other hand, may offer performance advantages in specific situations, but it can make it difficult to follow OCP in terms of code extensibility and maintainability. It is therefore important to weigh these principles at the design stage and choose the approach that best fits the project's requirements and long-term outlook.