What is the best practice to store a "saved search" in a database

Question

I am working an a search page that allows users to search for houses for sale. Typical search criteria include price/zip code/# bedrooms/etc.

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

I could either:

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

2) Have a list of columns in a tblSavedSearch corresponding to 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 searialized objects in the database invalid, but option 2 doesn't feel like an optimal solution either.

How have others solved this problem?

Answer 1

The best way to accomplish this is by storing your search criteria as XML. This will enable you to refer your search criteria easily and provide ability for user to change it if needed.

XML should be the most scalable solution and I will steer away from passing in URL parameters to action. It might be a simple solution but will have the following problems.

1. If user wants to edit a new search criteria then we have to do a large scale string manipulation.

2. Storing complex search criteria will be a issue. What if I want to save a complex and or condition. (In XML, since its already hierarchical, I could just store all the information I need).

3. The Solution could be scalable and will also sanitize your application from Sql injection attacks.

4. Since XML is a matured technology, you could use it to even perform validation before passing in it on to the back-end.

Answer 2

I would add also data visibility to the solution ... as well Table Users - contains the users Table Roles - contains the roles n the db Table UserRoles - one user might have one or more Roles at a session Table MetaColumns - contains the meta info for all the columns / views in the db Table ControllerMetaColumns - visibility per MetaColumns per UserRole , the users would always have to access the actual data via this one ( INNER JOIN ) Table TableViewToSearch - the table or view to perform the search on Table SavedSearch - Has Attributes ControllerMetaColumnsId , TableViewToSearchId , SavedSearchString - if the Role does not have access the attribute it will be given empty result set

Answer 3

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

For example, if the number of searches is high, and if you need to analyze them (e.g. answer questions like "How often do the people search for each number of bedrooms?"), storing searches and their criteria in relational form (your option #2) would be more than appropriate.

But if you don't have any imminent requirements that dictate use of option #2, there is nothing wrong with serializing, 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 specific to option #1 - they may occur even while using option #2.) The key point is: you can always switch from option #1 to option #2, and you can procrastinate with such switching for as long as deems practical.

Answer 4

You could save the dynamic SQL itself in the database as a text string. Then you won't have to do all the machinations of recreating the WHERE and IN and other SQL from the saved key-value pairs.

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

If your database design is not stable enough to be saving query information, you may need to defer the search design until your database design is more mature.

Answer 5

I assume you will need to re-run the search daily in order to find new additions to the results. Maybe it is possible to make sure that you search form specifies a get method so that the search criteria is appended to the url as a query string then save the entire querystring in the database.

So if you have a search program called search.action you will request the search like this:

search.action?price=20000&rooms=3

You can save the price=20000&rooms=3 part into the database. To retrieve this search simply append the query string onto the url and request the seach page again.

The only caveat is as the search action changes you have to make intelligent defaults to avoid breaking old searches. For example, suppose you start searching by color, none of the old searches will have a color criteria so your search action will have to cater for this and make sure that something reasonable like ALL colors is used instead.

Answer 6

I'd go with #1. If you're really worried about the criteria changing, you can store it with a "search version" attribute and massage the serialized representation when necessary.

#2 won't scale to any kind of usefulness. For instance, if you want to do any kind of boolean grouping of search criteria, your DB schema is going to light itself on fire and run screaming into the woods.

I generally solve search problems by yanking the indexing/search out of the database. That may be overkill for what you're talking about, but RDBMS's aren't that great for searching.

Answer 7

table Users

table Criteria (= the list of provided search criteria)

table SavedSearch (detail of Users)

table SavedSearchCriteria, detail of SavedSearch, referencing Criteria, column SearchValue holds the value entered by the user for each of the criteria entered

Answer 8

If I can suggest, have a html page with search results (if it contains a moderate number of records). And, store the path to it alongwith search criteria in the DB

This will avoid querying the DB.

Edit: I am assuming records won't change as frequently. If it does, it is better to store search criteria in database and query it when asked by the user.