如何重构数据表以允许通过动态可添加/可删除因素进行查找？

Question

今天早上我脑子一片空白，希望能从社区中得到一些想法。我的数据库中有一个需要重新建模的查找表 - 其基本示例是：

• Factor1
• Factor2
• Factor3
• Value

到目前为止这一切都很好，但业务现在决定用于查找值的因素应该是可变的由用户。

例如，他们可以添加一个具有可能值列表（即另一个表）的因子，并且可以将其合并到查找中，使用这种样式的表将需要添加一列，即因子4。

我的第一个想法是，我们不能授予用户在数据库中创建表、列、关系和索引的权限，这会带来太大的完整性风险，尽管这可能是最简单的情况。让设置新因子列的代码自动创建值表、生成关系、设置索引、修改查找存储过程等。但这一切似乎根本不是好的做法。

第二个想法是重新建模，以便将因子指定为表中的值，但今天早上，我对需要采取的方法一片空白，以便完成查找。首先，当我必须查找哪些因素决定哪个值时，如何完成查找...似乎我必须动态创建一个查询，但这似乎也不是喜欢走正确的路。

有什么想法吗？

假设我的（原始）表中有以下内容：

FactorValues
============
Factor1 | Factor2 | Factor3 | Value
-----------------------------------
 1         1         1         0.5
 1         1         2         1.0
 1         1         3         1.5
 1         2         1         2.0
 1         2         2         2.5
 1         2         3         3.0

我需要获得值 3.0（基于已知因素），这是小菜一碟

Select Value 
From   FactorValues 
Where  Factor1 = 1 
And    Factor2 = 2 
And    Factor3 = 3

现在，假设我已经重新建模到最终用户可以添加一个因素对于查找，我仍然需要能够根据以下语义提取值：

Select Value
From   FactorValues
Where  FirstUserDefinedFactor = FirstSelectedValue
And    SecondUserDefinedFactor = SecondSelectedValue
And    NthUserDefinedFactor = NthSelectedValue

因此，我的想法是我的值表将成为键/值查找，但我不确定如何对其进行建模那我可以基于多行而不是多列执行 where...

Answer 1

我认为您想要的一些内容将取决于您为用户构建的界面，但我认为界面直观地会受到设计的限制，所以这里是：

列出的数据类型当然是假设的，可以适当更改根据您的场景

创建一个表格，其中包含您希望用户可用的所有因素，或者您希望他们能够自行输入的因素：

Factor
-------
FactorID INT
Factor VARCHAR

创建另一组表格来建立因素组，以便您的用户可以输入多个作为单个条目：

FactorGroup
-----------
FactorGroupID INT
FactorGroup VARCHAR

FactorGroupDetail
-----------------
FactorGroupDetailID INT
FactorGroupID INT
FactorID INT

现在，假设您的值是预设的并且您不希望它们更改，您还需要一个表格来存储它们：

Value
-----
ValueID INT
ValueName VARCHAR
Value DECIMAL

否则，我认为您可以只粘贴 ValueFactorGroup 表中的 code> 字段。

最后，一个将因子组与值相匹配的表：

FactorValue
-----------
FactorGroupID INT
ValueID INT

我认为您需要的查询可能类似于：

SELECT v.Value
FROM Value AS v
INNER JOIN FactorValue AS fv
    ON v.ValueID = fv.ValueID
INNER JOIN FactorGroup AS fg
    ON fv.FactorGroupID = fg.FactorGroupID
INNER JOIN FactorGroupDetail AS fgd
    ON fg.FactorGroupID = fgd.FactorGroupID
INNER JOIN Factor AS f
    ON fgd.FactorID = f.FactorID
WHERE f.FactorID IN (
    SELECT FactorID
    FROM Factor AS ftor
    WHERE ftor.Factor = @user_entered_factor1
        OR ftor.Factor = @user_entered_factor2
        OR ftor.Factor = @user_entered_factor3
)

Answer 2

如果我正确理解了问题的要点，那么您需要设计一些可以捕获以下概念的东西：

f(X[i]) = y[i]

其中 f 是某种函数，X 是某种 n - 因素元组，y 是结果值。 （[i] 表示下标 i。）

假设 f 不会改变，那么至少两个表的方法可能会起作用。

Table A, Columns: Expression-Id, Factor-Index, Factor-Value

Table B, Columns: Expression-Id, Result-Value

Expression-Id表示一种特定的关系，即下标i。 结果值 是y。 Factor-Index 仅指示哪个因子（1=Factor1、2=Factor2、3=Factor3 等）具有给定的Factor-Value。

Expression-Id 上的Inner Join 可以将两个表连接在一起。

希望这有帮助！

---

或者采用单表方法，保留表 A 但删除表 B。使用 Factor-Index=0 来表示 y。