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
"When we talk about cloud without compromise what we're talking about is that when people think about 'I need the flexibility of the cloud' - it's the ability to create applications and run them in a cloud environment that's far more flexible,” explained Matthew Finnie, CTO of Interoute, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
The Internet giants are fully embracing AI. All the services they offer to their customers are aimed at drawing a map of the world with the data they get. The AIs from these companies are used to build disruptive approaches that cannot be used by established enterprises, which are threatened by these disruptions. However, most leaders underestimate the effect this will have on their businesses. In his session at 21st Cloud Expo, Rene Buest, Director Market Research & Technology Evangelism at Ara...
No hype cycles or predictions of zillions of things here. IoT is big. You get it. You know your business and have great ideas for a business transformation strategy. What comes next? Time to make it happen. In his session at @ThingsExpo, Jay Mason, Associate Partner at M&S Consulting, presented a step-by-step plan to develop your technology implementation strategy. He discussed the evaluation of communication standards and IoT messaging protocols, data analytics considerations, edge-to-cloud tec...
New competitors, disruptive technologies, and growing expectations are pushing every business to both adopt and deliver new digital services. This ‘Digital Transformation’ demands rapid delivery and continuous iteration of new competitive services via multiple channels, which in turn demands new service delivery techniques – including DevOps. In this power panel at @DevOpsSummit 20th Cloud Expo, moderated by DevOps Conference Co-Chair Andi Mann, panelists examined how DevOps helps to meet the de...
When growing capacity and power in the data center, the architectural trade-offs between server scale-up vs. scale-out continue to be debated. Both approaches are valid: scale-out adds multiple, smaller servers running in a distributed computing model, while scale-up adds fewer, more powerful servers that are capable of running larger workloads. It’s worth noting that there are additional, unique advantages that scale-up architectures offer. One big advantage is large memory and compute capacity...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend 21st Cloud Expo October 31 - November 2, 2017, at the Santa Clara Convention Center, CA, and June 12-14, 2018, at the Javits Center in New York City, NY, and learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
Amazon started as an online bookseller 20 years ago. Since then, it has evolved into a technology juggernaut that has disrupted multiple markets and industries and touches many aspects of our lives. It is a relentless technology and business model innovator driving disruption throughout numerous ecosystems. Amazon’s AWS revenues alone are approaching $16B a year making it one of the largest IT companies in the world. With dominant offerings in Cloud, IoT, eCommerce, Big Data, AI, Digital Assista...
Artificial intelligence, machine learning, neural networks. We’re in the midst of a wave of excitement around AI such as hasn’t been seen for a few decades. But those previous periods of inflated expectations led to troughs of disappointment. Will this time be different? Most likely. Applications of AI such as predictive analytics are already decreasing costs and improving reliability of industrial machinery. Furthermore, the funding and research going into AI now comes from a wide range of com...
Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 21st Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devic...
We build IoT infrastructure products - when you have to integrate different devices, different systems and cloud you have to build an application to do that but we eliminate the need to build an application. Our products can integrate any device, any system, any cloud regardless of protocol," explained Peter Jung, Chief Product Officer at Pulzze Systems, in this SYS-CON.tv interview at @ThingsExpo, held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA
SYS-CON Events announced today that Ayehu will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on October 31 - November 2, 2017 at the Santa Clara Convention Center in Santa Clara California. Ayehu provides IT Process Automation & Orchestration solutions for IT and Security professionals to identify and resolve critical incidents and enable rapid containment, eradication, and recovery from cyber security breaches. Ayehu provides customers greater control over IT infras...
SYS-CON Events announced today that MobiDev, a client-oriented software development company, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MobiDev is a software company that develops and delivers turn-key mobile apps, websites, web services, and complex software systems for startups and enterprises. Since 2009 it has grown from a small group of passionate engineers and business...
SYS-CON Events announced today that GrapeUp, the leading provider of rapid product development at the speed of business, will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company, specialized in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market acr...
SYS-CON Events announced today that Enzu will exhibit at SYS-CON's 21st Int\ernational Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive advantage. By offering a suite of proven hosting and management services, Enzu wants companies to focus on the core of their ...
SYS-CON Events announced today that Cloud Academy named "Bronze Sponsor" of 21st International Cloud Expo which will take place October 31 - November 2, 2017 at the Santa Clara Convention Center in Santa Clara, CA. Cloud Academy is the industry’s most innovative, vendor-neutral cloud technology training platform. Cloud Academy provides continuous learning solutions for individuals and enterprise teams for Amazon Web Services, Microsoft Azure, Google Cloud Platform, and the most popular cloud com...
SYS-CON Events announced today that IBM has been named “Diamond Sponsor” of SYS-CON's 21st Cloud Expo, which will take place on October 31 through November 2nd 2017 at the Santa Clara Convention Center in Santa Clara, California.
In his session at Cloud Expo, Alan Winters, an entertainment executive/TV producer turned serial entrepreneur, presented a success story of an entrepreneur who has both suffered through and benefited from offshore development across multiple businesses: The smart choice, or how to select the right offshore development partner Warning signs, or how to minimize chances of making the wrong choice Collaboration, or how to establish the most effective work processes Budget control, or how to ma...
SYS-CON Events announced today that CA Technologies has been named "Platinum Sponsor" of SYS-CON's 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. CA Technologies helps customers succeed in a future where every business - from apparel to energy - is being rewritten by software. From planning to development to management to security, CA creates software that fuels transformation for companies in the applic...
Multiple data types are pouring into IoT deployments. Data is coming in small packages as well as enormous files and data streams of many sizes. Widespread use of mobile devices adds to the total. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists looked at the tools and environments that are being put to use in IoT deployments, as well as the team skills a modern enterprise IT shop needs to keep things running, get a handle on all this data, and deliver...
In his session at @ThingsExpo, Eric Lachapelle, CEO of the Professional Evaluation and Certification Board (PECB), provided an overview of various initiatives to certify the security of connected devices and future trends in ensuring public trust of IoT. Eric Lachapelle is the Chief Executive Officer of the Professional Evaluation and Certification Board (PECB), an international certification body. His role is to help companies and individuals to achieve professional, accredited and worldwide re...