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?
我们有一个汽车表和一个空格表(可能还有一个链接表)。cars表中的每一行表示一个汽车的唯一实例(带有许可证、所有者、模型等)，Spaces表中的每一行表示一个惟一的停车位(包含车库的楼层、行和编号)。连接数据库中的这些表并执行上面描述的约束的最佳方式是什么?
(I am using C#, NHibernate and Oracle.)
(我正在使用c#、NHibernate和Oracle。)
3 个解决方案
#1
2
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.
通过使汽车和空间引用都是唯一的，你将每一面限制在一个最大的链接上——即一辆汽车最多可以停在一个空间里，而一个空间最多只能停一辆车。
#2
1
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.
解决方案中还提到，只有在不需要历史记录的情况下，它才能解决您的问题。相信我，当我告诉您实际应用程序几乎总是需要表示历史数据时。有很多方法可以做到这一点，但是一个简单的方法可能是使用一个附加的表创建所谓的三元关系。理论上，您可以创建一个“time”表，该表还将它的主键(比如一个不同的时间戳)与其他两个源表的继承键联系起来。这将使您能够避免两个汽车在同一时间位于同一停车场的错误。使用时间表可以允许您使用一个简单的整数id重用多个停车位的相同时间数据。
So, your data tables might look like so
你的数据表可能是这样的
table car car_id (integers/numbers are fastest to index) ...
table car car_id(整数/数字是索引最快的)……
table parking-space space_id location
表停车位space_id位置
table timeslot time_id integer begin_datetime (don't use seconds unless you must!) end_time (don't use seconds unless you must!)
表timeslot time_id integer begin_datetime(除非必须使用秒，否则不要使用秒)end_time(除非必须使用秒，否则不要使用秒)。
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.
现在，这就是有趣的地方。将中间的表添加到由car组成的复合主键中。用时+ parking_space。space_id + time_id。你还可以添加一些其他的东西来优化，但是我希望你能理解我的意思。
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).
表预订用时PK parking_space_id PK time_id PK(它是一个整数,就尽量保持尽可能高颗粒- 30分钟,或者——如果你允许这种包括秒/毫秒/等优点是取消了,因为你不能重用相同的值从表)(这也会存储可变利率,折扣,等不同的这个账户,预订,等等)。
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.
现在，您可以减少数据量，因为您没有复制join表(reservation)中的时间戳。通过使用一个整数，您可以对多个停车位重复使用这个时间表，但是您还可以应用一个约束，阻止两辆车在给定的日期/时间范围内以相同的“时间表”租用给定的停车位。这也可以让你更容易地储存一些关于客户的历史信息——谁知道呢，你可能想看看那些更经常租房的客户的报告，并给他们打折或其他什么。
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.
通过使用整数作为键而不是时间戳，可以确保数据库不需要做任何繁重的工作来索引键和对键进行排序/查询。在数据仓库/ OLAP报告中，当您有大量数据集并且需要效率时，这是一个常见的实践。我认为它也适用于这里。
#3
0
create a third table.
创建第三个表。
parking
--------
car_id
space_id
start_dt
end_dt
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.
避免这种情况的一种方法是复制该表，并查询该约束的复制。
#1
2
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.
通过使汽车和空间引用都是唯一的，你将每一面限制在一个最大的链接上——即一辆汽车最多可以停在一个空间里，而一个空间最多只能停一辆车。
#2
1
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.
解决方案中还提到，只有在不需要历史记录的情况下，它才能解决您的问题。相信我，当我告诉您实际应用程序几乎总是需要表示历史数据时。有很多方法可以做到这一点，但是一个简单的方法可能是使用一个附加的表创建所谓的三元关系。理论上，您可以创建一个“time”表，该表还将它的主键(比如一个不同的时间戳)与其他两个源表的继承键联系起来。这将使您能够避免两个汽车在同一时间位于同一停车场的错误。使用时间表可以允许您使用一个简单的整数id重用多个停车位的相同时间数据。
So, your data tables might look like so
你的数据表可能是这样的
table car car_id (integers/numbers are fastest to index) ...
table car car_id(整数/数字是索引最快的)……
table parking-space space_id location
表停车位space_id位置
table timeslot time_id integer begin_datetime (don't use seconds unless you must!) end_time (don't use seconds unless you must!)
表timeslot time_id integer begin_datetime(除非必须使用秒，否则不要使用秒)end_time(除非必须使用秒，否则不要使用秒)。
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.
现在，这就是有趣的地方。将中间的表添加到由car组成的复合主键中。用时+ parking_space。space_id + time_id。你还可以添加一些其他的东西来优化，但是我希望你能理解我的意思。
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).
表预订用时PK parking_space_id PK time_id PK(它是一个整数,就尽量保持尽可能高颗粒- 30分钟,或者——如果你允许这种包括秒/毫秒/等优点是取消了,因为你不能重用相同的值从表)(这也会存储可变利率,折扣,等不同的这个账户,预订,等等)。
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.
现在，您可以减少数据量，因为您没有复制join表(reservation)中的时间戳。通过使用一个整数，您可以对多个停车位重复使用这个时间表，但是您还可以应用一个约束，阻止两辆车在给定的日期/时间范围内以相同的“时间表”租用给定的停车位。这也可以让你更容易地储存一些关于客户的历史信息——谁知道呢，你可能想看看那些更经常租房的客户的报告，并给他们打折或其他什么。
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.
通过使用整数作为键而不是时间戳，可以确保数据库不需要做任何繁重的工作来索引键和对键进行排序/查询。在数据仓库/ OLAP报告中，当您有大量数据集并且需要效率时，这是一个常见的实践。我认为它也适用于这里。
#3
0
create a third table.
创建第三个表。
parking
--------
car_id
space_id
start_dt
end_dt
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.
避免这种情况的一种方法是复制该表，并查询该约束的复制。