Creating data-driven fluent user interface.

This article describes how to retrieve configurable data to be dynamically and evenly distributed in CSS friendly Web UI with multiple columns within multiple sections.

Data:

Sortable data items stored in “Items” table with Section Identifier (Foreign Key linked to Section Identifier (Primary Key) in “Sections” table.

Data items are configurable which means:

• They might be enabled or disabled to toggle their appearance in UI.
• They have to maintain sort order within Section they belong to.

User Interface:

CSS friendly HTML (“div” or “ul” html tags as containers) layout with multiple columns within multiple sections.

Each section has predefined amount of columns.

Database Server technology:

Microsoft SQL Server 2005/2008

Requirements:

• Items should be sorted within columns first.
• When one or more items to be disabled the remaining enabled items have to be evenly redistributed between columns, similar to principle of communicating vessels. (So the section’s space would be occupied efficiently).

Example 1:

If we disable and remove from available data items 5 and 6 in section 1, the item 3 from first column should be redistributed to column 2 and positioned before item 4.

Example 2

If we disable and remove from available data items 7 and 8 in section 1, the item 3 from first column should be redistributed to column 2 and positioned before item 4, whereas items 5 and 6 should be redistributed to third column.

First temptation to define item’s container width, make them floating left and control amount of columns within sections by defining section container’s width does not work well since items will stack by row first and then by column therefore destroying our sort order.

So let’s introduce columns containers to our html markup first.

This way we can easily maintain sort order within column, our layout will better reflect our data model (Sections table has [ColumnsCount] field) and we gain more control over columns styles with CSS.

In order to uniquely identify our columns we will refer to them by their “serial” number (Absolute Column Number) within page container. That is, we will not renew columns count within every section but will maintain count order from previous section so column “1” from section 2 will become column “4” and so on...

The key to success is

to dynamically identify for each item to which column it should be redistributed within its own section and to assign this number (as Absolute Column Number) to item in resulting set of data, based on following information we have:

• item’ sort order within its section
• amount of columns each section has
• total amount of enabled fields (to be selected) for each section

We are going to use Common Table Expressions (CTE) to conveniently hold intermediate results and discuss performance improvement later on.

[http://msdn.microsoft.com/en-us/magazine/cc163346.aspx]

Let’s select all enabled items first (since we will have to reference this set a couple of times in the next step) and columns amount ([ColumnsCount]) for section the item belongs to.

(We could also have this encapsulated in some indexed view to boost performance)

Next let’s convert (condense) sort order numbers to eliminate “holes” formed by absence of disabled items in our Data set.

Thus items 7 and 8 from Example 1 above will have sort order 5 and 6 correspondingly.

We need this done in order to be able to calculate absolute column number for each item later.

We can easily achieve this by using nice ROW_NUMBER() function with PARTITION BY (SectionId) clause to separate sort order conversion process for each particular section.

We will also select the Total (enabled) Items In section [TotalItemsInSection] to keep this data for each item.

We are going to use it later to calculate to which column in section particular item is going to be redistributed.

This is accomplished by joining sort order condensing set with inline view (derived from [EnabledItems] CTE introduced above) where this sum is calculated.

Now when we have condensed sort order for each item and total enabled items in section (to which the item belongs), we can calculate section column number to be assigned to each item.

(Another words for every item to have a column number to which it is going to be redistributed in our UI).

The order of calculation is following:

1. Calculate maximum amount of items per section column.
   • Cast [TotalItemsInSection] as float type in order to preserve the fractional result of our calculation from being cut and represented as integer.
   • The [TotalItemsInSection] we will divide by [SectionColumnsCount].
   • We will round (always up) the result of our division using nice CEILING(...) function which returns the smallest integer greater than, or equal to, the specified numeric expression. [http://msdn.microsoft.com/en-us/library/ms189818.aspx]

     
     

     For example if we have 5 enabled items in section with 3 columns, we would want the items to be distributed between 3 columns as 2 items in first column, 2 items in second column and 1 item in third column. Therefore we will force the result of 5/3 = 1.666(...) to became 2, That is a maximum amount of items per column in this scenario.

2. Calculate the actual [SectionColumnNumber]:
   • Divide the [SortOrder] by maximum amount of items per section column we have got in previous paragraph.
   • Round (always up) the result of our division using CEILING(…) function again.

     
     

     For example we would want the item with [SortOrder] equal to 1 to have a value of [SectionColumnNumber] equal to 1. The result of division [SortOrder] with value of 1 by maximum amount of items per section column with value of 3 we will produce 1/3 = 0.333(...) (remember that we have cast [TotalItemsInSection] as float). Therefore rounding up with CEILING(…) we will get the desired [SectionColumnNumber], that is 1 (first column) in this case.

We are almost done.

The only thing left is to derive the [AbsoluteColumnNumber] from [SectionColumnNumber] and deliver final result.

We will use the DENSE_RANK (…)function which returns the rank of rows without any gap in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question, therefore each time it encounters the same [SectionColumnNumber] as before it will convert it into next available distinct rank.

Our final result set should look like below:

Having items 5 and 6 from first section disabled (therefore absent from the result) we get Items 7 an 8 from first section assigned condensed sort order of 5 and 6 correspondingly. They will remain in the third column (as if items 5 and 6 were available) and also item 3 is moved to absolute column 2 right before item 4 as desired and described in Example 1.

The first item from Section 2 is assigned [AbsoluteColumnNumber] of 4, which is a first column of second section.

With [AbsoluteColumnNumber] available within our Data set we can easily wrap items in “div” or “li” html tag and insert them into corresponding column’s markup, but this is out of scope of this article.

In our scenario the algorithm of [AbsoluteColumnNumber] calculation is executed every time we would like to select our items, which is not efficient.

We can improve it by separating [AbsoluteColumnNumber] calculation process from Data selection process:

1. Introduce the actual [AbsoluteColumnNumber] field in [Items] table.

2. Encapsulate calculation algorithm in Trigger for [Items] table which will fire on DELETE, UPDATE, INSERT operations, maintaining the correct value of [AbsoluteColumnNumber] field.

3. To deliver final result we now select items directly from [Items] table with [AbsoluteColumnNumber] already calculated and available for us.

Happy Programming!

Alex Movshovich

http://ca.linkedin.com/in/alexmovshovich1