One of several great motivations for that approach is that you want the power to refactor the database later without being limited by the fact that you have seven applications from seven different divisions of the company all hooked up to a single schema design. You want to be able to moderate through a business logic layer.

Many businesses wind up in this situation by chance, and then have a very painful journey out of the casual schema they created when they were a small business into something which matches their larger business.

Unfortunately, IMO, supplying a good answer to that question is the 'northwest passage' of software development.

I've spent years at it on and off. I can create such a layer, but the solutions I've come up with are unwieldy from the perspective of the business layer programmer, which is an inadequate solution.

--

If I have another attempt, I'll try to create something which would look to the observer quite a lot like visual basic, but where instead of constructing a Windows forms, you're constructing a state, which consists of a set of forms and a variable store.

The datatypes for the fields would be something like this:

Readonly
Boolean
Text (untyped)
SingleSelect (conceptually like html dropdown)
MultiSelect (like checkbox)
ExposeObject (the object is tagged, client
can bookmark these these objects in its
session)
ReferenceSocket (the socket indicates the tags it
accepts, exposes compatible tags, connection point
for bookmarked objects with compatible tags)
Link (like html link)
Action (like html submit button)

A client API then migrates this with a query language that navigates the states. An example of a dumb session that knows nothing of the app to which it's connecting, and which is rendering a UI on the fly:

# Remote application knows nothing
list all
# It gets back a description of the current state, 'login'

# Remote application logs in to session
assert form is login
set username wizard_2
set password whatever
action login
list all
# Remote application has control again

# Remote application wants to know what operations are available
assert form is portal
list actions

# Remote application decides it wants to go to person
assert form is portal
link "manage person"
list actions

# Remote app decides to add a person
assert form is "manage person"
link new
list all
# Shows fields and actions

assert mode is new
set firstname Wiz
set lastname Ard
set age 40
select male from singleselect
action save
# Application throws an exception with details

# Client app
assert mode is new
set data_of_birth 08081971
action save
list form

Just as SQL is a flexible language for a relational model, this language is a flexible language for an interactive state graph. A client progrmamer who know the state mapping of the app could write tighter instructions than this.

That client app can be anything: desktop app, unit test framework, web app, direct rpc over http.

Conceptually the situation of states, objects and actions is very similar to a text adventure, but I'd been working on it for years before I realised this.

Every time the state progresses from one to the next, it keeps a copy of the previous state in memory.

This logic layer has the database underneath it. The DB is now abstracted away from the user-facing application. Major benefits of this approach:

- You can design the application with almost no concern to user interface. Then you can create a streamlined user interface later that leverages the correct business logic. You can separate the costing for both projects. That's how I came to the problem. It would revolutionise software consulting.

- You can wrap a dumb client interface around the engine. Want your entire business logic exposed to the web, securely? Or RPC? Or mobile? Or for the blind? Done, with literally zero extra effort.

- If we had a relational database that had version numbers for its internal state (think git model), you could write applications that could unwind and rewind. Takes a lot of memory, but you could store the states in a nosql database.

--

OK. I'll try to answer your question now with the best I know of current techniques. Things using current technology that somewhat address the question you asked:

- Filter all chat with the database through stored procedures. Problems with this: (1) non-stateful, you don't have the user's application session; (2) stored procedure languages are a pain to work with. This is the sort of solution you'll find most often in large organisations because you can hire a single known skillset (DBA) who knows both stored procedures and schemas. It's awful but it works.

- Put a HTTP layer between the database and the application. Do all interaction with the database through this. In the payload that users send in they could (1) name the action they want to do such as create person and (2) supply the arguments they should send in for that business case. Basically, here you're doing stored procedures but in language of your choice. Latency is higher than with real stored procedures.

- Have a rule that all application programmers integrate with the application using Apache Cayenne or Hibernate or something similar as an object-relational modeler, in a strongly-typed language. Insist on use of Data Access Object pattern (see Fowler) for any original entrypoint to the object graph. Now when you refactor your database, regenerate your schema, and use the static typing checking in Java to rapidly adjust your application to the changed schema. You'd need to get all client apps to do this, but it's a lot more rapid than if you've got application users using SQL.