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?
如何设计数据库来存储有关搜索的统计信息?哪些信息最相关,我应该如何查询它们?
3 个解决方案
#1
Well, it's dependent on how the different search modes work, but generally I would say that a table with 3 columns would work:
嗯,这取决于不同搜索模式的工作方式,但通常我会说有3列的表可以工作:
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.
每当有人进行搜索时,说他们搜索“公司名称:Initech”,首先查询表格中是否有任何行,其中SearchType =“公司名称”(或者您为此搜索类型提供的任何枚举/ ID值) )和SearchValue =“Initech”。如果已存在此行,请通过递增“计数”列来更新该行。如果此搜索还没有行,请插入一个Count为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.
#2
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.
选项1如果要严格分隔所有三种搜索类型,请为每种搜索类型创建一个表。对于公司名称,您只需存储CompanyID(假设您的网站维护公司列表)和搜索计数。对于区号,请存储区号和搜索计数。如果区号不存在,请插入。提供的服务最依赖于您的设置。最常见的方法是存储关键字和搜索计数,如果尚未插入则再次插入。
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.
或者,您也可以存储搜索日期信息。例如,您将拥有一个包含提供服务关键字和唯一ID的表。你有另一个表,其中包含一个FK到该ID和一个SearchDate。这样,您可以在最小化存储的同时了解数据。
Option 2 Treat all searches the same. One table with a Keyword column and a count column, incorporating SearchDate if needed.
选项2对所有搜索进行相同处理。一个表格,其中包含关键字列和计数列,如果需要,还包含SearchDate。
#3
You may want to check this:
你可能想检查一下:
http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx
#1
Well, it's dependent on how the different search modes work, but generally I would say that a table with 3 columns would work:
嗯,这取决于不同搜索模式的工作方式,但通常我会说有3列的表可以工作:
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.
每当有人进行搜索时,说他们搜索“公司名称:Initech”,首先查询表格中是否有任何行,其中SearchType =“公司名称”(或者您为此搜索类型提供的任何枚举/ ID值) )和SearchValue =“Initech”。如果已存在此行,请通过递增“计数”列来更新该行。如果此搜索还没有行,请插入一个Count为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.
#2
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.
选项1如果要严格分隔所有三种搜索类型,请为每种搜索类型创建一个表。对于公司名称,您只需存储CompanyID(假设您的网站维护公司列表)和搜索计数。对于区号,请存储区号和搜索计数。如果区号不存在,请插入。提供的服务最依赖于您的设置。最常见的方法是存储关键字和搜索计数,如果尚未插入则再次插入。
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.
或者,您也可以存储搜索日期信息。例如,您将拥有一个包含提供服务关键字和唯一ID的表。你有另一个表,其中包含一个FK到该ID和一个SearchDate。这样,您可以在最小化存储的同时了解数据。
Option 2 Treat all searches the same. One table with a Keyword column and a count column, incorporating SearchDate if needed.
选项2对所有搜索进行相同处理。一个表格,其中包含关键字列和计数列,如果需要,还包含SearchDate。
#3
You may want to check this:
你可能想检查一下:
http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx