Minimalist Meta-Model for CRUD Applications

Introduction

The discrepancies between object and relational models has been long noted, and referred to as the object-relational impedance mismatch.

What's more rarely noted however is how simple applications, often referred to as CRUD (Create, Read, Update, Delete) are effectively demonstrating the stunning resemblance between the relational models and the UI structures. A trivial remark lies in the correspondence between DB operators and CRUD functions: Create/Insert, Read/Select, Update/Update, Delete/Delete.

The main idea of this article is to explore a model of UI components that would make the impedance mismatch moot by linking directly the UI model to the database without going through any intermediate object representation.

We argue that the key concept here is the representation of UI Metadata and that with a proper set of fundamental UI widgets, most CRUD applications can be effectively designed without hand coding. The first step to automated code generation of full-blown application seems indeed to show that simple applications (CRUD in our example) can be automatically build (even on the fly).

In this article we will take as an example a "to do list" application and gather all the meaningful information necessary to fully describe the UI and database mapping necessary to add, view, edit, delete, and search records. This information is called UI metadata (data about the UI data) and its structure is called meta-model (model of model, or model of metadata).

Ideally, the metadata should only contain what is different from one CRUD application to another, with as little redundancy as possible. What is the same across all application should be re-usable code.

The different issues we need to consider for a meta-model are:

• Database mapping
• UI forms
• Field types
• Elements behavior
• Elements positioning
• Elements appearance

To paraphrase Eric. S. Raymond original quote from The Cathedral and the Bazaar, "smart data structures and dumb code works a lot better than the other way around", in our case, we could say "Smart meta-data structures and dumb code generation works a lot better than the other way around" ;-)

The meta-model is the structure of the applications definition; therefore decisions made there have incredible consequences on the volume and complexity of the code.

Database Mapping

Database mapping for code generation can become a very complex task when mapping all possible data structures and types of joins. We will not try to re-invent Hibernate but just look at the minimal metadata needed to generate all necessary SQL for CRUD.

In order to keep the metadata simple, we can use conventions and stick to as few patterns as possible. In the limited scope of CRUD, we can consistently call primary keys "ID" and define them as columns of type integer or long, not null, and auto-increment. Of course, other naming conventions and techniques are possible, you may choose to call your primary keys "<table name>ID" and not set them as auto-increment but that will eventually result in more code. What is important is to stick to a convention so that we will not have to define primary keys and joins in the metadata for each entity or to code all possible types of joins.

For our "to do list" example we will need a database schema like the following:

To manipulate the data in the former schema, different SQL syntaxes are possible, and the SQL could be generated on the fly or coded once in stored procedures. We will basically need SQL code like the following:

Create:
INSERT INTO EVOL_ToDo (ID, Title, Duedate, PriorityID, CategoryID, Complete, Notes)
VALUES (...)

Read:
SELECT t.ID, t.Title, t.Duedate, t.PriorityID, t.CategoryID, t.Complete, t.Notes, EVOL_ToDoPriority.name AS Priority, EVOL_ToDoCategory.name AS Category
FROM EVOL_ToDo t left join EVOL_ToDoPriority on t.PriorityID=EVOL_ToDoPriority.ID left join EVOL_ToDoCategory on t.CategoryID=EVOL_ToDoCategory.ID
WHERE ...
ORDER BY ...

Update:
UPDATE EVOL_ToDo
SET Title=..., DueDate=...
WHERE ID=...

Delete:
DELETE FROM EVOL_ToDo
WHERE ID=...

In addition, we will also need the following two simple queries to populate our drop downs of tasks categories and priorities with their lists of values:
SELECT ID, name FROM EVOL_ToDoPriority ORDER BY ID
and
SELECT ID, name FROM EVOL_ToDoCategory ORDER BY name

In order to generate all that SQL, we will only need the following information (remember we decided that ID would always be the primary key):

So far, all this metadata is available directly from the database there is no need yet for external mapping. It may be completed with stored procedure names for all database operations. In order to build the corresponding user interface more metadata will become necessary. Then it will make sense to have a separate structure (meta-model) to store our metadata.

UI Forms

In order to interact with our database tables, we will need several different forms (often called modes or views). These are the same for all CRUD applications: List, View, Edit, and Search... and maybe Advanced Search and Export.

List Displays the most important fields for multiple rows of records.	View Displays all fields for viewing 1 record at a time.
Edit Displays all fields for editing 1 record at a time.	Search Query by example form for searching records.
Export Export of selections in standard formats (Excel, HTML, CSV, XML, or SQL), one or several records at the same time.	Advanced Search Advanced form for queries such as "phone number start by 415" or "categories equal Finances or Business"...

