Search This Blog

Tuesday, July 28, 2009

Rethinking SQL Search in Web Applications (Part two)

Exploring the design

So, based on my previous post, a possible approach could be that instead of developer coding each search in the application by hand or like in some tools, an administrator configuring the search in an administration tool, the search gets defined as soon as a set of tables is scanned while connecting to a database schema. The administrator either manually defines a relationship between multiple tables using join keys and alternatively defines lookup columns and values for coded fields. These relationships could infact go across database tables. Alternatively, the system could scan the tables and come up with a list of suggested join keys and the end user simply confirms them. This meta-information that may span across multiple databases could be kept in a xml database for scalability, extensibility and portability.

In case of spatial data, it could be extended to the concept of spatial relationships such as for dealer locations in my previous example. Once these relationships are defined, the user could utilize them in the manner they want.

As mentioned, users can apply multiple search criteria on the same invocation of a search or can apply successive filters one after another. You can also use results of one search as a filter criteria on another search.

The user interface will also need some considerations. Instead of presenting the user a drop down of possible tables that could be added, user may be presented with an iPhone like UI, which user could potentially apply successively for firing a search.

Monday, July 27, 2009

Rethinking SQL Search in Web Applications

Search is a very common capability that exists in almost all data driven systems. Typically users enter a search criteria and perform a search. Behind the scenes the user's inputs are taken and are converted to a language such as Structured Query Language (SQL) and a query is fired.

While SQL itself is a very expressive syntax, end users are rarely exposed to the entire capabilities of the SQL language. The reason is simple. Traditional web applications cannot expose an entire SQL syntax without complicating the interface. On one hand are fixed parameter based search input screens and on the other hand are these calculator inspired Query Builder tools. With the onset of text indexing a lot of search tools also provide single input text boxes but these cannot be translated to SQL expressions so I will not include them as part of the discourse.

Looking from the users' perspective
In the users mind there are two concepts. Search for and search by. Typically users want to search for, say a second hand car for sale by model, make, manufacture year, etc. These are attributes of the car itself. Alternatively, they may want to search by other criteria related to the car. For example, number of times the car has been sold in the last 2 years. Maybe the location of the seller/ dealer from my house should not exceed X kilometers/miles or the offer price on the car should not exceed Y amount of dollars.

As I can see, the concept of the search is simple. Either I want to search based on the attributes of the entity itself or based on some criteria associated with the entity. At times, I may want to change the object I am searching for. Instead of searching for the second hand car, I may turn my attention to car dealers. I may want to know all car dealers that are selling the car I am interested in. If I have already fired the search for cars in a previous step, I may want to re-use my search results to finding dealers that are selling this car and are rated at a certain level by the industry.

In the next part, I will explore what form this search could take.

Saturday, May 9, 2009

Why do we need a portlet services layer

I was playing with the latest version of Liferay open source portal. I have previously worked a little with MOSS 2007. I noticed that for the portal platforms the preferred mechanism of accessing data was through a services layer. I was initially convinced that it was a good idea. Now I am not sure if this is the ideal design for all circumstances.

While looking around, I found this post on the IBM developer blog.