如何将事物的组合映射到关系数据库？

发布于 2024-09-02 14:19:56 字数 739 浏览 3 评论 0原文

我有一个表，其记录代表某些对象。为了简单起见，我假设该表只有一列，即唯一的 ObjectId。现在我需要一种方法来存储该表中的对象组合。组合必须是唯一的，但可以是任意长度。例如，如果我有 ObjectId，

1,2,3,4

我想存储以下组合：

{1,2}, {1,3,4}, {2,4}, {1,2,3,4}

不需要排序。我当前的实现是有一个将 ObjectId 映射到 CombinationId 的表 Combinations。因此，每个组合都会收到一个唯一的 ID：

ObjectId | CombinationId
------------------------
1        | 1
2        | 1
1        | 2
3        | 2
4        | 2

这是上例中前两个组合的映射。问题是，查找特定组合的 CombinationId 的查询似乎非常复杂。该表的两个主要使用场景是迭代所有组合，以及检索特定组合。该表将被创建一次并且永远不会更新。我通过 JDBC 使用 SQLite 。是否有更简单的方法或最佳实践来实现这种映射？

原文

I have a table whose records represent certain objects. For the sake of simplicity I am going to assume that the table only has one column, and that is the unique ObjectId. Now I need a way to store combinations of objects from that table. The combinations have to be unique, but can be of arbitrary length. For example, if I have the ObjectIds

1,2,3,4

I want to store the following combinations:

{1,2}, {1,3,4}, {2,4}, {1,2,3,4}

The ordering is not necessary. My current implementation is to have a table Combinations that maps ObjectIds to CombinationIds. So every combination receives a unique Id:

ObjectId | CombinationId
------------------------
1        | 1
2        | 1
1        | 2
3        | 2
4        | 2

This is the mapping for the first two combinations of the example above. The problem is, that the query for finding the CombinationId of a specific Combination seems to be very complex. The two main usage scenarios for this table will be to iterate over all combinations, and the retrieve a specific combination. The table will be created once and never be updated. I am using SQLite through JDBC. Is there any simpler way or a best practice to implement such a mapping?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦言归人 2024-09-09 14:19:56

问题是，查找特定组合的 CombinationId 的查询似乎非常复杂。

应该不会太糟糕。如果您想要包含所选项目的所有组合（允许附加项目），则类似于：

SELECT combinationID
FROM Combination
WHERE objectId IN (1, 3, 4)
GROUP BY combinationID
HAVING COUNT(*) = 3 -- The number of items in the combination

如果您只需要特定组合（不允许附加项目），则可能更像：

SELECT combinationID FROM (
   -- ... query from above goes here, this gives us all with those 3
) AS candidates

-- This bit gives us a row for each item in the candidates, including 
-- the items we know about but also any 'extras'
INNER JOIN combination ON (candidates.combinationID = combination.combinationID)

GROUP BY candidates.combinationID
HAVING COUNT(*) = 3 -- Because we joined back on ALL, ones with extras will have > 3

您还可以在此处使用 NOT EXISTS (或者在原始查询中），这似乎更容易解释。

最后，您也可以有一个简单的查询，

SELECT combinationID
FROM Combination AS candidates
INNER JOIN Combination AS allItems ON 
  (candidates.combinationID = allItems.combinationID)
WHERE candidates.objectId IN (1, 3, 4)
GROUP BY combinationID
HAVING COUNT(*) = 9 -- The number of items in the combination, squared

换句话说，如果我们正在寻找 {1, 2}，并且存在与 {1, 2, 3} 的组合，我们将有一个 {candidates , allItems} JOIN 结果：

{1, 1}, {1, 2}, {1, 3}, {2, 1}, {2, 2}, {2, 3}

额外的 3 导致 COUNT(*) 在 GROUP 后为 6 行，而不是 4，所以我们知道这不是我们追求的组合。

The problem is, that the query for finding the CombinationId of a specific Combination seems to be very complex.

Shouldn't be too bad. If you want all combinations containing the selected items (with additional items allowed), it's just something like:

SELECT combinationID
FROM Combination
WHERE objectId IN (1, 3, 4)
GROUP BY combinationID
HAVING COUNT(*) = 3 -- The number of items in the combination

If you need only the specific combination (no extra items allowed), it can be more like:

SELECT combinationID FROM (
   -- ... query from above goes here, this gives us all with those 3
) AS candidates

-- This bit gives us a row for each item in the candidates, including 
-- the items we know about but also any 'extras'
INNER JOIN combination ON (candidates.combinationID = combination.combinationID)

GROUP BY candidates.combinationID
HAVING COUNT(*) = 3 -- Because we joined back on ALL, ones with extras will have > 3

You can also use a NOT EXISTS here (or in the original query), this seemed easier to explain.

Finally you could also be fancy and have a single, simple query

SELECT combinationID
FROM Combination AS candidates
INNER JOIN Combination AS allItems ON 
  (candidates.combinationID = allItems.combinationID)
WHERE candidates.objectId IN (1, 3, 4)
GROUP BY combinationID
HAVING COUNT(*) = 9 -- The number of items in the combination, squared

So in other words, if we're looking for {1, 2}, and there's a combination with {1, 2, 3}, we'll have a {candidates, allItems} JOIN result of:

{1, 1}, {1, 2}, {1, 3}, {2, 1}, {2, 2}, {2, 3}

The extra 3 results in COUNT(*) being 6 rows after GROUPing, not 4, so we know that's not the combination we're after.

回复收藏 0 原文

流云如水 2024-09-09 14:19:56

这可能是异端邪说，但对于您的使用场景，使用非规范化结构可能会更好，其中您将组合本身存储为某种复合（文本）值：

CombinationId | Combination
---------------------------
1             | |1|2|
2             | |1|3|4|

如果您制定规则，在生成复合时始终对 ObjectId 进行排序值，很容易检索给定对象集的组合。

This may be heresy, but for your usage scenarios it might work better to use a denormalized structure where you store the combinations themselves as some kind of composite (text) value:

CombinationId | Combination
---------------------------
1             | |1|2|
2             | |1|3|4|

If you make the rule that you always sort the ObjectIds when generating the composite value, it's easy to retrieve the Combination for a given set of Objects.

回复收藏 0 原文