In most web applications today each mode is a different web page. In recent Web 2.0 rich internet applications the modes Export, Search and Advanced search are often modal dialogs. Also, we sometimes see applications with a single page made of 2 different frames for the List mode and the Edit or View mode (Microsoft Outlook style).

Whether you plan on using one page per mode, or have "the page be the application", the different modes are still there and the same proposed meta-model (and same models) can be used to describe it.

Field Types

For each field, the model should not describe every detail for every mode but use patterns. For example, a date field will appear as a text box with a date picker button in Edit mode but it will appear as a single string in View mode. Defining a field as "date" should be enough, there is no need to describe the text field, the button, and the popup calendar for every date field.

Field types are no longer just data types but become "behavioral types" or "UI types". For example: fields for a URL, an email, or a phone number are of different types for the user as they behave differently, but they have the same data type for the database (varchar or nvarchar). We need patterns associated to each field type.

The list of field types should at least include the following: boolean, date, datetime, decimal, document, email, formula, image, integer, lov (list of values), text, textmultiline, time, and url.

boolean	Boolean fields are Yes/No values displayed as checkboxes. A Boolean field is stored as a numeric value (Yes=1, No=0 or null). The most efficient database column type for it is bit. edit view list search adv. search
date, datetime, time	Dates are displayed as input boxes with a date picker in edit mode, and as a formatted string in other modes. Possible database column types are datetime or smalldatetime. edit view list search adv. search
decimal, integer	These types are used for numeric values. Decimal can be stored as data type money or decimal. Integer can be smallint, int, bigint… edit view list search adv. search
email, url	Text value displayed as a text box in edit mode and hyperlink in other modes. These can be stored as varchar, or nvarchar. edit view list search adv. search
formula	Formula fields are calculated values that are not persisted in the database. Calculation could be done by the database or the code. Fields of type formula are read-only and depending on the implementation may not be searchable. Of course, fields of this type will also need additional metadata to specify the formula itself. A simple example would be "Contact name" as the concatenation of "Lastname", comma, and "Firstname". edit view list search adv. search
image	Images are displayed as such in view mode, as a box with a browse button for upload in edit mode, as a checkbox in the search and advanced search modes. Depending on your implementation, images could be stored on the file server (with the filename stored in the database, as a varchar or nvarchar) or directly in the database (as an image column). edit view list search adv. search
text	Text is obviously the most commonly used data type. It is displayed as a text box in edit mode. It is stored as varchar or nvarchar. edit view list search adv. search
textmultiline	Fields of these types are displayed as big text boxes (HTML tag "textarea") and can spread over several rows. These can be stored as text, varchar, or nvarchar. edit view list search adv. search
lov (list of values)	Lists of values are choices of values displayed as drop-down lists in edit mode and as the string of the selected value in view mode. These correspond to joins to secondary tables in the database and are usually stored in the driving table as a number which is the value of the secondary table primary key. edit view list search adv. search
document	Documents are displayed as a link for download in view mode, as a text box with a browse button for upload in edit mode, as a checkbox in the search and advanced search modes. Like images, documents can be stored on the file server or in the database. edit view list search adv. search

The former list is what I believe to be the minimal set of field types necessary to build applications. Of course, more field types could be added if needed in your application. For example IP address, color, data-size, temperature, or rich text... could be nice to implement as special widgets. SalesForce offers specific field types for "Percent", and "Phone"; it also provides field types for relationships but these could also be considered "special list of values".

You may implement slightly different patterns. The nice thing is that if you build a metadata driven application you will only need to change the code once for all pages to change accordingly. If you use a Full Text Search, you may want to add "contain word". Also, I voluntarily omitted "Is null" and "Is not null" in the advanced search mode because in applications I have build without it, I have never got the enhancement request from any user yet.

Most fields of types map to a single database column but list of values (LOV) fields map a key column in the driving table and need a join to a secondary table. Fields of type "formula" may map to a sub-query or be calculated by the database or the UI depending on the implementation.

For our "to do list" example, we will need the following fields:

• Title (text)
• Due date (date)
• Priority (list of values)
• Category (list of values)
• Complete (boolean)
• Notes (multiline text)

Elements Behavior

The toolbar for any kind of CRUD application will be the same by definition. Maybe we should call it SCRUD, with an additional "S" for "Search". It may also have a "clone" button that can be quite useful for data entry. Some of the icons may be repeated in the web form as buttons or links.

A lot of the client interaction code can be generic and should not require any additional metadata. For example panels can be collapsible using the same code for a to do list or a contact management application.

