如何在RDMS中设计货币汇率表的最优数据模型

更优数据建模方案 #

如果你的现有存储方案是「单条记录对应一个基准货币，不同目标货币作为单独字段存储」的宽表结构，存在明显的扩展性缺陷：新增币种需要修改表结构新增字段，查询特定货币对汇率需要动态拼接字段，代码维护成本很高。
更推荐采用窄表结构，单条记录对应唯一的「日期+基准货币+目标货币」组合，字段设计如下：

• rate_date：汇率所属日期，若需要日内多频次拉取可新增rate_timestamp字段存储精确时间戳
• base_currency：基准货币编码，如USD、EUR
• target_currency：目标货币编码，如GBP、RUB
• exchange_rate：汇率值，建议用DECIMAL(18,6)定点类型存储，避免浮点型的精度丢失问题
• created_at：可选字段，记录数据写入时间用于问题排查

主键采用联合主键 (rate_date, base_currency, target_currency)，可天然实现同日期同货币对的去重，也适配绝大多数按日期、货币对查询的业务场景。

这个方案的优势非常明确：

• 扩展性极强，新增币种无需修改表结构，直接写入对应记录即可
• 适配API返回结构，解析代码逻辑统一：只需要遍历API返回中除base_currency外的所有键值对，逐一生成target_currency和对应exchange_rate的记录写入即可
• 查询逻辑极简，例如查询2024-05-20 USD兑GBP的汇率，只需执行：

SELECT exchange_rate FROM currency_rate 
WHERE rate_date = '2024-05-20' AND base_currency = 'USD' AND target_currency = 'GBP';

性能问题说明 #

按上述方案，每日写入量完全可控：全球主流流通货币不足200种，就算覆盖所有主流货币作为基准货币，单日新增记录也仅为200*200=4万条左右，就算每小时拉取一次单日也不足100万条，对常规RDMS完全没有写入压力。
就算后续累计了千万级以上的历史数据，只要做好以下优化，不会出现性能问题：

• 按rate_date做表分区，绝大多数业务查询只会访问近期或指定日期的数据，分区可直接过滤大量冷数据，查询效率提升非常明显
• 只保留联合主键索引，避免多余索引增加写入开销
• 定期归档超过半年/一年的冷数据到单独的归档表，主表仅保留高频访问的热数据即可。

内容的提问来源于stack exchange，提问作者Learner