I apologize if this type of theoretic question is not allow.

The application I am building will return a list of best matching venues from my database given an array of user selections. Typically we are returning around 50 venues. Users can also like or dislike a venue which will need to get stored in the database. I am trying to decide if I should create a new table in my database to store best matches for each user or if I should run my search algorithm with the user selections to get this list.

Initially I thought storing the data would be best. However, the user may want to change some of the answers on the questionnaire in which case I would need to rewrite the list of best matches. Also this table will grow quite large if I need to store 50 entries per user. The pros of storing the data are that it is quite easy to query and find the user's matches. Also it is easy to add a column into the matching table to indicate if a user liked or disliked the venue.

The other option is to resubmit the questionnaire each time. The obvious con of this is that I will be searching through a large database frequently which may be expensive. On the pros side I would eliminate the large table of user matches from my database and it would be easy to handle changes to the questionnaire.

Any help would be appreciated as I am just getting into the database side of things and am looking to learn as much theory as possible.

  • You say a « large database » but how large is it ? And you say searching may be expensive ? Have you tested that ? If so how expensive ? How many users will do searches ? At what frequency ? – Albert Godfrind Oct 13 '20 at 11:36
  • @AlbertGodfrind, this search would need to occur each time a user signs into the app since the results are critical to functionality. In terms of size and expense, it is hard to say. The hope is, of coarse, that many people would use the website and therefore the tables would grow (to the millions). I am really looking to gain the theory/insight of an experienced DBA to help me weight the options. – tdammon Oct 13 '20 at 13:36

It really depends on your requirement. Once the user has thier results, how much do you care about what they wanted, got, liked or disliked?

It sounds to me you want to learn from customer experience and preferences, so storing both the query and the response would be useful. Storing that on the db server means you can analyse the queries and results.

Personally i would save the query parameters and results (and thier response), as xml or json in 2 seperate fields.

Xml or json are useful because they allow your query/results to evolve over time with minimal changes to schema.

If you can just store the venue id, the results wont be huge. All depends on how much you need to store. If you can view the results with the query for context it should make sense.

Its worth considering that if venues are added or removed that a user will get a different result if they run the same query at a later date. So you may want go store queries and results in different tables if you want to retain a 1 to many relationship and history over time. E.g if i have my favourite query and run it once a week, do you want to store each result?

  • Thank you! This is exactly the type of feedback I was hoping to receive. When you talk about saving parameters and results as json are you thinking that a single table cell will hold all of the results as one object? My initial intention was to store all of the results in a table with each result getting its own row. – tdammon Oct 15 '20 at 15:09
  • @tdammon Personally I would save each query as an xml in a single field. (1 row). If you re-execute the query 3 times, I would save each result of that query in a different row. (3 rows.) in a results table. Each row would have a query Id field that relates it back to the originating query. If the user modified thier query i would save that as a new row in the query table. With good indexes and db queries a user app should get good performance retrieving thier last query & result. – Sir Swears-a-lot Oct 19 '20 at 3:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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