You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

Tales from the List: Searching for Answers

Tales from the List: Searching for Answers

Despite what the title of this month's article implies, this installment of Tales From the List is not about a CFDJ-List thread regarding the meaning of life. It's about something much more important: metadata.

Metadata, in a nutshell, is information about information. It's how data is described, categorized, and conceptualized, and is also commonly used to show a relationship between different pieces of information. Some sort of metadata implementation is often required to meet Web application business requirements, and this month's featured CFDJ-List thread is all about a post inquiring about how best to meet such a business requirement.

The thread we will examine began when Tammy Hong, a CFDJ-List regular, wrote to the List saying that she had been asked to build an application that allows end users to search for images based on keywords, and she had two dilemmas. One dilemma was whether to use Access or SQL Server for the database.

Knowing that SQL Server is more robust, she's looking for more detailed reasoning to use that is specific to the application business requirements. The second dilemma was how to best structure the database so that when an end user submits a search, only images with matching keywords are returned. She was already aware of the best practice of storing the location of the image as a string in the database (rather than the image itself), but was unaware of any other "best practices" that apply to the design of an application with her requirements.

I-Lin Kuo, a long-time CFDJ-List regular well-known for his database expertise, suggested that this type of search functionality is best implemented using text indexing and that, because Access does not support text indexing, SQL Server would be a much better RDBMS platform.

He added that text indexing is much faster and allows for stemming and ranked matches, although it does have a steeper learning curve because RDBMS proprietary SQL extensions are used to leverage it. He then noted that he advises never performing a LIKE "%word%" SQL search because of the performance impact.

Kola Oyedeji responded, stating that he assumed I-Lin was referring to SQL Server Full Text Indexing, and wanted to know whether I-Lin knew of similar implementations on other RDBMS platforms? I-Lin responded, stating that Oracle has "Text Indexing," which in his experience is better than SQL Server's "Full Text Indexing," though he certainly does think the SQL Server implementation is pretty good.

He went on to further clarify his statement about LIKE searches, pointing out that in a LIKE search every row of data has to be looped over, whereas in text indexing, the database creates a "dictionary" of words that is optimized to be read very quickly to look up matching rows by applying a "matching algorithm" to the index. He pointed out that the downside is that newly inserted data may not be immediately available in the index and that there's a lot of overhead in the routine that runs to build an index - especially in large tables and columns.

While I also advocate the use of text indexing, I responded to I-Lin's post to point out that case insensitivity may be achieved with LIKE searches, which he hadn't mentioned.

Tammy thanked everyone for their input and rephrased and reposted her original question regarding the recommended table structure to use. I suggested creating one table that contained a unique ID as well as the path of an image, another table containing only a unique ID column and a column containing a single (unique) keyword, and a third (join) table with a unique ID and a unique ID from each of the other two tables (foreign keys).

The majority of experienced developers would most likely immediately approach the database structure this way, as it's a textbook example of a normalized structure. I-Lin Kuo offered an alternative that most developers would not usually consider, but he had a very interesting explanation.

I-Lin suggested that my solution was good when not using text indexing, but suggests that one table with the image location and a column with all of that image's metadata (keywords) in it would actually suit Tammy's needs quite nicely. He explained that this is because the text index would essentially be doing the same thing for you (building the table of keyword lookups for the images) under the hood - without the developer having to do the work. What's more, no table joins would be required to access that data, and keyword redundancy techniques could be implemented.

This is a very interesting point. While one of the benefits and goals of the normalized structure is to have as little data as possible ever be replicated (for example, the text for each actual keyword occurs only once - in the unique keyword table), one of the features of text indexing is that it "ranks" or "scores" your results. A developer can use the same word twice (or more) to describe an image in order to give it more "weight." Imagine that - sometimes redundancy is a good thing!!

I responded to I-Lin's post, noting that there is a serious advantage to the normalized approach in that the keywords themselves are reusable (for cross-referencing and the like) across all of the tables in a database when extrapolated into their own table - something his approach does not allow. I also pointed out that in the normalized design, tables could still be text indexed to speed up lookups. His response was that until Tammy had a reason to normalize the data, why not subscribe to the Extreme Programming maxim of "Do the simplest thing that works" and normalize the data later if the need ever arises? Ultimately, this is where I-Lin and I could not see eye-to-eye.

