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 groups
- 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" https://www.codeproject.com/script/Forums/Images/smiley_wink.gif
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 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 use primary keys on columns called "ID" 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 and metadata (primay key column name, data type, increment...). 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:
INSERT INTO EVOL_ToDo (ID, Title, Duedate, PriorityID, CategoryID, Complete, Notes)
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
ORDER BY ...
SET Title=..., DueDate=...
DELETE FROM EVOL_ToDo
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
SELECT ID, name FROM EVOL_ToDoCategory ORDER BY name
In order to generate all that SQL, we will only need the following information (assuming all tables use "ID" as primary key):
- ID - Primary key - int
- Title - nvarchar
- Duedate - datetime
- PriorityID - int (LOV table = EVOL_ToDoPriority, display column = name, alias = Priority, sorted by = ID)
- CategoryID - int (LOV table = EVOL_ToDoCategory, display column = name, alias = Category, sorted by = name)
- Complete - bit
- Notes - nvarchar
|Default list order:||Duedate, PriorityID|
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.
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 a few extra form like Advanced Search, Charts, Mass Update, Export and Saved selections.
Displays all fields for viewing, 1 record at a time.
Displays all fields for editing, 1 record at a time.
Displays the most important fields for multiple rows of records.
Draws a few charts of the data.
Export of selections in standard formats (Excel, HTML, CSV, XML, or SQL), one or several records at the same time.
Query by example form for searching records.
Update for multiple records at once.
Form for advanced queries such as "phone number start by 415" or "for categories Finances and Business"...
List of canned queries.
User Login screen.
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.
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 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.
date, datetime, time
These types are used for numeric values. Decimal can be stored as data type money or decimal. Integer can be smallint, int, bigint…
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 are stored on the file server and only the filename is stored in the database.
Text value displayed as a text box in edit mode and hyperlink in other modes. These can be stored as varchar, or nvarchar.
SQL formula or sub-query. The calculation SQL is entered in the dbcolumn attribute of the field. Fields of type formula cannot be edited by users.
Example of formula field:
<field type="formula" readonly="1" label="Photos"
format="0 'photos'" dbcolumnread="NBphotos"
dbcolumn="SELECT COUNT(*) FROM EVOL_Photo P WHERE
P.albumid=T.id" ... />
html (rich text format)
The "html" field type is used to display Rich Text Format (RTF) or HTML. It uses TinyMCE widget for WYSIWYG edition in the browser.
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. Images are stored on the file server, only the filename is stored in the database, as a varchar or nvarchar.
lov (list of values)
Lists of values are choices of values displayed as drop-down lists in edit mode or as the string of the selected value in view mode. They correspond to joins to secondary tables in the database and are stored in the driving table as a number which is the primary key of the value in the secondary table.
Using certain attributes of the field it can become a many-to-many relationship instead of a one-to-many.
This type is the most commonly used one. It is displayed as a text box in edit mode. It is a string stored as varchar or nvarchar.
Fields of these types are displayed as big text boxes (HTML "textarea") and can spread over several rows. They can be stored as text, varchar, or nvarchar.
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".
It is also useful to add "Is null" and "Is not null" (maybe called "Is empty" and "Is not empty" depending on your users) in the advanced search (it is only useful for required fields).
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)
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 belonging 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
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. Labels can be on the left of fields (in their own column) as seen in the previous screenshot, or on top of each field as in the next screenshot. 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%
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 to code than other proportions.
Standard ratio: 50% - 50%.
Golden ratio: 62% - 38%
We can apply the golden ratio in a recursive or fractal way by also applying it for fields on 2 columns of 62% and 38% inside a panel. The difference is subtle but at the unconscious level users should feel more confortable with proportions which remind them of their environment or their body.
Field labels positioning
In his latest book Web Form Design: Filling in the Blanks, Luke Wroblewski dedicates a full chapter to labels positioning. In order to improve usability, I decided to follow his guidelines.
One of the best practices he proposes (and I hadn't thought of before) is: "When considering different label alignments for different forms in a single application, think through the context versus consistency trade-off".
As a result, I used different labels positioning scheme for the different forms as follows:
- Top-aligned labels for View & Edit forms because they "labels and input fields are n close proximity, processing them requires little effort".
- Left-aligned labels for Search & Advanced Search forms because they "make scanning the information required by a form easier".
- Labels with inputs for Export form
As we saw earlier, when we have a lot of field it becomes useful to group them into panels. Now, when we have too many panels to fit the screen without scrolling, we should group them into tabs. Tabs can save a lot of screen real estate and help users not to be overwhelmed by too much information at once.
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:
For fields not passing validation we should add a "FieldInvalid" CSS class. By using it in conjonction with the "Field" CSS class, we can reduce the potential number of CSS classes needed.
There is no need to specify in the model any of the CSS class for elements which stay the same for every application like toolbar, buttons, field labels... Actually, by using a set of default CSS classes and only specifying the classes of element we want to be special, we are saving a lot of time and effort but also enforcing look & feel consistency in the application.
In addition, we should add to the metadata a way to specify the file name of the entity icon.
In our example, to make it stand out, we will change the class name of the field "Title" from the default "Field" to "FieldMain". We will also specify an icon for the entity.
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.
<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"
required="1" cssclass="fieldmain" maxlength="255" width="100"
search="1" searchlist="1" searchadv="1" />
<field type="date" label="Due Date"
search="1" searchlist="1" searchadv="1" />
<field type="lov" label="Priority"
dbtablelov="EVOL_ToDoPriority" dbcolumnreadlov="name" dborderlov="ID"
search="1" searchlist="1" searchadv="1" />
<panel label="Category" width="38">
<field type="lov" label="Category"
dbtablelov="EVOL_ToDoCategory" dbcolumnreadlov="name" dborderlov="name"
search="1" searchlist="1" searchadv="1" />
<field type="boolean" label="Complete" labellist="C."
search="1" searchlist="1" searchadv="1" />
<panel label="Notes" width="100">
<field type="textmultiline" label="" labeledit="Notes" labellist="Notes"
maxlength="1000" width="100" height="6"
search="0" searchlist="0" searchadv="1" />
Also, I find it very useful to have good default values. That way things can be changed if needed, but users can rely on default value and not know about it until needed. It doesn't simplify the metamodel but can make the models smaller.
Try the "to do list" application generated from that metadata live on Evolutility.org.
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
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. It has the optional attributes
version, and the required namespace
form element contains one element
data and one or more elements
panel.nFields are displayed or not on each web form based on the attributes
data element specifies the set of database objects used by the component: driving table, stored procedures; and the icon and screen name associated.
|entity||User's object name for the database object (for example: "task" for a to do list application).|
|entities||Plural for |
entity (example "tasks" for the to do list).
|icon||Filename of the records icon (same one for all records).|
|dbcolumnlead||Database column used as record identifiyer for the user (not the primary key).|
|dbcolumnpk||Name of the primary key column used as record identifiyer.|
|dbtable||Name of driving table for the application.|
|dbwhere||SQL where clause to limit the dataset manipulated.|
|dborder||List of column names to include in the "order by" SQL clause. It is the default sort option.|
|spdelete||Name and parameters of the stored procedure for deleting records (or flagging them as deleted).|
|spget||Name and parameters of the stored procedure for retrieving a specific record.|
|splogin||Name and parameters of the stored procedure for checking users' credentials. Example: splogin="EvoSP_Login @login, @password"|
|sppaging||Name and parameters of the stored procedure for paging search results.|
panel element is used to visually group fields together on the screen.
|cssclass||CSS class for the panel. The default value is "Panel".|
|cssclasslabel||CSS class for the panel title. The default value is "PanelLabel".|
|optional||Skips the panel from displaying, if every field contained is empty and optional (in View mode only).|
|width||Width of the panel in percentage of the total width of the form.|
width="100" for the full width.
tab element can be used to group panels together and view them a group at a time. This element is optional.
|cssclass||CSS class for the tab. The default value is "Tab".|
|cssclasslabel||CSS class for the tab title. The default value is "TabLabel".|
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.
|cssclass||CSS class name for the specific field (if we want to over write the default). |
|cssclasslabel||CSS class name for the specific field label (in modes Edit and View). The default value is "FieldLabel".|
|dbcolumn||Database column (SQL name) for the field.|
|dbcolumnread||Database column alias. Only useful for field of type LOV, otherwise |
dbcolumnread must be the same as
dbcolumn for the field.
|dbcolumnreadlov||Column to show as value in lists. Default value is "name".|
|dborderlov||Column name (or coma separated list of column names) to sort the values in the drop downs.|
|dbtablelov||Database table with the list of possible values for the drop down.|
|dbwherelov||Extra SQL where clause to limit the list of value.|
|defaultvalue||Default value for the field displayed while creating a new record.|
|format||Field format (for fields of type boolean, date, decimal, or integer).|
|height||Height of the field, in number of rows (default to 1 for all field except fields of type TextMultilines).|
|help||Help tooltip for the field (only used in Edit mode).|
|img||Image to display (for fields of type "boolean" or "url") in Edit or View modes.|
|Field title (in different modes). Only |
label is mandatory and will be the default for all other modes if not specified.
|link||Forces the field to be displayed as a link to another web page. It can use the following variables @itemid, @userid, @fieldid.|
|linklabel||Display a sentence or an image as the link. @fieldvalue in the string is replaced by the field value at runtime.|
|linktarget||Direct link click to a new browser.|
|max ||Maximum value allowed for the field.|
|maxlength || Maximum number of characters allowed for the field value.|
Minimum value allowed for the field.
|minlength || Minimum number of characters allowed for the field value to be valid.|
|optional||Determines if the field is displayed when empty (apply to View mode only).|
readonly=1 presents edition of the field.
readonly=2 presents edition of the field, but allows typing in insertion.
|regexp||Regular expression to validate the field value against.|
|search||Determines if the field appear in the search form.|
|searchadv||Determines if the field appear in the advanced search form.|
|searchlist||Determines if the field appears as a column of the search results list.|
|type||The type of the field. As we saw earlier, possible values are: boolean (yes/no), date, datetime, decimal, document, email, formula, image, integer, lov (list of values), text, textmultiline, time, or url.|
Width of the field in percentage of the Panel it belongs to.
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.
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).
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 and have the application modify itself?
The UI is described in a way that can be implemented on any platform.
Implementation as an ASP.net web control
Live sample applications are available at www.evolutility.org. Evolutility project is source code (under Affero GPL v3 with dual licensing). Enjoy... maybe improve it and contribute your code.
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.
|Download the latest version of Evolutility at |
Implementation as a set of Backbone views
I also started a more modern version using Backbone, Bootstrap and jQuery. The code and a live demo are available at GitHub.
August 17th 2008 - Initial article
Winner of "Code Generation 2008 Competition" (Third Prize level)
May 27th 2009 - Update
- Added attributes label, description and version, and xmlns to element "form".
- Added attributes dbcolumnlead and pkcolumnname to element "data".
- Added attributes jsvalidation, min, and max to element "field".
- Added element "tab".
- Added paragraphs on field labels and field groups.
- Added details on CSS.
- Eventually understood that "is null" and "is not null" are valid search conditions.
- Updated screenshots of UI forms and added icons for field types. Many thanks to Mark James for his Silk Icons set.
- Added table of contents.
- Added links to localized demos.
- Misc. text edits.
October 13th 2009 - Small Update
- Added attribute minlength to element "field".
- Evolutility now supports 13 languages.
April 29th 2010 - Small Update
- Added new view (web form) for "Mass Update".
December 22nd 2011 - Update
- Added new field type for HTML (in Rich Text Format).
- New view for charts (becoming less and less minimalist with each version...).
- Evolutility now supports 14 languages with the addition of Farsi.
- New look with rounded corners.
- Misc. text edits.