如何构造数据库方案以允许“百万分之一”。案件？ [英] How to structure a database schema to allow for the "1 in a million" case?

问题描述

在数据库的所有表中，我有两个当前具有多对多联接的表。但是，几乎总是捕获的实际数据总体具有一对多关联。

Among all the tables in my database, I have two which currently have a Many-to-Many join. However, the actual data population being captured nearly always has a One-to-Many association.

考虑到我希望数据库查找（原则性查询）尽可能不受阻碍，我应该改为：

Considering that I want database look-ups (doctrine queries) to be as unencumbered as possible, should I instead:

• 在表之间创建两个关联（在这些例外情况下，第二个仅填充
  ）？


• 更改关联的数据类型（例如，到文本 / tinyblob ）来记录2个（或技术上甚至是3个）关联记录的微型数组？

• Create two associations between the tables (where the second is only populated in these exceptional cases)?
• Change the datatype for the association (eg to a text/tinyblob) to record a mini array of the 2 (or technically even 3) associated records?

这是我目前拥有的（尽管TableB-> JoinTable通常只是一对一）：

This is what I currently have (although TableB-> JoinTable is usually just one-to-one):

TableA.id --< a_id.JoinTable.b_id >-- TableB.id

所以，我正在寻找是否可以捕获异常。以下是正确的解决方法吗？

So, I am looking to see if I can capture the 'exceptions'. Is the below the correct way to go about it?

TableA.id     TableB.id
       +----< TableB.A_id1
       +----- TableB.A_id2
       +----- TableB.A_id3

推荐答案

您似乎感兴趣：

-- a and b are related by the association of interest
Foo(a, b)

-- foo(a, b) but not foo(a2, b) for some a2 <> a
Boring(a, b)
unique(b)
FK (a, b) references Foo

-- foo(a, b) and foo(a2, b) for some a2 <> a
Rare(a, b)
FK (a, b) references foo

如果您希望查询不受限制，只需定义Foo。

If you want queries to be unencumbered, just define Foo. You can query it for Rare.

Rare = select * from Foo f join Foo f2
    where f.a <> f2.a and f.b = f2.b

任何其他设计在保持数据库一致性方面都遭受了更新的复杂性。

Any other design suffers from update complexity in keeping the database consistent.

您对Rare的体积远小于Foo感到困惑。但是您的要求到底有多少，只有100万Foo记录中的n个：很多，您可以通过它们选择其他设计？

You have some fuzzy concern about Rare being much smaller than Foo. But what is your requirement re only n in a million Foo records being many:many by which you would choose some other design?

下一个复杂程度是要有Foo和Rare。更新必须使上面的等式保持正确。

The next level of complexity is to have Foo and Rare. Updates have to keep the above equation true.

减少Foo的2或3百万冗余的好处似乎不太可能+稀有，只有无聊+稀有并从它们中重建Foo。但是，为镗孔定义唯一的索引（b）可能会有所好处，该索引将保持其中的b只有一个a。当需要Foo时：

It seems extremely unlikely that there is a benefit in reducing the 2-or-3-in-a-million redundancy of Foo + Rare by only having Boring + Rare and reconstructing Foo from them. But it may be of benefit to define a unique index (b) for Boring which will maintain that a b in it has only one a. When you need Foo:

Foo = select * from Boring union select * from Rare

但您的更新必须保持这一点

But your updates must maintain that

not exists (select * from Boring b join Rare r where b.b = r.b)

这篇关于如何构造数据库方案以允许“百万分之一”。案件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！