在关系数据库中表示受约束的多对多关系的最佳方法是什么？ [英] What is the best way to represent a constrained many-to-many relationship within a relational database?

问题描述

我想建立一个多对多关系，并限制在该关系的每一侧都只能同时链接一个或没有实体。

I would like to establish a many-to-many relationship with a constraint that only one or no entity from each side of the relationship can be linked at any one time.

一个很好的比喻是汽车和停车位。有很多车，很多空间。一个空间可以容纳一辆汽车，也可以是空的。一辆车一次只能在一个空间内，也不能在一个空间内（不能停放）。

A good analogy to the problem is cars and parking garage spaces. There are many cars and many spaces. A space can contain one car or be empty; a car can only be in one space at a time, or no space (not parked).

我们有一个Cars表和一个Spaces表（可能还有一个链接表）。汽车表中的每一行代表一个汽车的唯一实例（具有许可证，所有者，模型等），而空间表中的每一行代表一个唯一的停车空间（具有车库楼层，行和编号的地址）。在数据库中链接这些表并强制执行上述约束的最佳方法是什么？

We have a Cars table and a Spaces table (and possibly a linking table). Each row in the cars table represents a unique instance of a car (with license, owner, model, etc.) and each row in the Spaces table represents a unique parking space (with address of garage floor level, row and number). What is the best way to link these tables in the database and enforce the constraint describe above?

（我正在使用C＃，NHibernate和Oracle。）

(I am using C#, NHibernate and Oracle.)

推荐答案

如果您不担心历史-即仅担心现在，请执行以下操作：

If you're not worried about history - ie only worried about "now", do this:

create table parking (
  car_id references car,
  space_id references space,
  unique car_id,
  unique space_id
);

通过使汽车和空间参考唯一，您将每一侧限制为最多一个链接-即一辆汽车最多只能停在一个空间中，而一个空间最多只能停一个车。

By making both car and space references unique, you restrict each side to a maximum of one link - ie a car can be parked in at most one space, and a space can has at most one car parked in it.

这篇关于在关系数据库中表示受约束的多对多关系的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！