From sorting beads to a left-join ... exploring parent-child dynamics in content management

– Brett Barron 26 March 2010

My daughter loves to sort beads and stack cups. It's human nature to want to organize multiples of things, and my daughter will find that her organizational skills will evolve to be applied to increasingly more complex structures. One day she may find herself an information architect, doing this for a living, dealing daily with one of the most common, simple ways of organizing information: by categorization.

Web developers quite frequently present information organized by category for easy assimilation. Products, for example are almost always categorized when displayed in an on-line catalog, but in a broader sense, so are news releases when they are arranged by month or year, likewise blogs when they are tagged. The developer of a content-managed website must designate data structures to categorize items so that the information may be easily navigated by the site visitor and precisely displayed the way the designer defines. This article explores a common information presentation scenario, describes different approaches to accomplishing the task and explores factors that impact implementation strategies.

Scenario: Display a list of stores in a shopping center by category, suppressing categories that do not (yet) have any stores, allowing stores to be displayed in one or more categories.

Simple enough. On the back-end, a table of Categories and a table for Stores. Each Store must identify one or more related categories. On the front end, a page with H2 headers for each category (parent) followed by an unordered list of stores (children) in each category, suppressing categories without stores. Two non-trivial parts: how to associate the store with one or more categories, and how to suppress, at render, any category without stores.

In general, developers should consider many implications when evaluating a specific approach to solving a data management task, to name a few:
  • security
  • performance/scalability 
  • efficiency of implementation
  • efficiency of maintenance (the original developer may not be available when it breaks)
First, the many-many relationship between stores and categories can be managed in a number of ways. 
  • A common (and clumsy) way is to stipulate an upper limit of possible categories a store could be assigned and add that number of category fields to the store table. This approach fails if the guess of high end categories turns out wrong, and makes SQL statements both inefficient and awkward. 
  • The third-normal (strictly proper) way is to create a join table between the category table and the stores table with one row for each combination of category and store. Scales well but requires a solid understanding of SQL.
  • Pack into one store field, the categories that store belongs to. This approach makes for simpler SQL, simpler template substitutions, and scales sufficiently for the data sets used in most dynamic web situations. In situations where real-world performance is not negatively impacted, this approach results in code that is the simplest to implement and easiest to debug/maintain.

The code logic for displaying the listing of stores by category is either category centric or store centric.

You can first cycle through the list of categories that have stores, and for each display the list of stores.
  • Multiple SQL calls, one for the categories, and additional calls to display stores for each category
  • rows do not have to be evaluated to manage category headers
  • requires some mechanism of elimitating categories without stores
    • SQL join when building the category list (requires a good understanding of SQL)
    • a field in category table that identifies categories with stores (requires scripting to keep this field up-to-date)
Alternately, you can sort the stores by category and cycle through them directly, detecting and accommodating changes in category header or cycle though the list of categories and for each look for associated stores. Let's evaluate working with the stores table directly:
  • one SQL call sorted by category returns the list of stores (a good strategy if the SQL server is not on local host)
  • server-side scripting (PHP, ASP, etc.) evaluates each row in the result and tests for a change in category, setting the category header when the category changes
  • only works if the HTML defined by the front-end-coder fits with this structure
  • requires developer understands server-side scripting sufficient to code the conditional placement of the header

Most dynamic website projects - the interesting ones, anyway - have a number of such little puzzles to solve. Often there is no clear "best" approach as several options will provide an acceptable balance of time to develop (cost) and performance at page render. Developers each have their own development style. Developers with strong SQL skills tend to seek answer in elegant SQL statements while PHP wizards rely more on scripting logic. Additionally, in small, single-server implementations with small data sets and modest traffic, render performance may not be as critical as swift development cycles and code that is inexpensive to build and maintain. In the end, as long as the developer is aware of the trade-offs of a particular approach (and has considered options to try if the first strategy fails during testing and QA), it probably doesn't matter exactly how the puzzles are solved as long as the project is delivered on time, is under budget, and performs to expectation.

Dialogs is a flexible dynamic website development platform that accommodates virtually all developer styles and approaches including the ones discussed here. In Dialogs, the web developer easily configures Lists (data tables) to hold data the way he or she sees fit. Lists can be configured to be related to other Lists (one-to-many or many-to-many). List templates may be cascaded to allow the category-centric approach; List templates can also be configured with complex SQL definitions to accommodate the store-centric approach. Many common elements of contemporary dynamic websites are as easy to configure in Dialogs as sorting beads or stacking cups. If you're tired of environments that constrain the way you develop, request a Dialogs.com login to take a closer look at the Dialogs sandbox. We think you'll find we work the way you do.