The field types already imply quite some behavior but we still need to introduce more metadata for each field:

• Validation rules: Required, Min, Max, RegExp, MaxLength...
• Security: ReadOnly
• Flagging appartenance to modes: all fields will be in the Edit and View modes, but the modes Search, Advanced Search, and List should only contain a sub-set of the fields.
• Altering the appearance or behavior of specific fields sometimes in specific modes: label-edit, label-search, help, optional...

Adding behavioral information to our "to do list" example, we now have the following fields definitions:

• Title (text) : required, maxlength = 255 characters
• Due date (date)
• Priority (list of values) : required
• Category (list of values)
• Complete (boolean) : labellist="C." (use abbreviated label in lists)
• Notes (multiline text) : maxlength = 1000 characters, height = 6

Elements Positioning

With the metadata we now have gathered for our example, and the field type patterns, we can already present an Edit form as follow.

As long as we do not have too many fields on the screen this is perfectly fine. For applications with a large number of fields, this layout involves a lot for vertical scrolling for the user. Also, it is much easier for users when fields are visually grouped into panels on the screen (maybe tabs too, which in the metadata could simply be considered a different type of panels).

The most common positioning strategy is to decide on a specific number of columns for the whole page (with labels on top of each field or on the left). This is not ideal in cases like addresses where it is much nicer to display a different number of fields on each row and have field of different width on the same row like the following:

Let's go through the exercise with our to do list example. Assuming we could position elements as we please, we could organize fields into 2 or 3 groups. It may seem trivial but I'll say it anyway because it is important and not always done: the grouping of fields should match the user mental model not the developer's.

2 panels organization: Task and Status

or 3 panels organization: Task, Status, and Notes

Several different ways to specify elements position on the screen are available. We could specify the top and left coordinates for each element, but each time we remove or add an element we would have to change coordinates of other elements to re-organize the form.

My favorite positioning scheme is the "flow positioning" because it only requires one variable per element: its width. Also, when adding or removing elements, there is no need to make any change to other elements. In addition, it can be implemented in a "forgiving way" where any value greater than 100% will be considered as 100%; a field of width less than 100% between two fields of width 100% will also behave as 100%. Also, it supports cases like the earlier address example.

Fields and panels (groups) organization is described the same way. Elements are placed sequentially, left to right, until a width of 100% is reached. Once the maximum width is reached, the next panel will appear below the previous group of panels.

Choosing the 3 panels presentation, and using the "flow positioning" scheme we have the following description:

Panel: Task 62%
• Field: Title 100%
• Field: Due date 40%
• Field: Priority 60%

Panel: Status 38%
• Field: Category 100%
• Field: Complete 100%

Panel: Notes 100%
• Field: Notes 100%

Notice how minimalist this scheme is: by keeping the structure of the metadata (elements order and nesting of fields into panels), and using 100% as the default width, we only need 4 numbers to position all elements (fields and panels) on the form.

The Edit and View mode follow the same positioning. For other modes, fields appear or not based on the attribute search for search, searchadv for advanced search, and searchlist for list. All fields present in the metadata will appear in the export mode.

By the way, I choose a 62%-38% proportion instead of a more typical 50%-50% because it is the golden ratio which is recognized as more aesthetical. If it works for ancient Greek architects, Da Vinci, and the French impressionists, it should also work for application screens... and it's not any more difficult.

Elements Appearance

In web applications today, elements appearance are almost always set using CSS. We can simply define a default CSS class for all fields, and another one for read-only fields. Of course, it is possible and sometimes necessary to go further and have different classes for different types of fields, or in different modes.

At the minimum we should have the following CSS classes:

There is no need to specify in the model any of the CSS class that will be the same everywhere like toolbar, buttons... We can use a set of default classes and only specify the CSS class of element (panel, field, field label) we want to differentiate.

In our example, we will change the class name of the field "Title" from the default "field" to "fieldMain" in order to make it stand out.

Keeping it Simple

