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

Question

这个问题是关于如何设计SQL关系的。我对这个话题还很陌生，希望能听到更多专家的意见...

我现在正在把一个ZopeDB（面向对象的）数据库迁移到MySQL（关系型数据库），使用的是MeGrok和SqlAlchemy（不过我觉得这不是重点，因为我的问题主要是关于在关系型数据库中设计关系）。

我有两个类，它们的关系是这样的：

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

class Parent(object):
    def __init__(self):
        self.child1 = Child()
        self.child2 = 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”，我只需要创建一个与现有关系非常相似的新关系。

而我现在的做法则意味着需要创建一个新关系，并且还要在父类中添加一个新的外键。

另外，拥有一个包含许多外键的“父类”表，可能并不是世界上最好的“父类”表，对吧？

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

谢谢。

附注：相关帖子？ 问题 3998545

Answer 1

这段话可能有点跑题，因为我用的东西和你提到的都不一样。不过就整体设计来说，我有几个想法：

1. 保持关系基于共同的类型：比如有一个、很多、属于某个东西、和很多东西都有关系。
2. 关于子项，最好不要明确指定有多少个子项；可能没有、一个，或者可能有很多个。所以你可以用一个属性来替代 child1 和 child2，这个属性是一个包含所有子项的数组。

老实说，我不知道这些想法和你用的东西契合得怎么样。不过，这大致上是ORM（对象关系映射）中关系的工作方式。所以，基于这些：

1. 如果一个模型属于另一个模型（它有一个指向另一个表的外键），那么它会有一个 parent 属性，指向父对象。
2. 如果一个模型有一个属于它的模型（那个模型有一个指向第一个模型表的外键），那么它会有一个 child 属性，指向子对象。
3. 如果一个模型有很多属于它的模型（很多其他模型有指向第一个模型表的外键），那么它会有一个 children 属性，这个属性是一个包含所有子对象的数组。
4. 如果一个模型和很多其他模型都有关系……你可能需要考虑使用 parents 和 children 属性，或者类似的东西；命名不那么重要，重要的是你能访问到它所属的一组模型，以及另一组属于它的模型。

抱歉如果这些内容没什么帮助，但希望能给你带来一些启发。祝好运！

Answer 2

根据评论扩展内容

问题在于，你在用你熟悉的方式思考（这很正常），而且你对面向对象数据库的理解有一些局限……这些局限不应该带到关系型数据库中。因此，出于很多原因，最好的做法是简单地识别出实体和关系，并对它们进行规范化。你调用的方法很容易改变，你不会仅仅局限于现在所拥有的东西。

这里有一些不错的回答，但即使那些也有限且不完整。如果你对父母和孩子进行规范化（作为人，他们会有很多共同的列），你就会得到一个“人”的表，没有重复的列。

人们与其他人有“向上”的关系，比如他们的父母，但这只是上下文，并不是说父母首先作为一个人存在（如果你愿意，可以有多个父母）。人们也与他们的孩子有“向下”的关系，这也是上下文。每个父母只能有两个孩子的限制是荒谬的（你可能需要检查一下你的方法/类：我怀疑一个是“向上”导航，另一个是“向下”）。而且你不想把关系存储为重复的（比如说，弗雷德是萨莉的父亲这件事只需要存一次，而不是两次），这个事实只存在于一行中，可以理解为父母⇢孩子或父母⇠孩子。

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

物料清单

对于有很多共同列的组件和装配件，它们被规范化为“零件”；无论它们是装配件还是组件都是上下文相关的，这些上下文列位于关联表（多对多表）中。

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

实际上，它是两次1::n。

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

Answer 3

我承认我对对象数据库不是很熟悉，但从关系数据库的角度来看，这其实是一个简单的一对多（可选）关系。

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）能为你处理的事情。

我称之为可选关系，是因为我假设可以存在没有孩子的父母——如果这不成立，那就变成了逻辑上的必需关系。不过，你仍然需要让数据库管理系统创建一个没有孩子的父母记录，然后获取它的ID来创建孩子——这同样需要在应用层面管理这个要求。