A posting by Devandra Shrikhande followed I-Lin's and my back-and-forth discussion, pointing out that another obvious benefit of normalizing the data is the ability to easily present the user with a list of available keywords to choose from. The thread also prompted a follow-up response by Amit Talwar, who brought up yet another very interesting point.

Amit reminded us that his preference is to use a database view whenever he needs data in a nonnormalized format. He also mentioned that SQL Server does not let you Full Text Index a view, but that of all the features he'd like to see added to SQL Server, the ability to do so is on the top of his list. If anyone from the SQL Server development team is reading this article - I have to agree with Amit - this would be a killer feature. Amit also reminded us that data inserted into a database via the Web often has to be made available immediately, and that in these situations, text indexing is not a good strategy - which is extremely important to keep in mind and was a very good point to have ended the thread on.

There never was a conclusive decision as to whether it's better to Full Text Index a nonnormalized table or to normalize your tables. However, as a result of I-Lin's thinking "outside the box," a very interesting and educational discussion was born. It taught us all a little bit about the pros and cons of text indexing versus normalizing your tables. Most important of all, it showed the benefit of challenging and rethinking even the most widely accepted techniques and common-sense solutions. That, after all, is what development is all about.

More Stories By Simon Horwith

Simon Horwith is the CIO at AboutWeb, LLC, a Washington, DC based company specializing in staff augmentation, consulting, and training. Simon is a Macromedia Certified Master Instructor and is a member of Team Macromedia. He has been using ColdFusion since version 1.5 and specializes in ColdFusion application architecture, including architecting applications that integrate with Java, Flash, Flex, and a myriad of other technologies. In addition to presenting at CFUGs and conferences around the world, he has also been a contributing author of several books and technical papers.

Comments (1) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

Most Recent Comments
Stephen Anderson 09/23/03 02:26:00 PM EDT

The text indexing features in databases are nice, but why not let Verity do most of the work for you? You can store the info in the database, then cfindex the metadata column. Once you have that, you can let Verity do most of the work for you, like scoring.

