Ajiths blog

Today advertisement management is one of major area in web development. So I thought of having blog post about that.

            Following basic features can be seen in a banner management system.
      • Scheduling multiple banners.
            Set start and end time frames
            Set advertisers that cannot appear on the same page(competitors)
            Determine the sequence and timing of display advertising
      • Rotation of banner adds
      • Banner tracking 

Let’s see at following database diagram which I have designed to fulfill above areas.
“ADManager_Category” table can be used to store banner category such as “Top Banner”,”Bottom banner” etc.
“ADManager_Ads” table can be used to store banner details(Name,Image path,Start Date,End Date, start time, end time etc).
To store competitor banner information we can use “ADManager_AdCompetitors”  table.
Also we can use “ADManager_Banner_AdTracking” table to store banner tracking information. 

For a catalogue web application I was asked to provide a kind of catalogue search facility. According to the requirement, I had to design search supported for exact / similar matching including Keyword and wildcard with predictive suggestions. To achieve this task I realized that fulltext search is the best way.

In this article we will see how fulltext search works and after that we will discuss how I have implemented my search part using it.

What is Full Text Search?

When we need to match exact / similar records, we can do it as below.
Select * from student where name=’Scott’
Select * from student where name like ‘%Scott%’

But if you need to improve above search to suport for wildcard charactors with predictive suggestions and improve performance, you have to use SQL full text search. Full text queries require full text enabled on SQL server.

 How to Activate Full text Index for a table column

Steps:
1) For an existing database, right click on database and go to properties->Files and click Use full text indexing.
You can enable this at the time of database creation also.
2) Then right click on selected table and select Full text index -> Define full text index

Note: You can use sp_fulltext_table, sp_fulltext_column to enable fulltext

For more information see http://msdn.microsoft.com/en-us/library/ms188053.aspx and http://msdn.microsoft.com/en-us/library/aa933406(SQL.80).aspx

How to perform full text Search
 Depending on your requirements you can use one of following for searching.

• FREETEXT
• FREETEXTTABLE
• CONTAINS
• CONTAINSTABLE

FREETEXT - FREETEXT finds the word or words you give it anywhere in the search column. Also when you performing free text search, it tries to look at the meaning of the word.
Sample Query

SELECT * FROM Students WHERE FREETEXT (Name, ‘Scott’)

FREETEXTTABLE - This works as free text search but returns table.
Sample Query
SELECT Name=fulltextSearch.StudentName, Address=Contacts.Address FROM Contacts INNER JOIN FreeTextTable (Students, [Name], ‘Scott’) fulltextSearch ON fulltextSearch.ID= Contacts.StudentID

CONTAINS - If you need to enable search for wild card characters also, you may use contains or containable.
Sample Query

SELECT * FROM Students WHERE CONTAINS (Name, ‘Scott’)

CONTAINSTABLE - This works as contains but returns a table.
Sample Query
SELECT Name=fulltextSearch.StudentName, Address=Contacts.Address FROM Contacts INNER JOIN CONTAINSTABLE(Students, [Name], ‘Scott’) fulltextSearch ON fulltextSearch.ID= Contacts.StudentID

Practical Use

Let’s see how I have deal with the practical implementation of search. I have used two tables to store catalogue data. One for catalogue and other for catalogue pages.We can use another table to store search keyword hit counts.

Data Tables

You can use following stored procedure to perform catalogue search. This procedure splits search keywords and insert to keyword hits table , then use ‘CONTAINS’(to enable search for wild card characters) and ‘FREETEXT’ (to finds the word or words anywhere in the search column) to search catalogue.

Searchkeywords Procedure 

Summary

You can use ‘LIKE’ operator to search similar matching, but fulltext search improves the performance of your search. You can use fulltext search to exact or similar matching values with wildcard support and predictive suggestions.