Clever Geek Handbook

What is the best practice for storing "saved searches" in a database

I am running a search page that allows users to search for homes for sale. Typical search criteria include price / zip / # of bedrooms / etc.

I would like to allow the user to save these criteria in the database and email new homes daily.

I could:

1) Serialize the "SavedSearch" object to a string and save it to the database, then deserialize as needed.

2) Enter the list of columns in tblSavedSearch matching the search criteria - price / zip / # bedrooms / etc.

I am concerned that if I choose option 1, my saved search criteria will change and leave the malformed objects in the database invalid, but option 2 will not be the optimal solution either.

How have others solved this problem?

+12


source to share


8 answers


I assume that you will need to rerun your daily search to find new additions to the results. Perhaps you can make sure that the search form sets a get method so that the search criteria is appended to the URL as a query string, and then the entire sequence of queries is stored in the database.

So, if you have a search program called search.action, you would request a search like this:

search.action?price=20000&rooms=3


You can store the price = 20000 & rooms = 3 part in the database. To get this search, simply add a query string to the url and query the search page again.

The only caveat is like changing the search action that you should do to smart defaults so as not to break old searches. For example, suppose you start searching by color, none of the older searches will have color criteria, so your search action will have to satisfy that, and make sure to use something sensible instead, like ALL colors.

+5


source


table

Criteria table (= list of provided search criteria)



SavedSearch table (detailed information about users)

SavedSearchCriteria table, SavedSearch detail, criteria link, SearchValue column contains the value entered by the user for each of the criteria entered

+3


source


I would go with # 1. If you are really worried about changing the criteria, you can save it with the "search version" attribute and mass the serialized view when needed.

# 2 won't scale to any usefulness. For example, if you want to do some kind of logical grouping of search criteria, your DB schema will light up and start screaming into the forest.

I usually solve search problems by pulling indexing / searching from the database. It might be overkill for what you are talking about, but RDBMSs are not that great for searching.

+1


source


You can store dynamic SQL in the database as a text string. Then you don't have to do all the tricks of recreating WHERE and IN and other SQL from stored key-value pairs.

You can use the saved SQL and run it when you create the email.

If your database design is not stable enough to store query information, you may need to defer your search design until your database design matures.

0


source


I think both approaches make sense and it all depends on the requirements (current and prospective).

For example, if the number of queries is large and if you need to analyze them (for example, answering questions such as "How often do people search for each number of bedrooms?"), Storing the searches and their criteria in a relational form (your option # 2) will be more than appropriate.

But unless you have any immediate requirements that dictate the use of option # 2, there is nothing wrong with serialization, IMHO. Just make sure you DO NOT break compatibility with existing data as the structure of your search criteria changes over time. (BTW, backward compatibility issues are not related to option # 1 - they can occur even with option # 2.) The key point is that you can always switch from option # 1 to option # 2, and you can procrastinate with such a permutation as long as it is considered practical.

0


source


I would add also data visibility to the solution ... and also Table Users - contains users Table Roles - contains roles n db UserRoles table - one user can have one or more roles per session MetaColumns table - contains meta information for all columns / views in db Table ControllerMetaColumns - visibility for MetaColumns on UserRole, users always had to access the actual data through this (INNER JOIN) TableViewToSearch table - table or view to perform searches on SavedSearch table - has ControllerMetaColumnsId attributes, TableViewToSearchId, SavedSearchString - if attribute, it will be given an empty result set

0


source


The best way to achieve this is to store your search criteria as XML. This will allow you to easily attribute your search criteria and provide the ability for the user to change it if needed.

XML should be the most scalable solution and I will avoid passing URL parameters to the action. This may be a simple solution, but will have the following problems.

  • If the user wants to change the new search criterion, we have to perform large scale manipulation.

  • Saving complex search criteria will be a challenge. What if I want to keep the complex and the condition. (In XML, since it is already hierarchical, I could just store all the information I need.)

  • The solution can be scalable and will also sanitize your application from Sql attacks.

  • Since XML is a mature technology, you can use it to perform validation before passing it to the end.

0


source


If I can suggest, please enter a html page with search results (if it contains a moderate number of entries). And save the path to it along with the search criteria in the DB

This will avoid database queries.

Edit: I'm guessing the entries won't change that often. If so, it is best to store the search criteria in a database and query it when the user requests it.

-2


source






More articles:


All Articles