SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Writing Nearly Codeless Apps: Part 2

By David Ziffer,

In our previous article you downloaded the Rapid Application Prototype (RAP) system, which included a sample application called ExampleCRM. Hopefully you noticed a few very interesting things about this four-tier application:

  1. There is no hand-written code in the database.
  2. There is no hand-written code in the data layer except for a few token definitions.
  3. The business rules section contains absolutely all the business logic and it's based on a highly structured pair of templates.
  4. The UI contains only the logic necessary to manipulate the UI components. Most significantly, there is no business logic in the UI.

This means, essentially, that three out of the four tiers of an application (1, 2 and 4 above) can be created with almost no thinking or design work, and two of the four (#1 and #2) involve almost no coding at all. Now this example is somewhat simpler than real-world applications. It has no data search and filtering capabilities for example, which would require that we hand-code a few procedures in the database and call them from the data layer. But beyond that, this app embodies most of what you would need to do when writing a real-world app.

Our objective, is to vastly increase the efficiency of programmers by giving them irresistible short-term incentives to eliminate most of the tedious hand work (and the accompanying errors and maintenance overhead) involved in writing an application. If we can really do this, we'll have a productivity revolution something like the one that happened when Henry Ford applied assembly-line techniques to automobile manufacture.

Shooting for the software ideal

In an ideal world, software implementers building basic database-based applications would need to think about only three things:

  • the data model
  • the business rules
  • the user interface structure

Unfortunately in the real world these important things constitute only a small fraction of developers’ time, because most developers spend most of their time:

  1. reinventing data modeling (i.e. choosing from among too many modeling techniques)
  2. reinventing database access on the database side
  3. reinventing database access on the application side
  4. reinventing business rule framework technology
  5. diddling with endless details of UI component management
  6. and reinventing UI design (i.e. choosing from among too many modeling techniques).

Consequently, the implementation of essentially all projects takes far longer than necessary, costs much more than needed, and is prone to poor quality since few developers can manage all the “reinvention” above very well. And because every application essentially constitutes an entirely new and different development paradigm, applications are inordinately unreliable and expensive to maintain and upgrade. In many software shops, developers live in constant fear of breaking their apps in non-recoverable ways.

We can approach the ideal world in which the developer spends time only on the aspects of each project that are truly unique to the project. The potential cost savings and vastly expanded potential for new development could be staggering. Imagine an environment in which new applications can be built in perhaps less than half the time and cost than we spend now.

Practical basics of simplifying applications

In this article series we will focus on the first four items listed above. We will address these items by:

  1. defining a very simple, standardized way of constructing databases;
  2. creating a code generator capable of creating routines that handle all the CRUD and auditing functions within the database;
  3. building an ultra-small, ultra-simple object-relational-mapper (ORM) that fully automates the interaction between the database and the application,
  4. and defining a standard design for implementing basic business-rule CRUD.

Item A above is the core of the standardization process, because it makes all the others possible. This discipline rewards us with much more than the simple reduction of development cost and time. Almost for free, we automatically also:

  • implement "ideal" auditing, where the app itself can recall all versions of the data that were ever entered into it;
  • cascade deletions properly, so that applications can confidently and correctly delete data (rather than producing errors, deleting too little, or deleting too much) and we can give the app user the option to perform cascades; and we can delete fearlessly, since every version of every record ever created is preserved by the auditing mechanism;
  • automatically test and correct for concurrency problems (where two or more users try to simultaneously modify the same data);
  • vastly improve the quality of database-related error messages that end-users receive - to the point where most error messages are essentially all in user-comprehensible English;
  • and dramatically improve overall application reliability, to the point where almost no testing of basic functionality is needed.

Since many if not most development projects never get around to any of this stuff due to time and cost constraints, this constitutes quite a bonus.

Standardizing database construction

From this point on we are going to simply present some design principles without justifying them individually. Many of these notions are controversial and have been argued for decades. Our justifications for our choices cannot be argued at the individual level, just as Henry Ford could not justify his choices at the component level. Rather our choices are justified by the capabilities of apps we generate and the reduction of effort we achieve in producing them.

Discipline #1: Keys

It is possible to totally standardize keys by making them all identical - and the way to do this is to make them all synthetic (i.e. auto-increment integers or GUIDs). So let's require all keys to be auto-increment long integers ("bigints"). Synthetic keys are the "key" (pun intended) to generating simple, standardized CRUD routines and writing a massively simplified ORM. So all primary and foreign keys in a RAP application are single-field "bigints".

Discipline #2: Status Fields

Rather than trying to micro-optimize individual tables' status fields based upon expected usage, we will require that all tables (no matter how few columns of "real" data they contain) will have precisely the same set of status fields, and that these fields will be interpreted in precisely the same way in every table.

Discipline #3: Modeling relationships

All relationships will be ultimately be modeled using only two mechanisms:

  • Many-to-one, in which a parent (the "one") has zero or more children (the "many"). One-to-one relationships are a degenerate case of this.

  • Many-to-many, in which two peers are joined by a link table containing one foreign key pointing to each peer. The table may contain more fields representing data that is endemic to the relationship (rather than to either peer).

Generating your table definitions automatically

RAP cannot design your schema for you, but once you've designed your schema (following the rules above), it will help you generate your table definitions in a totally uniform way. In order for this tool to work, you need to follow some simple naming conventions.

The RAP programmer defines tables by putting a series of table definitions into an SQL file. Here is an example of a single table definition

-- User
create table TBadmUser
	--#include "PrimaryKey.sql"
	-- indexed fields
	LoginName	DRLoginName,
    	constraint	UK_TBadmUser_LoginName unique nonclustered (LoginName),
	--#include "CommonFields.sql"

This simple table contains only one "real" field in addition to the standard primary key and audit fields that adorn every RAP table. The "#include" directives are similar to C-preprocessor directives that cause the contents of the named file to be included in-line, allowing one copy of common code to be replicated perfectly (these are discussed in detail below).

Database entity naming conventions

RAP tables follow the following conventions:

  • Every table name starts with the capital letters "TB".
  • Following the initial characters are a two-to-four character lower-case word or abbreviation indicating the category of the table. The names and the categorization are left to the discretion of the developer. Examples:
    • "adm": administrative tables
    • "sec": security-related tables
    • "crm": customer-relation-management tables
  • Following the lower-case mid section is a leading-cap name that basically defines the contents of the table.

In the example "TBadmUser" table above, the name has the following three sections:

  1. "TB" - standard prefix for all tables;
  2. "adm" indicates that this is an administrative table (at the discretion of the implementer);
  3. "User" indicates that this table contains user-related info..

RAP tables define their fields in the following order:

  1. Primary key (via an "include" as shown above);
  2. Foreign keys, which are always a single field and whose associated constraint name is of the form "FK_<ThisTableName>_<TargetTableName>;
  3. Indexed fields (that are neither primary nor foreign keys):
    1. Single-field constraint names are of the form "UK_<ThisTableName>_<FieldName>
    2. Multi-field constraint names are of the form "UK_<ThisTableName>_<Description>, where <Description> is at the developer's discretion;
  4. Non-indexed fields;
  5. Standard audit fields (via an "include" as shown above).

A more complex table definition that contains four of the five categories above is:

-- State/Province
create table TBcrmStateProvince
	--#include "PrimaryKey.sql"
	-- foreign keys
	CountryId	DRBigId,
	constraint	FK_TBcrmStateProvince_Country
	    foreign key (CountryId) references TBcrmCountry (CountryId),

	-- indexed fields
	Code		DTStateCode,	-- optional because provinces may not have codes
	constraint	UK_TBcrmStateProvince_Code unique nonclustered (CountryId, Code),

	Name		DRDescription,	-- required
	constraint	UK_TBcrmStateProvince_Name unique nonclustered (CountryId, Name),
	--#include "CommonFields.sql"

By convention, RAP defines a set of SQL types that are used in the definitions of primary keys and audit fields. These data types may be used by developers in defining the developer-supplied fields of tables (as shown in these examples here). Such definitions can be found in a file called DataTypes.sql in the DDL section of the example RAP code. These data types follow this naming convention:

  • Type names start with one of two prefixes:
    • "DR": denotes a required (non-nullable) data type;
    • "DT" denotes an optional (nullable) data type.
  • The remainder of a type name is a descriptive name that is left to the discretion of the developer.

For a more complete example of the complete structure of a RAP source file that defines tables, see the example "Tables.sql" file that is provided with RAP.

The Database Preprocessor

RAP comes with a Database Preprocessor that substitutes the "#include" directives in the examples above with their corresponding file contents. The preprocessor performs the following macro-like substitutions within the text of the included files:

  • instances of the string "$[TableName]" are replaced with the name of the table (if any) within whose definition the directive occurs;
  • instances of the string "$[TableSuffix]" are replaced with the suffix (ending) portion of the enclosing table name (e.g. in "TBadmUser" this would be "User").

Instructions for running the preprocessor are included in the RAP demo package.

The two files "PrimaryKey.sql" and "CommonFields.sql" in the provided example code show examples of the use of these two substitutions. Here we show the output code generated by the definition of the small table "TBadmUser", whose source code was shown above:

-- User
create table TBadmUser
	--#include "PrimaryKey.sql"

	-- Primary Key
	UserId		DRBigId identity(1,1),
	constraint	PK_TBadmUser primary key clustered ( UserId asc ),
	-- indexed fields
	LoginName	DRLoginName,
    	constraint	UK_TBadmUser_LoginName unique nonclustered (LoginName),
	--#include "CommonFields.sql"

	-- Note Fields
	Notes		DTTextMax,
	-- Audit Fields
	AuditDate			DRDateTime default current_timestamp,
	AuditUserId			DRBigId default 1,
	constraint			FK_TBadmUser_AUI foreign key (AuditUserId) references TBadmUser (UserId),
	AuditStatus			DRCodeSmall default 'I'

The black code in bold here was inserted by the preprocessor. (Unlike with the C preprocessor, the RAP preprocessor leaves #include directives in line with the code that they include.)

By requiring every table definition to begin with directives that define the table's primary key and common fields (including audit fields), total consistency in primary key and common field definition is achieved, so that every RAP table has the same type of primary key and the same set of status fields.

The output of the preprocessor can be placed directly into an SQL Server query window and executed, producing the application's tables.


In a very short article we've described the foundation of RAP design: consistent table structure. This is like the foundation of assembly-line auto manufacture: you use the same parts in every car, no exceptions. In our next couple of articles we'll see the amazing amount of database code generation that we can do as a result.

The Series

This is part of a series that examines the RAP application development system and a philosophy that believes in more standardization for both our database development and application organization.

  1. Part 1 - An Introduction
  2. Part 2- Database Design
  3. Part 3 - Auditing
  4. Part 4 - Insert/Update/Delete Routines
  5. Part 5 - Fetch Routines

Total article views: 10468 | Views in the last 30 days: 1
Related Articles

Developer Deployment Frustrations

SQL Server should work to make it easy for developers to work with it, and include versions like Exp...


Database Design for Blog application

Database Design for Blog application


Application Developers don’t own their data

As a data guy, I always smile when application developers refer to ‘their’ data. If only it were tha...


URGENT - SQL Database Development Manager required! London

URGENT - SQL Database Development Manager required! - London


Is Your Database Application DeadLock and Timeout Resistent?

Is your application scalable under increased activity? Timothy Claason brings us a methodology for t...