Adding full-text searching to Relational Databases

January 22, 2007

Relational databases like ORACLE, SQL*Server, and MySQL are great at storing structured data in rows and columns and managing relationships across tables. Relational database are also more write-friendly than read-friendly (like searching). What that means is that vendors and developers often structure the tables and columns more to make it easy for them to write data into than for users to read data out of (because users are always more creative than we think they are).

One way to get around structural limitations is to put full-text searching on top of structured data . One such tool, DBSight, is an inexpensive but useful application that allows you to point to any relational database and then do Google- or Amazon-like searches. While I recommend full-text searching because it is a great addition to most relational databases, you need to make sure you design the privacy and security rules appropriately otherwise users might get access to stuff they shouldn’t.

If you haven’t done so already, start asking your vendors to give you full-text searching capabilities in your applications so that they can manage the privacy and security rules.

  • Igor

    Just make sure that fulltext indexing does not bring the server to a crawl ;-)

  • http://shahid.shah.org Shahid N. Shah

    Good point. I should have mentioned that in addition to privacy and security, setting up indexing that runs too frequently may cause performance issues in production transactional systems.

  • http://www.dbsight.net Chris Lu

    Hi, Thanks for recommending DBSight. Setting up DBSight is really easy and can be used to search any complicated database system without changing any database configuration or existing code.

    BTW: DBSight can be easily scheduled and you can use incremental indexing to avoid slowing down the system. And DBSight has security built in.

    Check out this flash demo to create a database search in 3 minutes.
    http://wiki.dbsight.com/index.php?title=Create_Lucene_Database_Search_in_3_minutes

  • http://shahid.shah.org Shahid N. Shah

    Thanks for the update, Chris. Seems like a nice product.

  • http://www.trorta.org/glitter glitter

    The information I found here was rather helpful. Thank you for this.

  • nath

    Please advise what healthcare related relational database or financial application system one should train in if they are already a financial analyst in corporate finance? Thanks.

Previous post:

Next post: