database-design - 多币种信息系统，SQL不准确

Question

我正在开发必须处理多种货币(美元、欧元、日元)的信息系统。比方说，用户可以向系统添加产品。用户于 2017 年 2 月 13 日添加产品 #1，价格 2000，货币日元。用户于 2017 年 2 月 14 日添加产品 #2，价格 25，货币美元。

稍后(2017 年 2 月 15 日)用户可以查看和过滤他的产品，假设用户想要以美元查看产品并且有价格范围过滤器。

目标:

• 用户设置了一些价格范围，我必须在数据库中正确地找到这些产品

我目前的做法是:

• 我已经决定，欧元是我的统一货币
• 当用户添加产品时 - 使用今日汇率将每个产品价格转换为欧元
• 在数据库中，存储原始价格和货币(来自用户输入)，存储转换后的价格(欧元)
• 过滤器中的价格范围由今日汇率初始化，因此 2000 日元转换为美元作为下限(17.5 美元)，20 美元作为上限

问题:

• 用户设置限额从 17.5 美元到 20 美元
• 我将 17.5 美元转换为 EUR_value_1，将 20 美元转换为 EUR_value_2
• 选择 price_unified 在 EUR_value_1 和 EUR_value_2 之间的数据库
• 值与数据库中的产品不匹配，因为使用率可能更高/更低(每天)所以值不同

我的问题是:

• 在数据库中存储这些产品、价格、货币的正确方法是什么？我应该存储所有货币值吗？
• 如何将前端行为作为某种货币的价格范围过滤器来处理，但仍然能够正确地在数据库中找到产品？
• 我应该创建一个 cron 以按照今天的汇率重新转换所有价格吗？

Answer 1

汇率表在国际金融应用程序中很常见。通常，一切都被引用为与一种货币(例如美元)的比率。每种货币每天只需要一个条目，通常是前一天的收盘汇率——除了基础货币，它始终为 1。像这样:

Effective  Code Factor
2015-05-02 GBP  0.662251656 --based on today's rate of 1.51 GBP->USD

从美元到英镑:美元 * 系数 = 英镑
从 GBP 到 USD:GBP/factor = USD

有一个幻灯片演示展示了这样的表格设计here .货币讨论从幻灯片/第 12 页开始，查询从第 16 页开始。表格设计很简单:

create table XRates(
    Code       char( 3 ) not null,
    Effective  date not null,
    Factor     decimal( 12, 9 ) not null
    constraint PK_XRates primary key( Code, Effective )
);

该设计有几个不错的功能，即您可以在同一个表中维护当前汇率和历史汇率，而无需每日更新。只有当汇率变化时——或者当汇率变化足以触发更新时，才会进行输入。查询返回在给定日期生效的汇率，即使该汇率是由一个星期前的条目建立的(在今天的货币市场中不太可能)。

当然，您可以将基础货币设置为欧元，但表设计和查询不会改变。

因此您只需存储产品的价格和货币。定价为 2000 日元的产品的欧元价格可以根据有效的换算率轻松换算，例如，下订单当天。