Building Apps with an Intelligent Database Wizard

Data-driven solutions like database and enterprise apps rely on connection to, and interaction with, backend databases. Backend relational databases, however, store data in tables that reflect complex data relationships. This provides numerous advantages for effective data management and data integrity but can make it difficult to access and work with the data stored therein in new ways. App developers need to have a comprehensive understanding of database design principles and the SQL query language just to get started.

In contrast, real world data relationships most often represent parent-child relationships or even deeper hierarchical structure. As such, working with hierarchical data where relationships can be visualized in a tree structure can be much simpler and more flexible, leading to faster development. This approach is also more accessible to developers without extensive SQL expertise.

To make building apps that connect to the backend relational databases that are ubiquitous in today’s enterprise easier, faster, and available to a wider range of developers, Altova MobileTogether takes an entirely unique approach. Its visual Database Wizard lets developers easily build a query that returns hierarchical data, work with that data in the app, and then easily save the data back in hierarchical form, letting MobileTogether take care of normalizing the data and writing it back to the corresponding linked tables. Let’s take a look at how it works.

Working with Hierarchical Data

Relational databases organize data into rows and columns, which collectively form a table. Data is typically structured across multiple tables, and data relationships are indicated via primary and foreign keys that enforce uniqueness while allowing for complex connections to be defined. Though the relational model has numerous efficiencies and advantages, it doesn’t lend itself to certain applications. For instance, including data from two tables in a single form – and keeping that data editable – requires two separate database queries and quite a bit of handwritten SQL. Saving data from such a form back to the database is even more complicated. In addition to being time consuming, this requires app developers to be well versed in SQL in addition to their other qualifications, which is a significant challenge that only exacerbates the current shortage of qualified software and app developers.

Visual, low-code tools like MobileTogether work behind the scenes to make building apps that connect to backend data as straightforward as possible, allowing developers to focus on the form and function of their apps and, most importantly, the user experience, instead of getting bogged down writing complex SQL statements.  (A quick note: while this article focuses on database driven apps, MobileTogether can also connect to sources in other formats including XML, JSON, HTML, SOAP and REST, etc. Legacy data sources (Excel, EDI, text, etc.) can be easily connected via MapForce Server integration.)

Regardless of the data source, MobileTogether presents backend data in a hierarchical tree view that lets developers immediately understand relationships and work with elements as required – regardless of how they’re stored in the backend database – while defining the app functions and actions.

Database Wizard: Importing the Database Hierarchy

When you first connect to the datastore for your app in MobileTogether, you can choose from a wide range of supported relational databases and connection options.

The Connection Wizard walks you through the process.

Once connected, the Add Page Source dialog displays a list of available tables. Here is a fairly simple backend database being used to create a Books Catalog app.

To select the columns needed for the app, the visual SQL statement builder in the Database Wizard makes it easy to build a SELECT statement using point-and-click, which is much faster than writing the SQL statements manually. With the Authors table selected, the dialog initially includes a SQL statement to select all columns by default, but this can be refined. In this case we’ll keep all columns but edit the statement to order the data by AuthorName in alphabetical order. The resulting SQL statement is built purely with point-and-click using context sensitive, interactive toolbar buttons, combo boxes, and drop down menus.

One of the biggest advantages of using MobileTogether to work with databases is that you can import the entire database hierarchy at once, activating previously defined table relations or even defining new table relations on the fly via the Add/Edit Relations dialog.

If additional database tables are related to the main table, you can select the tables for which you want to maintain the relation. (The relations are built using primary and foreign keys.) For example, as shown below, the Books table has an AuthorID foreign key that relates each book record to an author in the Authors table. We can check the box next to the Books table to maintain that relation, and the page source will be created so that Books is hierarchically related to Authors as a child.  All the columns from the Books table will be selected automatically.

This makes the whole database structure available in a tree structure in the Page Sources pane.

Each column is now accessible for building the app UI and functionality.

Most importantly, when it comes to saving the user-modified data back to the database, MobileTogether makes this process extremely simple. For each level of the relational tables expressed in the hierarchical tree structure, we can decide whether  to replace all table rows upon save, or only save any modifications made – and this setting can be applied differently to each level of the hierarchy.  

The Database Wizard is helpful throughout the entire app development process. For instance, we can use it to filter data to use at the source for various subpages in our application design. Here, when defining the subpage for viewing and editing Author details, we’ve visually composed a statement to filter the data to select the Author with the current author ID.

Whether you are dealing just with one table, or with several linked tables, the database wizard makes it easy to define queries that retrieve the right set of data from the database. Using the built-in intelligence of MobileTogether, writing data back to the database can be as easy as simply issuing a “Save” command and telling MobileTogether to save modifications-only across an entire hierarchical tree structure.

Of course, for developers who prefer to directly work with SQL commands, MobileTogether has you covered: you can exercise any level of detailed control, including transactions, bulk inserts, and directly execute any SQL statements:

Learn More and Try it Now

To see all this in action, check out our app development how-to video showing how the database-driven Book Catalog app was created from start to finish.

To get started building an app that connects to a backend database, you can download the free MobileTogether Designer.