寻找一种使用内部数据库表扩展外部数据的好方法
我有一个从第三方接收大部分数据的应用程序。这些数据组之一是交易列表。我无法写入此服务，但我想向该数据添加更多信息，以便在我自己的应用程序中使用。我打算使用带有扩展信息的 SQL 表来实现这一点。
问题是第三方数据不会从他们这边返回记录标识符。这意味着我无法使用第三方主键作为附加数据的主键。如果可以的话，我显然会使用该密钥并存储扩展数据。
例如，返回的数据是：
-----------------------------------------------------------------------------
| Client Id | Transaction Date | Transaction Amount | Description | Balance |
-----------------------------------------------------------------------------
Client Id 在该表中不是唯一的，但是可以保证其余信息整体上是唯一的。
我想添加额外的数据。例如：
-------------------------------------------------
| ... | Transaction Category | Hide Transaction |
-------------------------------------------------
我曾考虑过使用主键作为所有其他信息的哈希值，但据我所知，查询该数据的效率非常低。例如，我可能想在屏幕上显示 100 笔交易。这需要从第三方检索数据，对每条记录进行哈希处理，并使用这 100 个键之一查询我的本地数据库以获取所有数据。
有人有什么建议吗？
I have an application that receives most of its data from a third party. One of these sets of data is a transaction list. I can't write to this service, but I want to add more information to that data for use in my own application. I intend to do that with a SQL table with extended info.
The problem is that the third-party data does not return a record identifier from their side. That means I can't use a third-party primary key as the primary key for my additional data. If I could, I'd obviously just use that key and store extended data.
For example's sake, the data returned is:
-----------------------------------------------------------------------------
| Client Id | Transaction Date | Transaction Amount | Description | Balance |
-----------------------------------------------------------------------------
Client Id is not unique in this table, however, there is a guarantee that the rest of the information, taken in its totality, is unique.
I want to add additional data. For example:
-------------------------------------------------
| ... | Transaction Category | Hide Transaction |
-------------------------------------------------
I've toyed with the idea of using a primary key that is a hash of all of the other information, but querying for that data would be very inefficient as far as I could see. For example, I may want to display 100 transactions on screen. That would require retrieving the data from the third party, hashing each record, and querying my local database for all data with one of those 100 keys.
Does anyone have any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我想你回答了你自己的问题：
只需将这些字段设置为复合主键即可。您的 WHERE 子句会比平常稍微复杂一些，但这就是权衡。
如果您想让查询更简单，您还可以创建一个与复合键一起使用的代理键。
I think you answered your own question:
Just make those fields into a composite primary key. Your WHERE clauses will be a little more complex than normal but that's the trade off.
You could also create a surrogate key that would work with the composite key if you wanted to make your queries a little simpler.