两个半相关连接表的SQL架构帮助

Question

我在用适合数据库的方式表示项目的数据时遇到了困难。为了提供帮助，我为示例设置了一个SQL：http://sqlfiddle.com/#!2/6d01c2/14/0

最简单的是，我有三个表:基金、FundReturn和FactorReturn。基金通过FundReturn每月有很多回报。基金也有一个地区(如美国或外国)，这些地区有所谓的FactorReturn。因此，因子回报与基金的关系不是直接的，而是通过基金所属的地区。

**Fund**
fund_symbol  | varchar(5)    | PRI
region_key   | varchar(255)  |

**FundReturn**
fund_symbol  | varchar(255)  | PRI
return_month | int(10)       | PRI
return_value | decimal(5,4)  | 

**FactorReturn**
region_key   | varchar(255)  | PRI
factor_key   | varchar(255)  | PRI
return_month | int(10)       | PRI
return_value | decimal(5,4)  | 

目标是在给定的时间内提取基金的收益以及相应的因素回报来进行一些分析。最后的表示方式如下所示：

fund_symbol | month  | fund_return | factor_ret_1 | factor_ret_2 | factor_ret_3
VTI         | 201001 | 0.0100      | 0.0200       | -0.0100      | 0.0000
VTI         | 201002 | 0.0500      | 0.0300       |  0.0300      | 0.0010
VTI         | 201003 | 0.0300      | 0.0100       | -0.0200      | 0.0020

此外，我还使用了PHP，并将数据映射为能够使用以下方法：

$fund = $em->find('VTI');
$fund->getFundReturns();   // Would return array with FundReturn objects.
$fund->getFactorReturns(); // Would return array with FactorReturn objects.

问题

由于数据的性质，试图在一个查询中查询基金回报和因子返回都会以指数方式增加返回的行数。一旦我尝试在ORM中使用它，这种情况就会更加严重，因为不可能构建具有广泛日期范围的对象图。使用多个查询是我目前的解决方案，但这在使用ORM时很棘手，因为从代码中错误地访问关系可能会触发大量SQL查询。

http://sqlfiddle.com/#!2/6d01c2/14/0的查询显示了查询仅12个月时是如何返回432行的。

问题

所以我的问题是：

1. 是否有更好的方法在数据库中表示这些数据？
2. 在SQL中查询数据的正确方法是什么？在ORM里？

Answer 1

首先，考虑到任何解决方案最终无疑都会使用‘联接’，我将生成所有外键整数值。这将使您的表关系更快，存储和更新速度更经济。因此，例如，基金表将有一个整数唯一ID (例如1)和一个符号(自然键，例如VTI)，您可以在这里读到更多：Surrogate vs. natural/business keys。

而且，您可能会沿着实体属性值路径走下去，这一点已经讨论过了。Entity Attribute Value Database vs. strict Relational Model Ecommerce

该实体属性值模型查询复杂，但为用户添加自己的因子返回键提供了灵活性。

第二，如果你沿着这条路走下去，你可能会在大多数ORM中苦苦挣扎。我会尝试手动构建查询，如下所示：

    SELECT f.*
      ,fr.return_month AS fund_return_month
      ,fr.return_value AS fund_return
      ,hml.return_value AS hml
      ,mkt.return_value AS mkt
      ,smb.return_value as smb

    FROM Fund f
    INNER JOIN FundReturn fr ON f.fund_symbol = fr.fund_symbol

    LEFT JOIN FactorReturn hml 
    ON f.region_key = hml.region_key
    AND hml.factor_key = 'hml'
    AND hml.return_month = fr.return_month

    LEFT JOIN FactorReturn mkt 
    ON f.region_key = mkt.region_key
    AND mkt.factor_key = 'mkt'
    AND mkt.return_month = fr.return_month

    LEFT JOIN FactorReturn smb 
    ON f.region_key = mkt.region_key
    AND smb.factor_key = 'smb'
    AND smb.return_month = fr.return_month

    WHERE f.fund_symbol = 'VTI'
    AND fr.return_month BETWEEN 201001 AND 201012
    AND hml.return_month BETWEEN 201001 AND 201012;