分层配置选择

Question

编辑：对不起，困惑。刚刚从我的老板那里发布了这个模式的这一部分。如果允许我发布图片，我会在原始帖子中详细说明。

我有一个看起来像这样的配置模式：

http://img717.imageshack.us/img717/7297/heirarchy.png

每个级别都包含它下面的水平内（即 - 合作伙伴有多个程序），并且每个配置水平的股票配置键与其他类型的配置级别（即 - 默认时区可以在合作伙伴级别设置，然后从程序，产品组合或设备级别覆盖）。

这使得我们可以做的是对某种类型的对象具有默认值，然后用更具体的分类法覆盖它。例如：

假设我有一个合作伙伴对象是一家公司。假设hierarchy_configuration_key 1是默认的时区。我提出了一个partner_configuration，最常见的是，该合作伙伴将位于东海岸（纽约时间）。

现在我有该合作伙伴支持的多个程序。说具体的节目是根据加利福尼亚。我提出了一个program_configuration，它表示该程序中的设备是萨克拉门托时间。

现在，让我们跳过投资组合，并说有人报名参加了加利福尼亚州的这个计划，然后转移到丹佛，但仍然是客户。我们设置了一个设备配置，表示他们现在在Mountain时间。

层次结构是这样的：

Level  |Timezone (hierarchy_configuration_key 1) 
--------------------------------------------------- 
Partner |NYC 
Program |Sacramento 
Portfolio |null (defaults to most granular above it, so Sacramento) 
Device |Denver 

现在我要选择我的配置由hierarchy_configuration_key_id分组：

我可以使用内部连接遍历的水平，但我希望有一个选择给我这样的结果对于该装置的主键（DEVICE_ID）（由hierarchy_configuration_key_id分组）：

device_id |portfolio_id |program_id |partner_id |device_config |portfolio_config |program_config| partner_config 
--------------------------------------------------------------------------------------------------------------------- 
1   |2   |1   |35   |Denver  |null    |Sacramento | NYC 

也是可接受的。将一个选择，只是给了我最相关的配置值，即：

device_id |portfolio_id |program_id |partner_id |config_value 
------------------------------------------------------------- 
1   |2   |1   |35   |Denver  

在此先感谢。让我知道你是否需要更多的澄清。

Answer 1

我认为不会在这里工作的唯一部分所指出的@ EugenRieck的评论...
- Which field tells the Miata it is a Child of Mazda?

我会稍微改变结构...

ENTITY_TABLE 

entity_id | parent_entity_id | entity_name 
    1   NULL   Vehicle 
    2    1   Car 
    3    2   Mazda 
    4    3   Miata 
    5    1   Cycle 
    6    5   Unicycle 
    7    6   Broken Unicycle 


PROPERTY_TABLE 

entity_id | property_type | value 
    1   Wheels  4 
    2   Wheels  NULL 
    3   Wheels  NULL 
    4   Wheels  NULL 
    5   Wheels  2 
    6   Wheels  1 
    7   Wheels  0 

    (And repeated for other property types as appropriate) 


-- Every entity must have the same properties as the parents 
-- (otherwise you have to find the topmost parent first to know what properties exist) 

-- An entity may only have 1 parent 

-- The topmost parent must have a NULL parent_id 

-- The bottommost parent must be no more than 3 joins away from the topmost parent 

然后你可以有这样的东西...

SELECT 
    entity1.id, 
    property1.property_type, 
    entity1.name, 
    entity2.name, 
    entity3.name, 
    entity4.name, 
    property1.value, 
    property2.value, 
    property3.value, 
    property4.value, 
    COALESCE(property1.value, property2.value, property3.value, property4.value) AS inherited_value 
FROM 
    entity    AS entity1 
LEFT JOIN 
    entity    AS entity2 
    ON entity2.id = entity1.parent_id 
LEFT JOIN 
    entity    AS entity3 
    ON entity3.id = entity2.parent_id 
LEFT JOIN 
    entity    AS entity4 
    ON entity4.id = entity3.parent_id 
INNER JOIN 
    property    AS property1 
    ON property1.entity_id = entity1.id 
LEFT JOIN 
    property    AS property2 
    ON property2.entity_id  = entity2.id 
    AND property2.property_type = property1.property_type 
LEFT JOIN 
    property    AS property3 
    ON property3.entity_id  = entity3.id 
    AND property3.property_type = property1.property_type 
LEFT JOIN 
    property    AS property4 
    ON property4.entity_id  = entity4.id 
    AND property4.property_type = property1.property_type 
WHERE 
    entity1.id    = @entity_id 
AND property1.property_type = @property_type 

Answer 2

此解决方案是基于您的架构与@ param1是hierarchy_con figuration_key_id和@ param2是所需的device_id。它使用类似于Dems'的方法，尽管除了借用COALESCE之外，它是独立运作的。

SELECT *, 
IF(dv_key IS NOT NULL,'device',IF(pf_key IS NOT NULL,'portfolio',IF(pg_key IS NOT NULL,'program',IF(pt_key IS NOT NULL,'partner',NULL)))) AS hierarchy_level, 
COALESCE(dv_key,pf_key,pg_key,pt_key) AS key_id, 
COALESCE(dv_value,pf_value,pg_value,pt_value) AS value 
FROM 
(SELECT sim_id, 
dv.device_id, pt.partner_id, pg.program_id, pf.portfolio_id, 
dvc.hierarchy_configuration_key_id AS dv_key, dvc.configuration_value AS dv_value, 
pfc.hierarchy_configuration_key_id AS pf_key, pfc.configuration_value AS pf_value, 
pgc.hierarchy_configuration_key_id AS pg_key, pgc.configuration_value AS pg_value, 
ptc.hierarchy_configuration_key_id AS pt_key, ptc.configuration_value AS pt_value 
FROM device dv 
LEFT JOIN portfolio pf USING(portfolio_id) 
LEFT JOIN program pg USING(program_id) 
LEFT JOIN partner pt USING(partner_id) 
LEFT JOIN device_configuration dvc ON dv.device_id=dvc.device_id AND [email protected] AND dvc.active='true' 
LEFT JOIN portfolio_configuration pfc ON pf.portfolio_id=pfc.portfolio_id AND [email protected] AND pfc.active='true' 
LEFT JOIN program_configuration pgc ON pg.program_id=pgc.program_id AND [email protected] AND pgc.active='true' 
LEFT JOIN partner_configuration ptc ON pt.partner_id=ptc.partner_id AND [email protected] AND ptc.active='true' 
WHERE dv.device_id = @param1) hierchy;