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
Enterprises have taken advantage of IoT to achieve important revenue and cost advantages. What is less apparent is how incumbent enterprises operating at scale have, following success with IoT, built analytic, operations management and software development capabilities - ranging from autonomous vehicles to manageable robotics installations. They have embraced these capabilities as if they were Silicon Valley startups.
The standardization of container runtimes and images has sparked the creation of an almost overwhelming number of new open source projects that build on and otherwise work with these specifications. Of course, there's Kubernetes, which orchestrates and manages collections of containers. It was one of the first and best-known examples of projects that make containers truly useful for production use. However, more recently, the container ecosystem has truly exploded. A service mesh like Istio addr...
Predicting the future has never been more challenging - not because of the lack of data but because of the flood of ungoverned and risk laden information. Microsoft states that 2.5 exabytes of data are created every day. Expectations and reliance on data are being pushed to the limits, as demands around hybrid options continue to grow.
Poor data quality and analytics drive down business value. In fact, Gartner estimated that the average financial impact of poor data quality on organizations is $9.7 million per year. But bad data is much more than a cost center. By eroding trust in information, analytics and the business decisions based on these, it is a serious impediment to digital transformation.
Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.
Digital Transformation: Preparing Cloud & IoT Security for the Age of Artificial Intelligence. As automation and artificial intelligence (AI) power solution development and delivery, many businesses need to build backend cloud capabilities. Well-poised organizations, marketing smart devices with AI and BlockChain capabilities prepare to refine compliance and regulatory capabilities in 2018. Volumes of health, financial, technical and privacy data, along with tightening compliance requirements by...
As IoT continues to increase momentum, so does the associated risk. Secure Device Lifecycle Management (DLM) is ranked as one of the most important technology areas of IoT. Driving this trend is the realization that secure support for IoT devices provides companies the ability to deliver high-quality, reliable, secure offerings faster, create new revenue streams, and reduce support costs, all while building a competitive advantage in their markets. In this session, we will use customer use cases...
Andrew Keys is Co-Founder of ConsenSys Enterprise. He comes to ConsenSys Enterprise with capital markets, technology and entrepreneurial experience. Previously, he worked for UBS investment bank in equities analysis. Later, he was responsible for the creation and distribution of life settlement products to hedge funds and investment banks. After, he co-founded a revenue cycle management company where he learned about Bitcoin and eventually Ethereal. Andrew's role at ConsenSys Enterprise is a mul...
DXWorldEXPO LLC announced today that "Miami Blockchain Event by FinTechEXPO" has announced that its Call for Papers is now open. The two-day event will present 20 top Blockchain experts. All speaking inquiries which covers the following information can be submitted by email to [email protected] Financial enterprises in New York City, London, Singapore, and other world financial capitals are embracing a new generation of smart, automated FinTech that eliminates many cumbersome, slow, and expe...
DXWorldEXPO | CloudEXPO are the world's most influential, independent events where Cloud Computing was coined and where technology buyers and vendors meet to experience and discuss the big picture of Digital Transformation and all of the strategies, tactics, and tools they need to realize their goals. Sponsors of DXWorldEXPO | CloudEXPO benefit from unmatched branding, profile building and lead generation opportunities.
The best way to leverage your Cloud Expo presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering Cloud Expo and @ThingsExpo will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at Cloud Expo. Product announcements during our show provide your company with the most reach through our targeted audiences.
DevOpsSummit New York 2018, colocated with CloudEXPO | DXWorldEXPO New York 2018 will be held November 11-13, 2018, in New York City. Digital Transformation (DX) is a major focus with the introduction of DXWorldEXPO within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of bus...
With 10 simultaneous tracks, keynotes, general sessions and targeted breakout classes, @CloudEXPO and DXWorldEXPO are two of the most important technology events of the year. Since its launch over eight years ago, @CloudEXPO and DXWorldEXPO have presented a rock star faculty as well as showcased hundreds of sponsors and exhibitors! In this blog post, we provide 7 tips on how, as part of our world-class faculty, you can deliver one of the most popular sessions at our events. But before reading...
Cloud Expo | DXWorld Expo have announced the conference tracks for Cloud Expo 2018. Cloud Expo will be held June 5-7, 2018, at the Javits Center in New York City, and November 6-8, 2018, at the Santa Clara Convention Center, Santa Clara, CA. Digital Transformation (DX) is a major focus with the introduction of DX Expo within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive ov...
DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City and will bring together Cloud Computing, FinTech and Blockchain, Digital Transformation, Big Data, Internet of Things, DevOps, AI, Machine Learning and WebRTC to one location.
DXWorldEXPO LLC announced today that ICOHOLDER named "Media Sponsor" of Miami Blockchain Event by FinTechEXPO. ICOHOLDER give you detailed information and help the community to invest in the trusty projects. Miami Blockchain Event by FinTechEXPO has opened its Call for Papers. The two-day event will present 20 top Blockchain experts. All speaking inquiries which covers the following information can be submitted by email to [email protected] Miami Blockchain Event by FinTechEXPO also offers s...
Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
Digital Transformation and Disruption, Amazon Style - What You Can Learn. Chris Kocher is a co-founder of Grey Heron, a management and strategic marketing consulting firm. He has 25+ years in both strategic and hands-on operating experience helping executives and investors build revenues and shareholder value. He has consulted with over 130 companies on innovating with new business models, product strategies and monetization. Chris has held management positions at HP and Symantec in addition to ...
Cloud-enabled transformation has evolved from cost saving measure to business innovation strategy -- one that combines the cloud with cognitive capabilities to drive market disruption. Learn how you can achieve the insight and agility you need to gain a competitive advantage. Industry-acclaimed CTO and cloud expert, Shankar Kalyana presents. Only the most exceptional IBMers are appointed with the rare distinction of IBM Fellow, the highest technical honor in the company. Shankar has also receive...
With tough new regulations coming to Europe on data privacy in May 2018, Calligo will explain why in reality the effect is global and transforms how you consider critical data. EU GDPR fundamentally rewrites the rules for cloud, Big Data and IoT. In his session at 21st Cloud Expo, Adam Ryan, Vice President and General Manager EMEA at Calligo, examined the regulations and provided insight on how it affects technology, challenges the established rules and will usher in new levels of diligence arou...