@ThingsExpo Stories
Widespread fragmentation is stalling the growth of the IIoT and making it difficult for partners to work together. The number of software platforms, apps, hardware and connectivity standards is creating paralysis among businesses that are afraid of being locked into a solution. EdgeX Foundry is unifying the community around a common IoT edge framework and an ecosystem of interoperable components.
"MobiDev is a software development company and we do complex, custom software development for everybody from entrepreneurs to large enterprises," explained Alan Winters, U.S. Head of Business Development at MobiDev, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
In his session at 21st Cloud Expo, Carl J. Levine, Senior Technical Evangelist for NS1, will objectively discuss how DNS is used to solve Digital Transformation challenges in large SaaS applications, CDNs, AdTech platforms, and other demanding use cases. Carl J. Levine is the Senior Technical Evangelist for NS1. A veteran of the Internet Infrastructure space, he has over a decade of experience with startups, networking protocols and Internet infrastructure, combined with the unique ability to it...
"Space Monkey by Vivent Smart Home is a product that is a distributed cloud-based edge storage network. Vivent Smart Home, our parent company, is a smart home provider that places a lot of hard drives across homes in North America," explained JT Olds, Director of Engineering, and Brandon Crowfeather, Product Manager, at Vivint Smart Home, in this SYS-CON.tv interview at @ThingsExpo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
"IBM is really all in on blockchain. We take a look at sort of the history of blockchain ledger technologies. It started out with bitcoin, Ethereum, and IBM evaluated these particular blockchain technologies and found they were anonymous and permissionless and that many companies were looking for permissioned blockchain," stated René Bostic, Technical VP of the IBM Cloud Unit in North America, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Conventi...
"Akvelon is a software development company and we also provide consultancy services to folks who are looking to scale or accelerate their engineering roadmaps," explained Jeremiah Mothersell, Marketing Manager at Akvelon, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection between Coke and its customers. Digital signs pair software with high-resolution displays so that a message can be changed instantly based on what the operator wants to communicate or sell. In their Day 3 Keynote at 21st Cloud Expo, Greg Chambers, Global Group Director, Digital Innovation, Coca-Cola, and Vidya Nagarajan, a Senior Product Manager at Google, discussed how from store operations and ...
Large industrial manufacturing organizations are adopting the agile principles of cloud software companies. The industrial manufacturing development process has not scaled over time. Now that design CAD teams are geographically distributed, centralizing their work is key. With large multi-gigabyte projects, outdated tools have stifled industrial team agility, time-to-market milestones, and impacted P&L stakeholders.
Gemini is Yahoo’s native and search advertising platform. To ensure the quality of a complex distributed system that spans multiple products and components and across various desktop websites and mobile app and web experiences – both Yahoo owned and operated and third-party syndication (supply), with complex interaction with more than a billion users and numerous advertisers globally (demand) – it becomes imperative to automate a set of end-to-end tests 24x7 to detect bugs and regression. In th...
"Cloud Academy is an enterprise training platform for the cloud, specifically public clouds. We offer guided learning experiences on AWS, Azure, Google Cloud and all the surrounding methodologies and technologies that you need to know and your teams need to know in order to leverage the full benefits of the cloud," explained Alex Brower, VP of Marketing at Cloud Academy, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clar...
"There's plenty of bandwidth out there but it's never in the right place. So what Cedexis does is uses data to work out the best pathways to get data from the origin to the person who wants to get it," explained Simon Jones, Evangelist and Head of Marketing at Cedexis, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
SYS-CON Events announced today that CrowdReviews.com has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5–7, 2018, at the Javits Center in New York City, NY. CrowdReviews.com is a transparent online platform for determining which products and services are the best based on the opinion of the crowd. The crowd consists of Internet users that have experienced products and services first-hand and have an interest in letting other potential buye...
SYS-CON Events announced today that Telecom Reseller has been named “Media Sponsor” of SYS-CON's 22nd International Cloud Expo, which will take place on June 5-7, 2018, at the Javits Center in New York, NY. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
It is of utmost importance for the future success of WebRTC to ensure that interoperability is operational between web browsers and any WebRTC-compliant client. To be guaranteed as operational and effective, interoperability must be tested extensively by establishing WebRTC data and media connections between different web browsers running on different devices and operating systems. In his session at WebRTC Summit at @ThingsExpo, Dr. Alex Gouaillard, CEO and Founder of CoSMo Software, presented ...
WebRTC is great technology to build your own communication tools. It will be even more exciting experience it with advanced devices, such as a 360 Camera, 360 microphone, and a depth sensor camera. In his session at @ThingsExpo, Masashi Ganeko, a manager at INFOCOM Corporation, introduced two experimental projects from his team and what they learned from them. "Shotoku Tamago" uses the robot audition software HARK to track speakers in 360 video of a remote party. "Virtual Teleport" uses a multip...
A strange thing is happening along the way to the Internet of Things, namely far too many devices to work with and manage. It has become clear that we'll need much higher efficiency user experiences that can allow us to more easily and scalably work with the thousands of devices that will soon be in each of our lives. Enter the conversational interface revolution, combining bots we can literally talk with, gesture to, and even direct with our thoughts, with embedded artificial intelligence, whic...
SYS-CON Events announced today that Evatronix will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Evatronix SA offers comprehensive solutions in the design and implementation of electronic systems, in CAD / CAM deployment, and also is a designer and manufacturer of advanced 3D scanners for professional applications.
Leading companies, from the Global Fortune 500 to the smallest companies, are adopting hybrid cloud as the path to business advantage. Hybrid cloud depends on cloud services and on-premises infrastructure working in unison. Successful implementations require new levels of data mobility, enabled by an automated and seamless flow across on-premises and cloud resources. In his general session at 21st Cloud Expo, Greg Tevis, an IBM Storage Software Technical Strategist and Customer Solution Architec...
To get the most out of their data, successful companies are not focusing on queries and data lakes, they are actively integrating analytics into their operations with a data-first application development approach. Real-time adjustments to improve revenues, reduce costs, or mitigate risk rely on applications that minimize latency on a variety of data sources. In his session at @BigDataExpo, Jack Norris, Senior Vice President, Data and Applications at MapR Technologies, reviewed best practices to ...
An increasing number of companies are creating products that combine data with analytical capabilities. Running interactive queries on Big Data requires complex architectures to store and query data effectively, typically involving data streams, an choosing efficient file format/database and multiple independent systems that are tied together through custom-engineered pipelines. In his session at @BigDataExpo at @ThingsExpo, Tomer Levi, a senior software engineer at Intel’s Advanced Analytics gr...