1

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.
3
  • With solution 2 how would you handle ranges: i.e. price between 100 000 and 150 000?
    – k3b
    Jun 8, 2017 at 13:07
  • I would use a priceLow and priceHigh field. But I'm now more inclined to use the solution of Etsitpab Nioliv. What would be better, use solution 2 with date and time columns added compared to Etsitpab's solution?
    – Soundwave
    Jun 8, 2017 at 13:25
  • Please see my edited post: solution 3 and 4.
    – Soundwave
    Jun 8, 2017 at 13:32

3 Answers 3

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.

10
  • Good point about the date, this seems like the easiest solution so far. But will there be no problem if I add fields later on?
    – Soundwave
    Jun 8, 2017 at 13:07
  • I see, but what if there are lots of different fields to search for? Isn't it bad practice to have a lot of columns?
    – Soundwave
    Jun 8, 2017 at 13:26
  • @Soundwave Edited my answer to suggest a way to handle change Jun 8, 2017 at 13:26
  • Edited my post again. What makes your solution (solution 4) more preferable over solution 3 (solution 2 with dates). Because I've read that a lot of columns is not preferable. And there might be a lot of search options.
    – Soundwave
    Jun 8, 2017 at 13:30
  • @Soundwave Check the documentation specific to your DBMS but I would be surprised that it is a limiting factor. From my experience, it's very common to have 100+ columns in a table. If you prefer facts, prototype and benchmarck to make sure this is sustainable with a large amount of data and columns Jun 8, 2017 at 13:36
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).

4
  • This seems like a perfect solution, but it will not let me allow to use different data types for the value column. Would you implement it in a different way or store numbers as Strings? So Rooms: "2" and City: "New York" will work?
    – Soundwave
    Jun 8, 2017 at 11:52
  • For different types you need little more complex model, with different tables for INT,VARCHAR, etc. Also, to keep a single table (and store values as the most generic type, likely VARCHAR) you can have another table to store the real type of each field, and use conversion routines to convert form the real type to varchar and getting back to the real type when needed. Jun 8, 2017 at 12:06
  • I see, I've created 2 solutions from your answer, I edited my original post. Could you please take a look at it?
    – Soundwave
    Jun 8, 2017 at 12:21
  • @AntonioParra Sounds like a dirty hack. Having numbers stored as varchar is a terrible idea in terms of performance and maintainability. Jun 8, 2017 at 13:41
0

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.

4
  • I see, is solution 2 good practice or do you recommend something else
    – Soundwave
    Jun 8, 2017 at 10:57
  • @Soundwave: I wouldn't prefix table names with "tbl", but otherwise it sounds good.
    – JacquesB
    Jun 8, 2017 at 10:58
  • Alright, it was just an example though. Besides, how would this implementation scale on country level and on global level?
    – Soundwave
    Jun 8, 2017 at 11:11
  • The number of countries is irrelevant, the only factor affecting scalability would be the number of queries.
    – JacquesB
    Jun 8, 2017 at 11:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.