Jeff Tsai

Posted on Oct 27 Originally published at hackmd.io

Day 3: The Northwind Case Study: Eight Forms and a Custom Business Object

#architecture #dotnet #erp #softwareengineering

The first two posts were about the argument. Today the case study comes on stage.

The case study is Northwind, Microsoft's sample database that has been passed down since the Access era. The reason for choosing it is practical: customers, suppliers, products and orders are tables most people recognize without explanation, so the conversation can go straight to what this structure puts the framework through, without first explaining what the business does.

This post covers:

The four conditions a case study has to meet before it can test a framework
Eight forms in three shapes, and the framework's system fields
Why relationships are declared between forms rather than between tables
The one form that needed code, and why it also came with a repository
What is inside that business object
The framework runs the standard flow; the application takes over only the part it cannot reach

First, what it looks like. This is the order, the most complex of the eight, rendered from one definition on four front ends.

Desktop	Browser

iOS	Android

One master with one detail. Customer, employee and shipper are all lookup windows that bring one record back, each detail row picks a product, and the amount is not typed by anyone. The four front ends share one UI project and read the same definition. Not one of those screens has screen code behind it. The only difference is the thin platform shell on the outside.

1. What a case study has to put a framework through

Framework demos almost all look the same: one table, fields in a row, create, read, update and delete work. That demo proves nothing, because every framework can do it. Not one of the places that actually break is on that screen.

A case study worth using as verification has to satisfy four conditions at once:

Forms have to vary in difficulty. A plain master, a master with cross table relationships, and a document with a master and details all have to be present. A framework that only holds up on the simplest of those is just a form generator.
Relationships cannot have only one shape. One table pointing at another is the basic case. You also need two tables pointing at each other, and an application table pointing at one of the framework's own tables, before the design of the relationship mechanism is forced into the open.
There has to be one form that needs custom logic. If a framework claims most things need no code, and its case study contains no code at all, that is not proof of the claim. It is avoidance of it.
It has to run on more than one front end. A layout definition that claims not to be tied to a UI framework does not get to make that claim until one definition has actually grown two or more different screens.

Northwind holds up on all four. Its complexity sits in a good place: complex enough to test the mechanisms, small enough that the whole thing can be laid out and reconciled item by item.

2. Eight forms, three shapes

Shape	Count	Forms
Plain master, meaning fields, a list and CRUD, with no relationships and no rules	4	Category, supplier, customer, shipper
Master with cross table relationships	3	Product, pointing at supplier and category; department, pointing at employee as its manager; and employee, pointing at department
A document with a master and details	1	The order

Department and employee point at each other. That circle was left in deliberately.

Eight forms map onto nine tables, since the order takes two. Two of those nine are tables the framework provides: department and employee. The permission and organization mechanisms use them, and the application copies their definitions over and uses them as they are. How framework tables and application tables are told apart, and which database each lands in, is the subject of Connections, Transactions and Query Conditions in the Data Access Layer.

These nine tables are not Northwind's original shape

What is borrowed is the business case and the sample data, not the table structure. The original Northwind uses business columns as primary keys, with CustomerID='ALFKI', integer product ids and a composite key on order details. This application kept none of that.

What went in instead are the framework's system fields, and the framework's mechanisms hang off these exact names:

Field	What the framework uses it for
`sys_rowid`	Cross table joins, the WHERE clause of UPDATE and DELETE, auditing, data scope permissions, and the row the user clicked on. All of them compare against this column
`sys_master_rowid`	How details are fetched, and how a whole document is saved together
`sys_no`	The primary key and the default sort order
`sys_id`	Gets a unique index automatically at table creation, which is what guarantees business code uniqueness
`sys_name`	The column shown in a lookup window

So these names cannot be changed, and that is not a naming convention issue. Everything listed above is what the framework uses them to recognize.

Two design decisions, and what they cost:

The primary key and the relation key are separate. The primary key is an auto incrementing sys_no; the relation key is a Guid in sys_rowid. A relation key has to be immutable, because a changed code must not break old documents, and it has to exist before anything is written to the database, because an order is saved in one go and the details need to point at the master first. The cost is one more unique index, and a fatter key.
Details do not use composite keys. If the shape of master and detail differed from case to case, the mechanism that reads and writes a whole document could not be common to every form.

One more cost, and this one is heavier: an existing system has to change its table structure before it can be brought in. Writing the definitions is not enough to run on the original database.

3. Relationships are declared between forms, not between tables

Traditionally a relationship is a table concern: the order table gets a customer column and a foreign key pointing at the customer table's primary key. What that declaration expresses is exactly what it says on its face, that the value in this column must be findable in that one.