So far, we looked at what needed to be accounted to describe CRUD applications. We could now decide to have one XML document for the database mapping (similar to Hibernate's), and one XML document for each UI form (refering the database mapping document by field/property name). This may be needed for complex applications but in the scope of CRUD we can "collapse" all these documents into a single one.

Keeping the number of XML documents as low as possible, and avoiding redundancy will make maintenance easier and avoid many potential "metadata bugs" caused by typos or mismatches across documents.

We can now define the elements of the Edit form of our "to do list" application, add to each field its database mapping information, and flag fields we want in the Search, Advanced search, and List forms. That will give us a single XML document like the following which can be used to generate all SQL, and all UI forms.

<?xml version="1.0" encoding="UTF-8"?>
<form label="To Do"  xmlns="http://www.evolutility.com">
  <data entity="task" entities="tasks" icon="m-todo.gif"
            dbtable="EVOL_ToDo" dborder="PriorityID, duedate" />
    <panel label="Task" width="62" >
        <field type="text" label="Title" 
            dbcolumn="title"
            required="1" cssclass="fieldmain" maxlength="255" width="100" 
            search="1" searchlist="1" searchadv="1" />
        <field type="date" label="Due Date" 
            dbcolumn="duedate" 
            maxlength="10" width="40" 
            search="1" searchlist="1" searchadv="1" />
        <field type="lov" label="Priority" 
            dbcolumn="PriorityID" dbcolumnread="Priority" 
            dbtablelov="EVOL_ToDoPriority" dbcolumnreadlov="name" dborderlov="ID"
            required="1" width="60"  
            search="1" searchlist="1" searchadv="1" />
    </panel>
    <panel label="Category" width="38">
        <field type="lov" label="Category" 
            dbcolumn="CategoryID" dbcolumnread="Category" 
            dbtablelov="EVOL_ToDoCategory" dbcolumnreadlov="name" dborderlov="name"
            width="100" 
            search="1" searchlist="1" searchadv="1" />
        <field type="boolean" label="Complete" labellist="C." 
            dbcolumn="Complete"
            width="50" img="checkb.gif"  
            search="1" searchlist="1" searchadv="1" />
    </panel>
    <panel label="Notes" width="100">
        <field type="textmultiline" label="" labeledit="Notes" labellist="Notes" 
            dbcolumn="Notes"
            maxlength="1000" width="100" height="6"  
            search="0" searchlist="0" searchadv="1" />
    </panel>
</form>

Try the "to do list" application generated from that metadata live on Evolutility.org.

Meta-model for CRUD

Let's now gather an exhaustive list of the metadata encountered in the previous paragraphs and come up with a formal language to fully describe CRUD applications. We will also add information that was not needed in our example but will become handy for more complex applications.

As we just saw earlier, a CRUD application can be defined by a single XML document. This XML document is composed of one form element containing one data element, and one or more panel elements containing one or more field elements.

form element

form is the root element. It doesn’t represent one single web form, but all necessary web forms (Edit, View, List, Search, Advanced Search, Export) at once. Fields are displayed or not on each web form based on the attributes search, searchlist, and searchadv.

The form element contains one element data and one or more elements panel.

data element

The data element specifies the set of database objects used by the component: driving table, stored procedures; and the icon and screen name associated.

panel element

The panel element is used to visually group fields together on the screen.

field element

The elements field represents fields on the screen, and database columns at once. It is the most used element and the element with the most attributes. Database columns hidden to the user (like the primary key of the driving table) are not declared.

Storing the Metadata

Now that we have a meta-model, we need to store the metadata. Naturally, we have the 2 common options: XML or database. This is where we rip some of the benefits of a simple meta-model: the simpler meta-model the simpler structure for the XML or the database dictionary.

Using XML Documents

• Pros: flexible, self contained, easy to install, easy to modify.
• Cons: sometimes redundant across documents.
• Nice to have: An XSD for validation.

Using a Database Dictionary

• Pros: centralized, can mix data and metadata (even though it can be dangerous), can use the same application to edit the data and the metadata.
• Cons: overhead of a database dictionary, difficult to edit without custom tool.
• Must have: A database schema (a simple one below).

The former schema could be enhanced by spliting the "EvoDico_Field" table into 2 separate tables, one for the database mapping and one for the UI information of each field. This will be especially helpful when the same table is accessed by different groups of users and some users are restricted to sub-set of the fields.

About the Implementation

The proposed meta-model is relatively simple, ideal for CRUD, and all its patterns work together. Other meta-models, and different sets of patterns are definitly possible. Depending on the level of control and customizability you want to achieve, you may want to use a more complex ones. There are many advantages to keeping it simple. That way, non-programmers can look at an application description and make modifications without having to go through a big learning curve or read the doc (as we know most people don't).

A working implementation of this meta-model is available with sample applications in my other articles on the fly code generation with Evolutility and Wizard and CRUD applications to build other CRUD applications.

The most interesting question about metadata driven applications may be about when to perform code generation. Should we use the meta-model to generate code at design time (then compile and run) or can we generate the code at run-time?

Updates and more sample applications are downloadable at www.evolutility.org. You are welcome to look at the latest code (open source under Affero GPL v3 with dual licensing) at SourceForge. Enjoy... maybe improve it and contribute your code.