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.
Best Answer
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?