Platform Guide

Developer’s Guide

validDB User Guide

validDB is a database hosting service that provides a quick and easy way for data modelers to validate the correctness of their models by providing an application that can be easily shared with domain experts and stakeholders for the purpose of getting feedback on designs.  It was not built with the intent of hosting production work loads. If you are interested in using validDB in your production environment please contact our sales team for more information.

Subscriptions

A subscription for validDB determines the length of time, the number of databases and the number of shares available. A trial subscription grants the user the ability to create one database and share it with ten users for seven days.  Shares can be used across multiple databases.

Databases

Databases hosted on validDB use the partial containment model of Microsoft Sql Server 2016 or later with a size limit of 100MB.  The database creator is granted permission to create tables, views, procedures, schemas, roles, functions, rules, synonyms and types.  User management is handled by validDB.  A default schema is created for each user based upon their user name. However, it is recommended that data models define their own schemas for objects.

Getting Started

validDB is available at https://validdb.com. Once you have created an account or signed in, a new database can be created on the “Databases” tab of the “Account” page by clicking “Add a new database”.  Provide a name for the database and click “Add Database”.

Executing a Sql Script

After the database has been provisioned, the next step is to execute the Sql script to create the database schema. Typically the Sql script will be forward engineered from a data modeling product such as erwin Data Modeler, IDERA ER/Studio Data Architect and Toad Data Modeler. Alternatively, scripts can be generated in Microsoft Sql Server Management Studio for an existing database. From the account page, click the “Sql” button below the database name. This will navigate to a page where the Sql script can pasted and then executed. The executed script will be displayed below and will execute until an error is encountered.  The portion of the script that encountered an error will be displayed in red. Hovering over that section will display the reason for the error.

The database is available for exploring and sharing.

Exploring

From the account page click the database name. This will open a new tab that will launch validDB for that database. The owner can use the design tools within validDB to customize many aspects of the interface. See the validDB Developer’s Guide for more information.

Inviting Users

Database owners can invite other users to interact with the database by clicking the icon next to the database name on the account page. Enter the e-mail address of user to invite and check the roles that the user should have within the database. After clicking the “Invite” button an e-mail message will be sent to the user with a link that will allow them to quickly create an account if they don’t have one and access the database.

Shares can removed at any time by clicking the “X” next to each invited user.

validDB Developer’s Guide

The key tenet of validDB is that if you build a good database, it will produce a good user interface. It should reward the database designer for doing the right thing. Since a good database and is one that reflects the business problem it stands to follow that the user interface will as well. The proper use of normalization and the use of declarative integrity constraints, including data types, relationships and indexes, will provide a richer user experience in validDB. For database objects that don’t offer built in integrity constraints such as views and stored procedures, validDB has built-in tools to provide the missing information.

This guide is divided into two sections. The first section discusses how database design affects the user experience. The second section discusses how to use the built-in design tools to improve the user experience. This guide assumes a basic understanding database design.

Database Design

This section shows how database schema elements such as tables, keys, relationships and procedures are manifested in the validDB user experience.

Tables

Let’s start with a single table.

This table has a single, nullable column and no primary key. When the database is opened, the table is available in the Views panel.

Clicking the table opens the grid view.

Clicking “New” opens a form to allow a new row to be added to the table.

After the row has been saved, another row can be added by clicking the “New” button or the user can go back to the grid view by hitting the escape key or clicking “Some Table” in the path tab.

Primary Keys

Without a primary key on the table, users cannot update or delete rows. After a primary key has been added, users can navigate to existing rows from the grid view where they will be available for updating and deleting if the user has permissions available to do so.

Surrogate and Natural Keys

A primary key is the set of columns that uniquely identify a row in a table, however, the primary key and what the user considers to be the primary key are often different. This is often the case when surrogate keys, in the form of identity or unique_identifier, columns are used. Consider a table with an identity column “Id” and another column “Name” with a unique contrast. In this scenario we would use “Id” to implement relationships, but the user would identify each row the name. Having an immutable primary key is sometimes more efficient than using “Name” as the primary key since it consumes less disk space in foreign keys and doesn’t require mass updates should “Name” change. validDB recognizes this pattern.

The “Id” column is automatically hidden from the user and the identity value is managed transparently in the background.

A table can have multiple unique indexes so the one used as the natural key will be selected in the following order.

Later, we’ll discuss using the design tools to explicitly specify the natural key. The natural key is used in other important contexts. Notice that the caption in the path tab is composed of the natural key values. Later we’ll see how natural keys play an integral role in lookups.

Relationships

The real power of validDB is in the use of relationships and are the basis for sub tabs, lookups and seamless navigation through the database.

Drop-Down List Boxes

Drop-down (or lookup fields) are created using one-to-many relationships (foreign keys).  These can be hard relationships built-in to your database schema or soft-relationships created with the design tools built into validDB. Throughout this section we will use a table of cars to illustrate the various features and build on it as we go.  This simplest case is a single table.

