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
LoginNameDRLoginName,
    constraintUK_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
CountryIdDRBigId,
constraintFK_TBcrmStateProvince_Country
    foreign key (CountryId) references TBcrmCountry (CountryId),
-- indexed fields
CodeDTStateCode,-- optional because provinces may not have codes
constraintUK_TBcrmStateProvince_Code unique nonclustered (CountryId, Code),
NameDRDescription,-- required
constraintUK_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
UserIdDRBigId identity(1,1),
constraintPK_TBadmUser primary key clustered ( UserId asc ),

-- indexed fields
LoginNameDRLoginName,
    constraintUK_TBadmUser_LoginName unique nonclustered (LoginName),
    
--#include "CommonFields.sql"
-- Note Fields
NotesDTTextMax,

-- Audit Fields
AuditDateDRDateTime default current_timestamp,
AuditUserIdDRBigId default 1,
constraintFK_TBadmUser_AUI foreign key (AuditUserId) references TBadmUser (UserId),
AuditStatusDRCodeSmall 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.

Summary

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