在关系数据库中表示受限多对多关系的最佳方式是什么？
我想建立一种多对多关系，其约束是关系的每一方在任何时候只能链接一个或没有实体。
这个问题的一个很好的类比是汽车和停车库空间。有很多车和很多空间。一个空间可以容纳一辆车，也可以是空的；一辆车一次只能停在一个车位，或者没有车位（不能停放）。
我们有一个 Cars 表和一个 Spaces 表（可能还有一个链接表）。 cars 表中的每一行代表汽车的唯一实例（具有许可证、所有者、型号等），Spaces 表中的每一行代表一个唯一的停车位（具有车库楼层地址、行和编号）。链接数据库中的这些表并强制执行上述约束的最佳方法是什么？
（我使用 C#、NHibernate 和 Oracle。）
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).
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?
(I am using C#, NHibernate and Oracle.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您不担心历史 - 即只担心“现在”，请执行以下操作：
通过使汽车和空间参考都唯一，您可以将每一侧限制为最多一个链接 - 即一辆车最多可以停在一个链接中车位，一个车位最多可以停放一辆车。
If you're not worried about history - ie only worried about "now", do this:
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.
在任何关系数据库中，多对多关系都必须有一个连接表来表示组合。正如答案中所提供的（但没有太多的理论背景），如果中间没有一个表来存储所有组合，则无法表示多对多关系。
解决方案中还提到，如果您不需要历史记录，它只能解决您的问题。相信我，现实世界的应用程序几乎总是需要表示历史数据。有很多方法可以做到这一点，但一个简单的方法可能是使用附加表创建所谓的三元关系。理论上，您可以创建一个“时间”表，该表还将其主键（例如不同的时间戳）与其他两个源表的继承键链接起来。这将使您能够防止两辆车同时位于同一停车位的错误。使用时间表可以让您能够使用简单的整数 ID 为多个停车位重复使用相同的时间数据。
所以，你的数据表可能看起来像
这样
car_id（整数/数字索引速度最快）
...
桌子停车位
空间ID
位置
表时隙
time_id 整数
begin_datetime（除非必须，否则不要使用秒！）
end_time（除非必须，否则不要使用秒！）
现在，这就是有趣的地方。您添加带有复合主键的中间表，该复合主键由 car.car_id + parking_space.space_id + time_id 组成。您还可以添加其他内容来优化这里，但我希望您明白了。
餐桌预订
car_id PK
停车位 ID PK
time_id PK（它是一个整数 - 只是尽量保持它尽可能高的粒度 - 30分钟增量或其他 - 如果你允许它包括秒/毫秒/等，那么优点就会被取消，因为你不能重复使用相同的值时间表中的值）
（这也是存储与该特定帐户、预订等不同的可变费率、折扣等的地方）。
现在，您可以减少数据量，因为您没有复制连接表（保留）中的时间戳。通过使用整数，您可以为多个停车位重复使用该时间段，但您也可以应用约束，防止两辆车在给定日期/时间范围内的同一“时间段”租用该给定停车位。这也将使存储有关客户的一些历史记录变得更容易 - 谁知道，您可能想查看有关经常租赁并向他们提供折扣或其他东西的客户的报告。
通过使用三元关系模型，您可以使每个停车位对于给定的时间段都是唯一的（可能添加了一些验证规则），因此系统在一个给定的时间段内只能在一个停车位中存储一辆车。
通过使用整数作为键而不是时间戳，可以确保数据库不需要做任何繁重的工作来索引键和排序/查询。当您拥有大量数据集并且需要效率时，这是数据仓库/OLAP 报告中的常见做法。我认为这也适用于这里。
in any relational database, many to many relationships must have a join table to represent the combinations. As provided in the answer (but without much of the theoretical background), you cannot represent a many to many relationship without having a table in the middle to store all the combinations.
It was also mentioned in the solution that it only solves your problem if you don't need history. Trust me when I tell you that real world applications almost always need to represent historical data. There are many ways to do this, but a simple method might be to create what's called a ternary relationship with an additional table. You could, in theory, create a "time" table that also links its primary key (say a distinct timestamp) with the inherited keys of the other two source tables. this would enable you to prevent errors where two cars are located in the same parking spot during the same time. using a time table can allow you the ability to re-use the same time data for multiple parking spots using a simple integer id.
So, your data tables might look like so
table car
car_id (integers/numbers are fastest to index)
...
table parking-space
space_id
location
table timeslot
time_id integer
begin_datetime (don't use seconds unless you must!)
end_time (don't use seconds unless you must!)
now, here's where it gets fun. You add the middle table with a composite primary key that is made up of car.car_id + parking_space.space_id + time_id. There are other things you could add to optimize here, but you get the idea, I hope.
table reservation
car_id PK
parking_space_id PK
time_id PK (it's an integer - just try to keep it as highly granular as possible - 30 minute increments or something - if you allow this to include seconds / milliseconds /etc the advantages are cancelled out because you can't re-use the same value from the time table)
(this would also be the place to store variable rates, discounts, etc distinct to this particular account, reservation, etc).
now, you can reduce the amount of data because you aren't replicating the timestamp in the join table (reservation). By using an integer, you can re-use that timeslot for multiple parking spaces, but you could also apply a constraint preventing two cars from renting that given spot for the same "timeslot" for a given day / timeframe. This would also make it easier to store some history about the customers - who knows, you might want to see reports on customers who rent more often and offer them discounts or something.
By using the ternary relationship model, you are making each spot unique to a given timeslot (perhaps with some added validation rules), so the system can only store one car in one parking spot for one given time period.
By using integers as keys instead of timestamps, you are assured that the database won't need to do any heavy lifting to index the keys and sort / query against. This is a common practice in data warehousing / OLAP reporting when you have massive datasets and you need efficiency. I think it applies here as well.
创建第三个表。
对于约束，我想您的情况的问题是您需要根据交集表本身检查复杂的规则。如果你在触发器中尝试这个，它会报告一个突变。
避免这种情况的一种方法是复制表，并针对该复制查询约束。
create a third table.
for the constraint, i guess the problem with your situation is that you need to check a complex rule against the intersection table itself. if you try this in a trigger, it will report a mutation.
one way to avoid this would be to replicate the table, and query against this replication for the constraint.