Users can do advanced searches (they are many possible parameters):
用户可以进行高级搜索(它们有很多可能的参数):
/search/?query=toto&topic=12&minimumPrice=0&maximumPrice=1000
查询/搜索/ ? = toto&topic = 12 &minimumprice = 0 &maximumprice = 1000
I would like to store the search parameters (after the /search/?) for an email alert.
我想将搜索参数(在/search/?)后存储为电子邮件警报。
I have 2 possibilites:
我有两个可能性:
- Storing the raw request (
query=toto&topicId=12&minimumPrice=0&maximumPrice=1000) in a table with a structure like id, parameters. - 将原始请求(query=toto&topicId=12&minimumPrice=0&maximumPrice=1000)存储在具有id、参数等结构的表中。
- Storing the request in a structured table id, query, topicId, minimumPrice, maximumPrice, etc.
- 将请求存储在结构化表id、查询、topicId、minimumPrice、maximumPrice等中。
Each solution has its pros and cons. Of course the solution 2 is the cleaner, but is it really worth the (over)effort?
每个解决方案都有利弊。当然，解决方案2更简洁，但它真的值得(过度)努力吗?
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:
更好的解决方案应该是针对每个维度的最佳方案:
- Rigidity
- 刚性
- Fragility
- 脆弱
- Viscosity
- 粘度
- Performance
- 性能
3 个解决方案
#1
1
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?
丹尼尔的解决方案可能是最干净的解决方案，但我明白你的观点。我对PHP不是很熟悉，但是应该有一些db抽象库来处理关系和多重插入，这样才能获得最好的性能，对吧?我之所以提到它，是因为可能没有真正的性能问题。您是否有可能指向问题的负载测试?
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?"
无论如何，如果它在原来的2个解之间，我必须选择第一个。拥有一个具有列名的表(如解决方案#2)只是在自找麻烦。如果添加新的params，则必须修改表列。还有一个经常出现的问题，“我们要用什么来表示未选中vs为空?”
So I don't agree that solution 2 is cleaner.
所以我不同意解决方案2更简洁。
#2
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.
您可以有一个包含三列的表:search_id、key、value，第一个是主键。这样，如果您有保存搜索的ID，就可以重构特定的搜索。这也允许您使用额外的搜索关键词进行扩展，而不必实际修改您的表。
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.
如果您愿意，还可以将key设为另一个包含有效搜索词的表的外键，以确保完整性。不过，你是否想这样做取决于你的具体需求。
#3
1
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.
这完全取决于你想对数据做什么。对于PHP部分，您需要在插入或选择时处理它。
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.
Daniel的答案是一个通用的解决方案，但是如果您认为性能是个问题，那么您可能会在数据库端为单个搜索(每个参数一个)做太多的插入。太多的插入是性能问题的一个常见原因。
You know your resources.
你知道你的资源。
#1
1
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?
丹尼尔的解决方案可能是最干净的解决方案，但我明白你的观点。我对PHP不是很熟悉，但是应该有一些db抽象库来处理关系和多重插入，这样才能获得最好的性能，对吧?我之所以提到它，是因为可能没有真正的性能问题。您是否有可能指向问题的负载测试?
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?"
无论如何，如果它在原来的2个解之间，我必须选择第一个。拥有一个具有列名的表(如解决方案#2)只是在自找麻烦。如果添加新的params，则必须修改表列。还有一个经常出现的问题，“我们要用什么来表示未选中vs为空?”
So I don't agree that solution 2 is cleaner.
所以我不同意解决方案2更简洁。
#2
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.
您可以有一个包含三列的表:search_id、key、value，第一个是主键。这样，如果您有保存搜索的ID，就可以重构特定的搜索。这也允许您使用额外的搜索关键词进行扩展，而不必实际修改您的表。
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.
如果您愿意，还可以将key设为另一个包含有效搜索词的表的外键，以确保完整性。不过，你是否想这样做取决于你的具体需求。
#3
1
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.
这完全取决于你想对数据做什么。对于PHP部分，您需要在插入或选择时处理它。
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.
Daniel的答案是一个通用的解决方案，但是如果您认为性能是个问题，那么您可能会在数据库端为单个搜索(每个参数一个)做太多的插入。太多的插入是性能问题的一个常见原因。
You know your resources.
你知道你的资源。