Tables , Columns, and Keys
When building a business application or connecting to an existing SQL database, the core concept of tables and columns can be visualized as a spreadsheet of raw data. When designing a business application or extending an existing legacy model the first step is to identify the primary key(s) for each row. The ability to identify a unique row is critical for Espresso Logic to work correctly. WIthout an identified primary key updates and deletes would be difficult if not impossible to perform. The Primary Key is also used in foreign key declarations (e.g. relationships) to join parent to child. Espresso Logic will use these native relationships and user defined declarations to enforce referential integrity (RI) at the server across databases. The attributes are used for column derivations (formula, sums, counts, parent copy) and the entities hold the cross column validations.
If each table represents a spreadsheet page, then a workbook would be a collection of tables with ‘links’ that join or lookup related data. The relationship is defined as a foreign key from the child table to the parent table. Relationships can include one or more column attributes. The advantage of the predefined relationship is that it helps the SQL engine optimize joins and validate related data (RI). A relationship is often described by the roles it plays from parent to child. A common example is the state code field in an address collection. The parent table StateCodeTypes has a primary key stateCode and the Address table has a foreign key reference from the stateCode. Espresso Logic uses these relationships to validate data before writing back to disk. This means that you can add new tables and new user defined relationships for lookup and validation without having to change your existing schema. (See Espresso Logic Multi-database support page). The Resource editor and Live Browser both use these relationships to help build new nested documents and display parent/child data and foreign key data lookups.
Derivations (SUMS, COUNT, MIN, MAX)
When you see a spreadsheet with subtotals and totals – these are formula expressed on a column or row. In our example, we know we can add formula to each single record to create row totals. For column subtotals, we will introduce a new parent table to hold these aggregate values. The common example is the shopping cart where the order total is the parent and the cart details are the individual rows. Our parent may have columns to sum the order line total amount, the discounts applied, and any tax or shipping costs associated with the row. Counts can be applied to the parent (e.g. you have 3 items in your cart). If your existing database schema cannot be easily changed, simply add a new parent table to a new database and create a user defined relationship from the parent to the existing child. New entries into the child can automatically create the parent (manage parent rule) and create these aggregations. In fact, the concept of a multi-hierarchy rollup is very easy to achieve. Weeks can roll up to months, months to quarters, and quarters into years. Business logic can create the same analysis on your entire SQL database that you would consider using a spreadsheet. If a row derivation is needed, the model will need to be altered to add a new column.
One special rule is the parent copy – using a relationship to the parent table, a parent attribute value can be copied down into the selected child column. A common example is the shopping cart, we want to copy the current price of the product down to the cart line item so that the price can be used to calculate the line total (price * quantity). If the parent price changes in the future – it will not impact the child. If you need to propagate a change to all children, use an Event, described below.
Putting it together
In our shopping cart example, we have been asked to add a new pricing policy (Buy one Get one – BOGO) to our existing application. We are also told that the max limit per order is 4 and the max limit per customer is 10. Further, these rules only apply to selected products during a selected date range. To make it more difficult our DBA told us we cannot change the data model – only the logic. We will need to create new tables to hold the BOGO products, effective dates, and limits. We will also need a global customer bogo table to count lifetime limits. Our new tables will be joined to our existing shopping cart, product, and the new pricing rules will be applied to our existing formula.
Our Bogo_product table has effective dates, product ID, and bogo limits for orders and customers, and the discount price. It is up to the rule itself to determine if a product is used in the BOGO calculation and is within the effective date. The count on the CustomerBogo table is used to determine the lifetime limit. These tables can be added to the existing database or a new database. Now the formula to determine the line item price is invoked to lookup the price and then ask if this is a bogo product within the effective range.
var rows = logicContext.getRowsByQuery("bogo_product", "select * from bogo_product where id <> "+row.productID +' and effStartDT >= getDate() and effEndDate <= getDate()); for (var i = 0; i < rows.length; i++) log.debug('Found bogo_product:' + rows[i].name);
Validations are used to enforce lifetime customer and client line item purchase amounts. In Espresso Logic, rules are added and invoked based on a dependency tree of columns and entities. This means you do not need to invoke or ‘call’ rules, rules are invoked when state change occurs (e.g. insert or update). The image below is an example of the Logic Design Studio relationship editor used to join tables between new and existing databases.
Espresso Logic can connect to one or more of your existing SQL and NoSQL databases and create an instant REST API for tables, views, and stored procedures. Business Logic can be placed on top of existing entities and attributes or new tables can be added to new databases and joined into your legacy system to create new REST API endpoints that provide sophisticated business transaction logic. Data Model concepts like adding new tables or altering tables to add columns to support business rules is an integral part of the design lifecycle of reactive programming.