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

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.

Answer 2

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 3

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 4

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 5

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 6

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.