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.