The table has a column called “VIN” (Vehicle Identification Number) that serves as the primary key and columns for “Manufacturer”, “Model” and “Color” that describe various attributes of the car.  In this first iteration each of those attributes are defined with columns of type nvarchar (a variable length string composed of Unicode characters) .  When viewed in validDB the following form is produced.

The problem with this design is that there is no integrity checking mechanisms in place to ensure that valid values for each field are entered.  Arbitrary values can be entered into “Manufacturer”, “Model” and “Color”.  That can be addressed by creating additional tables to represent the valid lists of “Manufacturers”, “Models” and “Colors” and then establishing relationships between the “Cars” table and each of the supporting tables as illustrated in the following diagram.

When the form is viewed in validDB it looks very similar to the first form, however, each field is now a drop-down list. You can enter text into the fields that filter the list of available options, but it will not allow you to save the form until a valid option is selected.

This is a dramatic improvement on the original design, but there are integrity problems since invalid combinations of “Manufacturer” and “Model” can be saved.  To address this, the design will be refactored such that “Manufacturer” becomes a member of the primary key for “Models” as shown in the following diagram.

Now when the form is viewed it is clearly different from the original and provides clues as to how foreign keys factor into the production of the form.

In this example the form production engine starts with the tuple of visible columns (VIN, Manufacturer, Model, Color) and replaces columns with the parent relationships that cover them.  This produces the following tuple that is composed of columns and parent relationships (VIN, FK_Cars_Models, FK_Cars_Colors).  Each parent relationship produces a lookup form that contains fields for the natural key columns of the parent table and then parent relationships are applied just like they were at the base level.  This continues recursively until all of the parent relationships have been resolved or the maximum depth is reached.

In the example there is a lookup form called “Manufacturer_Model” that contains fields for “Manufacturer” and “Name”.  “Manufacturer” is its own form but has been collapsed since it has just one field.  The same is true for “Color” lower in the form. 

The default label “Manufacturer_Model” is computed by combining the captions of the covered child columns.  This is known as the parent caption can be explicitly specified using the built-in design tools.

Surrogate Primary Keys

In practice database designers often use surrogate keys.  In this design pattern the table has an immutable identity column such as an auto number integer, code or guid that serves as the primary key and another set of columns that serve as the natural key.  The natural key is the set of columns that uniquely identify a row in the table to the end user. In the previous examples the primary key and natural key have been the same set of columns.  The following design uses surrogate keys. The primary key is an integer that has been defined as the identity column for the table.  That natural key is defined as a non-primary unique index on the table.  Both the primary key and natural key can be explicitly defined using the built-in design tools.

The produced form looks and behaves exactly like the previous form except for the “Model” label.  Since the “Models” table now has a surrogate key, it is unnecessary for the “Manufacturer” column to be migrated back to the “Cars” table so the only column covered by the parent relationship is “Model”.

Overlapping Foreign Keys

So far, all the foreign keys have been discrete. Each key has its own set of columns that are not shared with any other keys. The next iteration will demonstrate what happens in this scenario, but first additional integrity mechanisms will be introduced. So far, we could specify any color for any model, however, each model of car is only available in certain colors.  This is a many-to- many relationship that is resolved with an intermediate table called “Available Colors”. It defines the pairs of model and color.  A relationship between “Cars” and “Available Colors” is defined.

The resulting form has one lookup form that only allows valid combinations of “Manufacturer”, “Model” and “Color” to be saved.

In this design there are several transitive relationships that are not explicitly defined. Since “Cars” has a relationship “Available Colors” it has an implied relationship between “Cars” and “Models” and another between “Cars” and “Colors”. The following design explicitly defines those relationships.

These relationships change the way the form is produced.

The following steps show how the form production engine produced the form.

The base tuple starts with (VIN, Model, Color).  It then applies the set of child column tuples from the parent relationships.  These tuples are (Model), (Model, Color) and (Color).  This scenario has tuples that intersect. To address this, tuples are sorted by degree.  This results in the following list of tuples:

FK_Cars_Models (Model)

FK_Car_Colors (Color)

FK_Cars_Available_Colors (Model, Color)

The tuples are applied to uncovered columns in the base tuple. The result is the following base tuple:

(VIN, FK_Cars_Models, FK_Cars_Colors)

All the child columns in FK_Cars_Available_Colors are covered by the time it gets applied so there is no visible user interface for it. If there were any uncovered columns they would be visible in a lookup form.

Sub tabs and Related Views

Drop-down list boxes are manifested from the parent side of a relationship. The child side will appear as a sub tab if the relationship is dependent or a related view if it’s not. A relationship is considered dependent if it is defined with cascade delete. The following form shows a model. It has a sub tab for the available colors and a related view for the cars of that model.

Stored Procedures

validDB supports executing stored procedures by allowing the user to enter the parameters and displaying the results returned. The following example takes a single parameter and returns two result sets.

createprocedure[dbo].[Return Something]

@Parameternvarchar(20)

as

begin

select@Parameter [Value];

 

select'Result set two'[Value];

end

It will appear on the action bar of the database tab.

Clicking on the button will open a form to allow the parameter to be entered. If there are no parameters, the user will be asked to confirm whether they want to execute the procedure and it will be executed right away. If it returns results, a path tab item will be opened to display them.