The framework moves the relationship up a level:

<FormField FieldName="customer_rowid" Caption="Customer" DbType="Guid" RelationProgId="Customer">
  <RelationFieldMappings>
    <FieldMapping SourceField="sys_id" DestinationField="ref_customer_id" />
    <FieldMapping SourceField="sys_name" DestinationField="ref_customer_name" />
  </RelationFieldMappings>
</FormField>

RelationProgId="Customer" points at the customer program, not at the ft_customer table. No table name appears anywhere in that declaration. Which table to join is something the framework works out by taking that identifier and looking up the customer's own FormSchema.

Pointing at a form and pointing at a table can connect to very different amounts of information:

A table knows only which columns it has
A FormSchema also knows which columns the list displays, what a person sees when picking from a lookup window, which fields are queryable, and whether it wants to add conditions of its own, such as listing only active suppliers

So once that one declaration is written, four things are in place at once:

The field automatically becomes a lookup window editor on screen
Picking a record fills the customer code and name back in
When querying the list, the framework assembles the join that fetches those two columns
What the database stores is the relation key

Traditionally those four are recorded in three places, the foreign key in the database, the picker configuration on the front end, and the join in the query layer, and all three can disagree.

There are no foreign keys in the database

The framework does not create foreign key constraints. Those three relationship columns on the order have only indexes in the database.

That follows necessarily from moving relationships into the definition layer. The relationship's authority is the definition, and the database is a projection of it. Creating a foreign key as well would give that one declaration two sources, and two sources eventually disagree.

The cost: the database will not block orphaned rows for you. If someone deletes a customer with raw SQL, the orders pointing at it are not stopped. Whether that is acceptable depends on whether your database is written to by this application alone. The assumption that definitions are the single source of truth has to be carried all the way down to the database. You cannot give half of it to definitions and half to constraints.

Display columns are not the authoritative value

The two fields ref_customer_id and ref_customer_name do not exist in the table.

The physical order table holds only the customer's relation key
The code and the name are joined in by the server at query time
When the front end finishes picking, it writes the values into the data it is holding, purely so the screen updates immediately. They are not persisted on save
If the customer changes its name, the value shown on the order is correct on the next read

A typical trade-off: one more join per query, in exchange for the screen always showing the current value. The cost is that there has to be an explicit rule about which side is authoritative, and that rule has to be set by the framework rather than decided by each form separately.

How many this application declares

Eight, counted directly. Three on the order master, being customer, employee and shipper; one per order detail row, being the product; two on product, being supplier and category; one on employee, being department; and one on department, being the manager.

The three cross table relationships mentioned in the first post are the three on the order master, and that is also the scope used for reconciliation later.

One of the eight crosses a line. The employee an order points at is one of the two framework tables mentioned above. The salesperson on an order is the framework's employee, and the application does not build an employee table of its own.

Why the circular relationship was left in

Department points at employee, for the manager, and employee points at department. In the data model that is a circle. It was left in deliberately, because any mechanism that grows something out of definitions needs a defined answer for circles:

Generating strongly typed classes at build time: the circle forces a type initialization ordering problem into the open
Assembling queries dynamically from definitions at run time: the circle only affects how many levels that one query joins

This is the first post's build time versus run time distinction, projected onto the data model.

4. The order: the one form that needed code

Seven of the eight forms have zero lines of application code. On the registry those seven declare only a program identifier and a display name, which means they run the framework's default flow. Only the order row names two types:

<ProgramItem ProgId="Order" DisplayName="Orders"
             BusinessObject="...OrderBO, Bee.Northwind.Server"
             Repository="...OrderRepository, Bee.Northwind.Server" />

The two types cover one area each. The business object holds this form's business logic, and the repository holds its data access.

That repository exists for exactly two SQL statements: reading the order's current state as stored in the database, and finding the highest order number this month. The rest of CRUD is still assembled by the framework from the definition.

Putting those two in the repository rather than in the business object is not layering fastidiousness. A business object would have to name the database it connects to itself, whereas a repository is resolved from the FormSchema category plus the current session, and lands automatically in the right company's database. Whoever writes it does not need to know how many companies the system has.

5. What is inside that business object

The order's business object overrides two methods, and the step before saving does four things:

Check that there is at least one detail row
Sum the detail amounts back into the master total
Generate the order number
Check that the status transition is legal

All four look like business logic. Run them through the test from An Overview of Definition-Driven Architecture, would every ERP in the world do this the same way, and only the last one is.

The first three are general. That a document needs at least one detail row, and that the total equals the sum of the details, is established practice. An order number of a prefix plus year and month plus a sequence is the kind of numbering rule most ERPs turn into a configurable mechanism. All three should converge into mechanisms. They live in the application today because the framework has not absorbed them yet.

