Blog: Posts from May, 2012

Master-detail relationships are present in all database web applications. Code On Time automatically generates two-level master-detail page layouts from your database.

You can also create three or more levels of master-detail relationships. For example, view the diagram from the Northwind database below. Order Details table references Orders table, which references Customers.

Let’s create a master-detail page that allows the user to select the customer, the order, and then view the order details.

Create a Northwind web application. Start the Project Designer. In the Explorer, right-click on Home page node and choose New Page option.

Give this new page the following properties:

Press OK to save the page.

Let’s add containers to hold the master and detail data views. In the Project Explorer, right-click on Three-Level Master-Detail page node, and select New Container.

Give this container the following properties:

Press OK to save the container. Create two more containers:

Let’s add a data view in each container. Right-click on Three-Level Master-Detail / c100 container node, and select New Data View option.

Give the new data view the following properties:

Press OK to save the data view. Right-click on Three-Level Master-Detail / c101 container node, and choose New Data View.

Give the data view the following properties. The Filter Field is a field in the child view that match the primary key of the master view.

Press OK to save. Create one more data view by right-clicking on Three-Level Master-Detail / c102 container node and selecting New Data View.

Use the following properties:

Press OK to save the data view. On the tool bar, press Browse to generate the application.

When it opens in your default browser, navigate to the Three-Level Master-Detail page.

First, select a Customer from the list. A child data view will open underneath, showing Orders.

Select an Order, and a list of Order Details will appear below.

Custom result sets can be produced by stored procedures and web services. A quick look at the sample output cache table and output caching script will give you a few ideas about introducing optimization in the caching.

The datable table CustOrderHist_Cache stores cached output created by concurrent users in the process of interacting with the application. The highlighted columns are the actual output columns produced by the stored procedure.

The output caching script filters out the data by CacheUserName column to segregate the user-specific result sets. The performance will improve if the column indexed.

The same consideration applies to the column CachExpires.

A single index that includes both CacheUserName and CacheExpires will likely work well in most situations.

The primary key column CacheID is provided to allow selecting data rows in the application user interface. Our sample result set could be changed to use the ProductName  and CustomerID as a compound primary key. It is possible since the stored procedure CustOrderHist produces a list of unique products purchased by a customer. In that case you can delete the CacheID column and simplify the output caching script.

Another area of optimization if the length of time the custom result set remains cached. Most applications will tolerate the data to be “stale” for at least a few minutes if not hours.

The output cache table may end up containing orphan cached data if users are not working with the app on a daily basis. It is a good idea to enhance the caching script with a simple delete statement that wipes out any cached data rows with the cache expiration overdue by a few days.