Guide‎ > ‎

Database Design

Database design is core to Business Logic, since the major multi-table rules are dependent on relationships. It is critical these relationships be properly defined.

This document provides some familiar background (and our terminology), and then discusses important Hibernate considerations.


Basic Concepts

Database Diagrams

Although not required, it is a Best Practice to maintain a diagram of your Domain Objects, such as:

You can produce such diagrams from a database tool (such as MySql (tm) above), or a Case tool that will also depict Inheritance associations.

In either case, nodes represent Entities, and lines represent Relationships, also often referred to as Associations.


Relationships

Relationships are a foundation of Data Modeling. Although there are many kinds, the most fundamental concept is a one-to-many relationship. These are sometimes called hasA relationships.

Represented by a line (arc) in the diagram, it represents one Parent object that is related to many Child objects. For example, one Customer has many Purchaseorders.

In relational databases, you define a Foreign Key in the child to the Parent. So, Purchaseorder has an attribute CustomerName.

Such keys are frowned upon in Object technology, so Hibernate hides the Foreign Key attributes. Instead, it provides parent/child role accessors as described below. These hide the key manipulation, providing a simple programming model.


Parent Role

A Parent Role is the name a Child uses to access its parent within a one-to-many relationship. In the example above, customer represents the Customer for a given Purchase Order.

Hibernate Domain Objects include accessors that provide access to this data. In Java:

 Customer theCustomer = anOrder.getCustomer();

In Groovy:

  Customer theCustomer = anOrder.customer

Hibernate provides advanced caching services to return already-retrieved data.


Children Role

A Children Role is the name a Parent uses to access a collection of related children within a one-to-many relationship. In the example above, orders represents the Purchase Orders for a given Customer.

Hibernate Domain Objects include accessors that provide access to this data. In Java:

 List<Purchaseorders> customerOrders = aCustomer.getOrders()

In Groovy:

  List<Purchaseorders> customerOrders = aCustomer.orders


Terminology Note: it's what the accessor returns

While the concept is usually quite obvious, the terminology can be troublesome - we need to choose between these two reasonable (but opposite!) explanations for what is a child role?

  • origin-based: is a child role a role in a child (that gets parents), or
  • returns-based: is it in a parent (that gets children).

To make this easy to remember:

  1. Role Names correspond to accessor names, so get<Parent> returns a parent, is a parent accessor
  2. For further help, we are not using the term child role; the plural term children role makes clear that a collection is returned, and discourages the misinterpretation that it is a role in the child (that is, it doesn't really parse to imagine a method in a collection of objects!)

So, to summarize: role names, like accessors, related to what is returned.


Relationship Examples

The sample database contains classic examples of the kinds of Parent/Child relationships present in virtually all databases. In the parlance below, 1:n means one to many.

  • 1:n / different
    This is the most common, where there are different objects at both ends of the relationship. The classic example is Customer/Purchaseorder, where Purchaseorder contains a foreign key of the Customer.
  • 1:n / same (self relationship")
    Here the Parent and Child are the same type - but not the same instance. The classic example is Departments which have (Sub) Departments, where Department has a Foreign Key HeadDepartment. For example, the CEO Department has Sub Departments of Sales, and Engineering. Self-relationships recurse, so Sales has its own Sub Departments such as International Sales and Domestic Sales.
    Note: Transitive Closure is a retrieval concept that means get all related objects and subobjects, recursively
  • n:m / different
    Here there are many objects on both sides of a relationship. Relational database does not support these directly, since a Foreign Key (like any attribute) can have only 1 value. So, you introduce Junction (also called Intersection) Objects to represent these. For example, a Purchaseorder can have many Products, and a Product can be ordered on many Purchaseorders. We introduce the Junction Lineitem which as Foreign Keys to both, as well as additional attributes (such as QuantityOrdered).
  • n:m / same
    These are less common, and rather complicated. The classic example is a Bill of Materials explosion, where Products can be Kits: comprised of multiple Component Parts (which recurses - Components can have Sub Components). Conversely, a Component Part can be a part of multiple Kit Parts. As for n:m / different, you introduce a Junction table, which has two Foreign Keys to the Parent. See Bill of Materials Structure.

As things get more complex, there can be multiple relationships between the same tables.


Inheritance


Hibernate Considerations

Hibernate is quite flexible when it comes to database design. It can adapt to many less-than-desirable data models.

ABL does not support all of these. There are certain restrictions that must be respected in order to define business rules.


Multiple Relationship Disambiguation

The Business Logic Engine requires meta data to match Parent/Children roles that is not made available through Hibernate. In the relatively rare situations where there are more than one relationship between 2 Domain Objects, you must provide an annotation as shown below:

package database.buslogicintro.businesslogic;

...

/**
 * Business Logic Component for Product 
 * (each row represents a Product sold on Purchaseorders to Customers).
 *
 */
@Relationships(rolePairs=["kits: product", "components: kit"])
public class ProductLogic { 

Bidirectional relationships

In order to use a relationship in a sum or count rule, the relationship must be bidirectional.


Components

Embeddable Components are not supported for sums and counts, only entity relationships, for ABL 2.17.  Additional support for utilizing attributes of embedded components is under investigation.


Inheritance

Inheritance is not fully supported.


Aggregate Updates are not supported

Since these do not raise events, the Business Logic Engine is not invoked. So, you cannot do code like this:

Query query = session.createQuery("delete from Lineitem where amount < 1"); query.executeUpdate();


Business Logic Considerations

Be aware of the following important considerations


Stored Data assumed to be correct - recompute as required

Adjustment logic depends on current summed values.  So, if you add / alter a sum or count, you must bring the database into conformance with your logic with queries such as:

update customer set customer.balance =
  (select sum(amountUnPaid)
   from Purchaseorder where Purchaseorder.customername = customer.name) 


Performance Considerations

Please consult this section.
Comments