Results are displayed in sub tabs.

Using the Design Tools

In many situations the database may have missing meta data or cryptically named elements, so validDB includes built-in design tools for creating an interface that allows you to make it more user friendly. The design tools do not change the schema of the database. Changes are saved as extended properties on the relevant objects. The design tools are only available if the logged in user has alter permissions for at least one element in the view.

The user can enter design mode by clicking the pencil icon in the navigation bar. Once in design mode the user can click on designable elements. This will bring up a dialog that allows editing of various properties associated with the element. The navigation bar will also show another icon that toggles whether hidden objects are shown.

Database

While in design mode on the database tab clicking an element will bring up the following dialog.

This allows the designer to provide a user-friendly caption for the object and description that appears when the mouse is hovered over the object. The object may also be explicitly hidden or shown on the database tab. By default, dependent child objects are hidden.

If the object is a table or view, the table tab will be available.

From this tab a variety of meta-data can be provided about the table or view. For tables, this information should be part of your schema, but views don’t have the notion of keys and the meta-data gathering process can’t always determine the identity or row guid column of a view, so they can be explicitly specified here.

Grid

Grid columns can be resized by mousing down and dragging the column border.  When in design mode in the grid view, resized columns are persisted.

Form

The form view has many design opportunities.

Columns

A form field may be either a column from a table or view or a lookup from the parent side of a relationship. Clicking on a column in design mode will bring up the following dialog.

This allows you to specify a user-friendly caption along with a description that will be displayed when the mouse is hovered over the column. If a column is designated as computed it will not be shown when adding a new row. Columns may also be designated as required. This is sometimes necessary when the nullability of column cannot be properly determined during the meta-data gathering process of a view. Columns may also be specified as explicitly hidden or shown. Certain columns are hidden by default such as columns that are part of the primary key, but not the natural key. Other columns may be hidden because of the factoring process for generating lookup fields. Finally, the display order affects where the column is displayed in the form.

If a column is of a string or variant type it maybe designated as a hyperlink on the Hyperlink tab. A hyperlink can have three distinct parts; a URL, the display text and a description. The URL is where the user will be directed when the link is clicked. The display text can optionally provide more user-friendly text for the URL. The description can optionally provide text that is displayed when the user hovers the mouse over the link. Buddy columns can be specified that provide the display text and description. Buddy columns are automatically hidden on the form and grid.

Object Columns

If a column is of a variable length binary or variant type it may be treated as an object field where files can be attached to the form. Buddy columns may be specified for File Name, Last Updated and Created on the Object tab.  The following shows a sample table used to store files.

The following shows the form before using the design tool to specify buddy columns. Each column is shown and treated independently. After going into design mode, clicking on the “Bits” column and navigating to the “Blob” tab we can specify the buddy columns.

Now if we navigate to the form we can add file using drag and drop or by clicking the folder icon in the field which opens a dialog to allow the user to select a file.  The size and last modified date are displayed as when the mouse is hovered over the field. The buddy columns are automatically updated in the database.

Relationships

Relationships for the basis of lookup fields, sub tabs, sub forms, related views and related procedures.  These can all be designed from the design mode of a form by clicking on the object. Soft relationships can be added from the design mode as well. The following tables will be used for this section.

We have two tables. Each has a surrogate primary key called “Id” that is an identity column. Each has a natural key called “Name”.  The child table has a foreign key from the column “Parent” to the “Id” column of the parent table that is designated cascade delete. After navigating to “Parent Table” and adding a row we have the following form.

The child table appears as a sub tab on the parent form, however, it has a very user-unfriendly name so we can go into design mode, click the tab and change it in the Edit Relationship dialog.

The dialog is organized into two columns for the child and parent properties.  To change the caption for the sub tab we would update the child caption. The child description property would define the text that would appear when we hover over the sub tab, sub form, related view or related procedure.  The child display order affects where the tab, view or procedure would appear in the respective interface element. The element may also be explicitly hidden or shown.

The flip side of the relationship will have “Parent” as a lookup field on the child form.

If a relationship is dependent it will appear as a sub tab. Relationships designated as cascade-delete will default to dependent. If it’s not dependent it will appear in the “Related Views”. Dependent objects will not appear in the database tab.

Soft Relationships

Hard relationships (those that are defined in the database schema) can be between two tables. However, they can logically exist between views, stored procedure parameters and stored procedure results. validDB provides a mechanism for defining these logical, or soft relationships. They do not provide the database level integrity checking of hard relationships but do allow for a better user experience in validDB. To define a soft relationship, navigate to a form that contains the parent object for the relationship and enter design mode.  This will display a new sub tab that contains a link to add a new soft relationship.

This will display a wizard. On the first page, specify the parent and child objects of the relationship.

On the next page, the segments can be paired between the parent and child by dragging and dropping them onto the corresponding column. “Name” is required for multi-segment relationships.

Stored Procedures

The parameters of a stored procedure can be designed in the same manner as table or view columns. User-friendly captions and descriptions can be provided and can be designated as required, computed and hidden. The can also serve as foreign keys by defining a soft relationship.