In our previous article you downloaded the
system, which included a sample application called ExampleCRM. Hopefully you
noticed a few very interesting things about this four-tier application:
- There is no hand-written code in the database.
- There is no hand-written code in the data layer except for a few token
- The business rules section contains absolutely all the business logic
and it's based on a highly structured pair of templates.
- 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
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:
- reinventing data modeling (i.e. choosing from among too many modeling techniques)
- reinventing database access on the database side
- reinventing database access on the application side
- reinventing business rule framework technology
- diddling with endless details of UI component management
- 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:
- defining a very simple, standardized way of constructing databases;
- creating a code generator capable of creating routines that handle all
the CRUD and auditing functions within the database;
- building an ultra-small, ultra-simple object-relational-mapper (ORM)
that fully automates the interaction between the database and the
- and defining a standard design for implementing basic business-rule
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 itselfcan 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
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
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
- "TB" - standard prefix for all tables;
- "adm" indicates that this is an administrative table (at the discretion
of the implementer);
- "User" indicates that this table contains user-related info..
RAP tables define their fields in the following order:
- Primary key (via an "include" as shown above);
- Foreign keys, which are always a single field and whose associated
constraint name is of the form "FK_<ThisTableName>_<TargetTableName>;
- Indexed fields (that are neither primary nor foreign keys):
- Single-field constraint names are of the form "UK_<ThisTableName>_<FieldName>
- Multi-field constraint names are of the form "UK_<ThisTableName>_<Description>,
where <Description> is at the developer's discretion;
- Non-indexed fields;
- Standard audit fields (via an "include" as shown above).
A more complex table definition that contains four of the five categories
-- 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
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
Instructions for running the preprocessor are included in the RAP demo
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.
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.
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.