About the Problem Leading to EDO

In many business applications there are situations where tables of data contain common repeating patterns of information that are not best managed if stored redundantly in each table. A good example of this is address information. There are many business cases that result in logical data models, including address information in entity designs. For example, address information is logically associated with Organizations, Employees, Persons, Companies, Orders, Subscriptions, and more.

In Aptify 3.x and prior versions, this information was maintained separately in each table in order to simplify the development and report-writing aspects of the data model. Added normalization in a data model usually comes at a cost in the area of reporting ease of use, performance for queries, and programmatic complexity when working with the data model. On the other hand, added normalization generally will improve transactional performance due to smaller table sizes and more efficient data storage patterns.

One of the design goals for the Aptify 4.x and 5.x framework technologies was to enable application developers, including the developers of Aptify's own CRM and Membership suite, to pursue a more normalized data model while at the same time maintaining the ease of programmatic and reporting access that has been enjoyed in all prior versions of the technology. For example, consider the following scenario:

In an application, a developer chooses to create a normalized Address table which will be used in all areas of a system that need to store Address data, including a Customer table and an Orders table.

A simplified version of the schema might look something like this:

The challenge to the developer is that all transactions that relate to a Customers or Orders record in the above model would require managing records in both the Customers/Orders table as well as one or more records in the Addresses table. In addition, it is critical that the transaction management around these multiple inserts/updates be managed properly so that an update to a customer profile happens in its entirety as part of a commit.

In addition, report writers must join in multiple tables for common report writing requirements, such as to create simple rosters or label reports on a Customers record. This complexity is particularly problematic for many of the report writers who do not have substantial database experience and fit the description of a power-user more than a database administrator or programmer.