What is the best strategy to store user searches for an email alert?

Question

Users can do advanced searches (they are many possible parameters):

/search/?query=toto&topic=12&minimumPrice=0&maximumPrice=1000

I would like to store the search parameters (after the /search/?) for an email alert.

I have 2 possibilites:

1. Storing the raw request (query=toto&topicId=12&minimumPrice=0&maximumPrice=1000) in a table with a structure like id, parameters.
2. Storing the request in a structured table id, query, topicId, minimumPrice, maximumPrice, etc.

Each solution has its pros and cons. Of course the solution 2 is the cleaner, but is it really worth the (over)effort?

If you already have implemented such a solution and have experienced the maintenance of it, what is the best solution?

The better solution should be the best for each dimension:

1. Rigidity
2. Fragility
3. Viscosity
4. Performance

Answer 1

You could have a table consisting of three columns: search_id, key, value with the two first being the primary key. This way you can reconstruct a particular search if you have the ID of a saved search. This also allows you to expand with additional search keywords without having to actually modify your table.

If you wish, you can also have key be a foreign key to another table containing valid search terms to ensure integrity. Whether you want to do that depends on your specific needs though.

Answer 2

Well that's completely dependent on what you want to do with the data. For the PHP part, you need to process it anyway, either on insertion or selection time.

For really large number of parameters you may save some time with the 1st on the database management/maintenance, since you don't need to change anything about your database scheme.

Daniel's answer is a generic solution, but if you consider performance an issue, you may end up doing too many inserts on the database side for a single search (one for each parameter). Too many inserts is a common source of performance problems.

You know your resources.

Answer 3

Daniel's solution is likely to be the cleanest solution, but I get your point about performance. I'm not very familiar with PHP, but there should be some db abstraction library that takes care relations and multiple inserts so that you get the best performance, right? I only mention it because there may not be a real performance issue. DO you have load tests that point to an issue perhaps?

Anyway, if it is between your original 2 solutions, I would have to select the first. Having a table with column names (like your solution #2) is just asking for trouble. If you add new params, you have to modify the table columns. And there is the ever present issue of "what do we put to indicate not selected vs left empty?"

So I don't agree that solution 2 is cleaner.