Saving user searches to a database

Question

My job is to come up with an implementation to save searches linked to an individual user, as well as the ability to analyze this data for business cases.

The data will be used for users to see their search history, and for data mining to get insight in user behavior

It will be implemented on an app to find houses, the scale will be a single country but a possibility to grow global.

Solution 1:

Saving the query string e.g. ?price=20000&rooms=3

Table Searches

• All query strings are inserted in this table with a unique constraint and a unique ID. So whenever the same search is performed it will not be inserted again.

Table UserSearches

• Whenever a search is performed, a search ID gets linked with a User ID. And a counter for how many times the user has performed this search.

Solution 2:

Have a list of columns in a tblSavedSearch corresponding to the search criteria - price/zip/# bedrooms/etc.

Table Searches

• The params of every search are divided in columns and linked to a User ID.

My conclusion

I think solution 2 is the best case for analyzing the data to get an insight in individual as well as common user behavior. But I am not sure it is scalable enough and whether it’s the best way.

Solution 1 is more scalable, but it will require a lot of programming on the application itself to retrieve useful information from the data.

Are there any other options available and how would you do it?

EDIT

Solution 1

Searches Table:

Search ID
Field (e.g. rooms, price, city)
Value (e.g. 2, 20 000, New York) <= numbers saved as String.
Counter 

UserSearches Table:

User ID
Search ID
Counter

---

1. User performs search query
2. Search query is decomposed per field (e.g. rooms: 2, city: New york)
3. For each field
   - If field & value combined are unique: 
        insert search in Searches table
   - Else:
        increment counter of the found field & value combination
4. For each search
   - if search ID doesn’t exist:
        insert UserSearch in UserSearches table.
   - Else:
         increment counter of the UserSearch

Solution 2

Searches Table:

Search ID
Field (e.g. rooms, price, city)
Value (e.g. 2, 20 000, New York) <= numbers saved as String.

UserSearches Table:

User ID
Search ID

---

1. User performs search query
2. Search query is decomposed per field (e.g. rooms: 2, city: New york)
3. each field with its value is inserted into Searches table.
4. for each inserted search, insert UserSearch with the searchID into UserSearches table.

With the first method we can retrieve business logic easily with the counter fields, this will reduce row size from both tables a lot, but will require more logic on insertion.

With the second method we can retrieve business logic with complex querys, the row sizes of both tables will be a lot bigger, but there’s not so much overhead on insertion.

As for my knowledge with databases goes, writing performance is more important than reading, therefore I think solution 2 is best.

What do you think?

EDIT2

Solution 3

Searches Table:

Search ID
Field (e.g. rooms, price, city)
Value (e.g. 2, 20 000, New York) <= numbers saved as String.
Date

UserSearches Table:

User ID
Search ID
Date

Solution 4

UserSearches Table:

User ID 
Date
Field1 (e.g. rooms)
Field2 (e.g. priceLow)
Field3 (e.g. priceHigh)
etc.

Answer 1

I would go for the following UserSearches table:

UserId

Date

Then I add a column for each field

This way, you can easily SELECT the columns you want to consider, then group / sum the data.

A business case you may encounter : How many different user searched for a price over 20000:

SELECT count(UserId) 
FROM (SELECT UserId, SUM(price) 
      FROM UserSearches 
      WHERE price > 20000 
      GROUP BY price)

It seems pretty straightforward to me.

The count is not needed. You don't have an history if you don't have a Date associated to every search.

Your Field column is going to be problematic. Your table is supposed to make you store normalized data, not key-value pairs.

---

EDIT (to answer your comment)

If you add a field to your search, add a column, existing data will have NULL for this column. You loose a bit of info which is the 'context' of your search (what fields where available at the time). If it is relevant for you to keep the context of every search set the defaut value to "N/A" meaning "Not available at the time". It doesn't seem very clean (you might need to exclude the "N/A" value often) but it's flexible.

Answer 2

You may consider a third solution, having a table with:

1. Search ID (key)
2. Field (key, assuming a field appears only once per search)
3. Value

Then you can have another table with:

1. User ID (key)
2. Search ID (key)
3. Search counter (or whatever you need)

With this model you can add and remove fields to the search with little impact to the database (Solution 2 implies adding columns for new fields).

Answer 3

Solution 2.

Solution1 is not more scalable as far as I can tell, and it has a number of drawbacks. Checking for duplicates is not as easy as it seems, since the key-value pairs might occur in arbitrary order in the encoded URL. In any case, you shouldn't couple the database so tightly to the URL-layout used in the frontend. Also it violates first normal form by encoding multiple values in a single field.