关系数据库设计 - 两个关系 1:1 还是一个 1:2？

Question

这道题是关于如何设计SQL关系的。我在这件事上是个新手，我想知道更多专家的答案...

我目前正在使用 MeGrok 和 SqlAlchemy 将 ZopeDB（面向对象）数据库迁移到 MySQL（关系）（尽管我不这样做）认为这真的不太相关，因为我的问题更多是关于在关系数据库中设计关系）。

我有两个这样相关的类：

class Child(object):
    def __init__(self):
        self.field1 = "hello world"

class Parent(object):
    def __init__(self):
        self.child1 = Child()
        self.child2 = Child()

“Parent”类有两个不同的 Child() 类实例。我什至不确定如何对待这个（两个不同的 1:1 关系或 1:2 关系）。

目前，我有这个：

class Child(rdb.Model):
    rdb.metadata(metadata)
    rdb.tablename("children_table")
    id = Column("id", Integer, primary_key=True)
    field1 = Column("field1", String(64))   #Irrelevant
    def __init__(self):
        self.field1 = "hello world"

class Parent(rdb.Model):
    rdb.metadata(metadata)
    rdb.tablename("parent_table")

    id = Column("id", Integer, primary_key=True)
    child1_id = Column("child_1_id", Integer, ForeignKey("children_table.id"))
    child2_id = Column("child_2_id", Integer, ForeignKey("children_table.id"))

    child1 = relationship(Child,
        primaryjoin = ("parent_table.child1_id == children_table.id")
    )

    child2 = relationship(Child,
        primaryjoin = ("parent_table.child2_id == children_table.id")
    ) 

意思是...好吧，我将两个“子”id 作为外键存储在父级中，并使用该信息检索子级本身。

这工作正常，但我不知道这是否是最合适的解决方案。

或者我可以这样做：

class Child(rdb.Model):
    rdb.metadata(metadata)
    rdb.tablename("children_table")
    id = Column("id", Integer, primary_key=True)
    parent_id = Column("id", Integer, ForeignKey("parent_table.id"))  # New!
    type = Column("type", ShortInteger) # New!

    field1 = Column("field1", String(64))  #Irrelevant
    def __init__(self):
        self.field1 = "hello world"

class Parent(rdb.Model):
    rdb.metadata(metadata)
    rdb.tablename("parent_table")

    id = Column("id", Integer, primary_key=True)
    child1 = relationship(
        # Well... this I still don't know how to write it down,
        # but it would be something like:
        #   Give me all the children whose "parent_id" is my own "id"
        #   AND their type == 1
        # I'll deal with the joins and the actual implementation depending 
        # on your answer, guys
    )

    child2 = relationship(
        #  Would be same as above
        #  but selecting children whose type == 2
    )

这可能有利于向父类添加新的子级...如果我添加“Parent.child3”，我只需要创建一个与现有关系非常相似的新关系。

我现在的方式意味着创建一个新的关系并向父级添加一个新的外键。

另外，拥有一个带有一堆外键的“父”表可能不会使它成为世界上最好的“父”表，对吧？

我想知道对数据库了解更多的人是怎么想的:)

谢谢。

PS：相关帖子？ 问题 3998545

Answer 1

针对评论进行扩展

问题是，您正在用您知道（可以理解）的术语进行思考，并且您具有面向对象数据库的局限性......这不太好延续到关系数据库。因此，出于多种原因，最好简单地识别实体和关系，并对它们进行规范化。您用来调用的方法很容易改变，并且您不会仅限于现在所拥有的。

这里有一些很好的答案，但即使这些答案也是有限且不完整的。如果您标准化 Parent 和 Child（作为人，他们将有许多公共列），您将得到 Person，没有重复的列。

人们与其他人、他们的父母有“向上”的关系，但那是上下文，而不是父母首先作为一个人存在的事实（如果你愿意，你可以有两个以上）。人们与孩子也有“向下”的关系，也是有背景的。每个父母有两个孩子的限制是荒谬的（您可能必须检查您的方法/类：我怀疑一个是“向上”导航，另一个是“向下”）。并且您不想将关系存储为重复项（一旦 Fred 是 Sally 的父亲；两次 Sally 是 Fred 的孩子），该单个事实存在于单行中，可以解释为 Parent⇢Child 或父母⇠孩子。

这个要求在很多问题中都出现过，因此我使用一个通用但详细的说明。该模型定义了任何需要向上或向下遍历的树结构，通过简单的递归处理。它称为物料清单结构，最初是为库存控制系统创建的，可应用于任何树形结构要求。它是第五范式；没有重复的列；无更新异常。

物料清单

对于装配体和零部件，它们有很多公共列，它们被归一化为零件；它们是组件还是组件都是上下文相关的，并且这些上下文列位于关联（多对多）表中。

两个关系 1:1 还是一个 1:2？

实际上，它是 1::n 的两倍。

序号或排名在主键中是明确的（按时间顺序）。如果需要其他序数，只需向关联表添加一列即可。更好的是，它确实是一个派生列，因此可以在运行时根据当前值进行计算。

Answer 2

我承认我对对象数据库不太熟悉，但从关系术语来看，这是一种简单的一对多（可选）关系。

create table parent (
  id           int PK,
  otherField   whatever
)

create table child (
  id           int PK,
  parent_id    int Fk,
  otherField   whatever
)

显然，这不是可用的代码......

我认为这与您的第二个示例类似。如果您需要跟踪子级在与父级的关系中的顺序位置，您可以向子表添加一列，例如：

create table child (
  id           int PK,
  parent_id    int Fk,
  birth_order  int,
  otherField   whatever
)

您必须负责在应用程序级别管理该字段，这不是什么您可以期待 DBMS 为您做的事情。

我将其称为可选关系，假设没有孩子的父母可以存在 - 如果这不是真的，那么它在逻辑上就成为必需的关系，尽管您仍然必须让 DBMS 创建新的父记录无子项，然后获取其 id 来创建子项，并再次在应用程序级别管理需求。

Answer 3

这可能有点断章取义，因为我没有使用你提到的任何东西 - 但就总体设计而言，这里有一些想法：

1. 保持基于常见类型的关系：has_one、has_many、belongs_to、has_and_belongs_to_many 。
2. 对于孩子，最好不要显式指定 N 个孩子；要么没有，要么有一个，或者可能有很多。因此，您的 child1 和 child2 模型声明将被替换为单个属性 - 包含子项的数组。

老实说，我不知道它与您正在使用的产品的契合程度如何。然而，这通常就是 ORM 意义上关系的运作方式。因此，基于此：

1. 如果一个模型属于另一个模型（它具有另一个表的外键），则它将具有一个 parent [sic] 属性，并引用父对象
2. 如果一个模型具有属于它的一个模型（另一个模型具有第一个模型表的外键），它将有一个 child [sic] 属性，其中包含对子对象的引用
3. 如果一个模型有多个模型属于它的（许多其他模型具有第一个模型表的外键），它将具有一个 children [sic] 属性，该属性是对子对象的引用的数组
4. 如果模型具有并属于许多其他模型...您可能需要考虑使用 parents 和 children 属性，或类似的东西；命名法并不重要，重要的是您可以访问它所属的一组模型以及属于它的另一组模型。

抱歉，如果这完全没有帮助，但它可能会带来一些启发。 HTH。