How to design a database table structure for storing and retrieving search statistics?

Question

I'm developing a website with a custom search function and I want to collect statistics on what the users search for.

It is not a full text search of the website content, but rather a search for companies with search modes like:

• by company name
• by area code
• by provided services
• ...

How to design the database for storing statistics about the searches?
What information is most relevant and how should I query for them?

Answer 1

You may want to check this:

http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx

Answer 2

Well, it's dependent on how the different search modes work, but generally I would say that a table with 3 columns would work:

SearchType    SearchValue    Count

Whenever someone does a search, say they search for "Company Name: Initech", first query to see if there are any rows in the table with SearchType = "Company Name" (or whatever enum/id value you've given this search type) and SearchValue = "Initech". If there is already a row for this, UPDATE the row by incrementing the Count column. If there is not already a row for this search, insert a new one with a Count of 1.

By doing this, you'll have a fair amount of flexibility for querying it later. You can figure out what the most popular searches for each type are:

... ORDER BY Count DESC WHERE SearchType = 'Some Search Type'

You can figure out the most popular search types:

... GROUP BY SearchType ORDER BY SUM(Count) DESC

Etc.

Answer 3

This is a pretty general question but here's what I would do:

Option 1 If you want to strictly separate all three search types, then create a table for each. For company name, you could simply store the CompanyID (assuming your website is maintaining a list of companies) and a search count. For area code, store the area code and a search count. If the area code doesn't exist, insert it. Provided services is most dependent on your setup. The most general way would be to store key words and a search count, again inserting if not already there.

Optionally, you could store search date information as well. As an example, you'd have a table with Provided Services Keyword and a unique ID. You'd have another table with an FK to that ID and a SearchDate. That way you could make sense of the data over time while minimizing storage.

Option 2 Treat all searches the same. One table with a Keyword column and a count column, incorporating SearchDate if needed.