The last one is the business judgment. Status goes from draft to confirmed to shipped, no skipping and no going back, and details lock once confirmed. That rule differs at every company, since some allow quantities to change after confirmation and some have two more intermediate states, so the application should own it.

That line has moved before

The business object used to do more than those four. Two batches have already moved out:

The amount on each detail row used to be calculated in code. It is now one expression on the field
Three checks, that customer and product are required and quantity is greater than zero, used to be in code. They are now three rules in the FormSchema

<FormField FieldName="amount" DbType="Currency" NumberKind="Amount" ReadOnly="true"
           ValueExpression="quantity * unit_price * (1 - discount)" />

What was saved by moving them is more than those lines. The expression is recalculated the moment the user changes the quantity or the discount, so the front end does not have to write the same calculation a second time to make the amount update live. One formula, declared once, computed correctly on both ends.

One of them went further still: who is able to change it also changed. How the amount is calculated now lives in a definition, so adjusting it means editing a definition file rather than going back to the code side. Every time something moves from code into definitions, the people who can change it move one step closer to where the requirements are. What used to need a slot in the development schedule becomes one line of arithmetic that someone who understands the business can read, and edit.

So the line between what needs code and what does not was never drawn once at the start. It moves forward as the framework's declarative power grows. Of those four items, the first three will be absorbed sooner or later, and only the last will remain.

What stays in code is partly what belonged there all along, and partly what has simply not been taken over yet. Mixed together they look alike. Separated, you can see which one to absorb next.

6. The framework runs the standard flow, the application takes over what it cannot reach

What the framework owns is everything that is the same on every form: query the list, open a record, create, update, delete, pick from a lookup window, validate before saving, and deliver data to the front end. The shape of those does not change with the form. What changes is which fields exist, and the fields are in the definition. Governance is the same story: who may access it, which rows they can see, and who changed what and when.

That is why those seven forms have no application code.

At this point a doubt usually surfaces, and it should. If the whole flow is standardized, what happens to flexibility?

That question matters far more than how much a framework can do. Setting a standard is not hard. What is hard is still having room for exceptions afterwards. Without that room, the first special requirement means carving out the entire flow yourself, and everything saved earlier is handed back. In the case study, the form that runs into this is the order.

There are two ways to take over:

Approach	Mechanism	Nature
Inherit and override	The framework's business object cuts the whole flow into a sequence of overridable steps, being fetch a new record, before save, the save itself, and after save, with delete cut the same way. The application changes only the steps it needs. The order changed two	Replacement, and there is one implementation per program
Hook in a plugin	The framework opens four points around save and delete for external logic	Layering, several can stack, and the customization chain runs after the packaged one without touching the original program

What the two have in common is the answer to that doubt: what you take over is one short segment of the flow, not the whole flow. Overriding "before save" does not suddenly make you responsible for assembling SQL or delivering data to the front end.

Flexibility is not about the framework being able to do everything. It is that you only have to handle the part that differs from how the mechanism does it, or that the mechanism does not support yet. Whether a standard flow and its exceptions can coexist comes down to that granularity. Cut too coarsely and one exception means rewriting the whole thing. Cut finely enough and the standard parts keep working for you.

Back to a claim from the first post

The first post, Why ERP Needs a Definition-Driven Framework, said that across the whole Northwind application, only one of the eight forms needs code written for it. That form is the order, and it needs to take over not because it is more complex, but because it runs into the few things the current mechanisms cannot reach. A case study needs a form like that, or the claim that most things need no code has never been tested.

And handling them takes overriding the two steps the framework cut out, without leaving the standard flow. That room is necessary. A mechanism must not standardize a flow by taking flexibility away. Many of the special cases in an ERP are not badly written requirements. They are what that company's structure and working habits actually look like, and they do not disappear because a framework set a standard. How deep a takeover can go is cut into several levels, and Three Depths of Development and the Big Picture of the Framework lays all of them out.

This is the state definition-driven design is aiming at. Not writing no code, but writing it only where a judgment is genuinely required, and taking over only that one segment when you do.

The next post, The Purpose and Classification of Definition Files, moves into the definition layer, starting with an account of all thirteen kinds of definition file.

📚 Part of the thirty-post series ERP Architect's Notes: Designing a Definition-Driven Framework

📘 Original article on HackMD, in Traditional Chinese:
👉 https://hackmd.io/@jeff377/day-03-northwind-case

📢 You are welcome to republish this, please credit the source.
📬 Follow for more engineering notes:
Facebook ｜ HackMD ｜ GitHub ｜ NuGet

DEV Community