数据库关系多对多3种方式混淆

Question

我有一个简单的数据库，我想创建一个预订和日程安排系统。它是开放的，为了达到我的目标而改变，所以如果需要的话，不用担心改变设计。

以下是ERD：

​

​

问题如下:我有一些成员可以在特定的时间(预订期)为特定的地方(可预订区域)进行预订。当前的关系集允许许多成员在许多地方多次预订，这些关系可能会重叠。例如，它允许重复预订或人们同时预订两个地方(即同时在两个地方)。

我的问题是，我如何修改这个数据库的设计，以严格允许以下内容:一个成员只能预订一个地方，但可以多次预订(即9-9:30，9:30-10，10-10:30等)。有效地允许会员预订一个地点和时间。一旦预订，任何其他会员都不能预订相同的地点和时间。他们可以预订相同的地点但在不同的时间，或者相同的时间但不同的地点，但不能两者都预订。

我需要做些什么才能实现这一点。我更喜欢在关系中这样做，因为这让它变得显而易见，然而，作为最后的手段，如果没有办法使用关系来做到这一点，我将转向触发器，但我不想使用这些触发器，因为它们不像erd那样明显。

提前感谢您的帮助。

编辑1:触发器和检查约束确实可以作为答案，我没有以任何方式说这不是规范或它是不正确的。如果这是我唯一能做到的方法，那么我就只能这样做了。然而，我之所以这样问，是因为我更愿意让其他程序员明白这一点，所以这就是为什么我在寻找一种通过关系来做到这一点的方法。然而，我确实承认这可能是不可能的。我只是不确定，我有个问题。

编辑2:我知道我说过我希望在不使用约束或触发器的情况下做到这一点，然而，如果没有极端的措施，似乎不可能做到这一点，这将使事情变得过于复杂。然而，我确实喜欢这个过于复杂的答案，因为它显示了一些关于关系的非常好的思考。谢谢大家。

Answer 1

原始响应

如果我理解正确的话，下面的场景展示了哪些是允许的，哪些是不允许的。

Member            Book Time              Book Place
-----------------------------------------------------
John              0900-1000              Room 1
John              1000-1100              Room 1
John              1100-1200              Room 1
John              0900-1000              Room 2        <--- not allowed, member double booked
Jane              0900-1000              Room 1        <--- not allowed, room double booked

因此，您想要强制在两个不同的组上实现唯一性。一个组是成员和图书时间，另一个是图书时间和图书地点。

您可以使用这些列在Bookings上创建两个唯一的键，以强制任何组都不能复制。也就是说，具有重复预订时间的成员不能重复，因为它将违反唯一约束，并且具有重复预订地点的预订时间由于相同的原因不能重复。

编辑:我看到你在这个场景中完全反对约束，所以我的回答可能没有意义。

EDIT2:我的解决方案假设您建立了一个代理键作为Bookings表的主键。很抱歉没有提到这一点。

替代选项

这是我想到的一个古怪的想法。您可以维护6张表。

Member
BookTime
BookPlace
Member_BookTime
BookPlace_BookTime
Member_BookTime_BookTime_BookPlace

好了，在你把我写下来之前，先听我说完！您的图表中有4/6的表格。我建议拆分Members、BookTimes和BookPlaces之间的关系。

下面有一个代理主键和一个复合唯一键，以保证成员在一段时间内不会重复预订。

Member_BookTime
---------------
ID (PK)
Member_ID (CUK)
BookTime_ID (CUK)

下面有一个代理主键和一个复合唯一键，保证一次不会重复预订房间。

BookPlace_BookTime_ID
------------------
ID (PK)
BookPlace_ID (CUK)
BookTime_ID (CUK)

下面有一个复合主键，以及成员的预订时间和房间的预订时间之间的关系，最终将成员绑定到特定时间的房间。

Member_BookTime_BookTime_BookPlace
----------------------------------
Member_BookTime_ID (CPK)
BookPlace_BookTime_ID (CPK)

这一切有什么意义吗？在上面的模式中，成员的不能重复预订，房间的不能重复预订。我意识到这可能是“过度规范化”，但它使关系变得更加清晰。

建议的约束并不是真正“隐藏”的，因为它们显示在用于定义表的SQL中。我理解您希望从ERD中看到有关数据约束的所有内容，但有时约束太复杂。

Answer 2

如果您只想支持30或15分钟的时间间隔，我们可以这样做。

periods表将如下所示：

   ID     StartPeriod End Period
   1      0:00        0:15
   2      0:15        0:30 
   3      0:30        0:45 
   4      0:45        1:00 
   5      1:00        1:15

*以此类推

因此，如果您希望会员1在2012年7/30至12:30从午夜到12:30预订区域1，并且预订描述为“睡眠”，则记录将如下所示

    BookingInfo
    Id    Description
    1     "Sleep"

    Bookings
    Id     MemberId PeriodID BookingInfoId BookableAreaId Day
    1      1        1        1             1              7/30/2012
    2      1        2        1             1              7/30/2012

然后，你可以在预订上建立两个唯一的索引，一个是MemberId，Day，PeriodId (这将防止它们被安排在同一时间的同一天)

一个是BookableAreaId，Day，PeriodId (这将防止某人在同一天以相同的15分钟间隔重复预订相同的区域)

您还可以删除Id字段作为Bookings表上的主键，并用这些字段上的主键替换其中一个唯一索引。

抱歉，格式晦涩，我是新来这个网站的。附加ERD作为链接，因为我不能发布图片(直到我得到足够的代表)

ERD

Answer 3

我会像这样改变你的数据模型：

BookingPeriods将包含时隙列表。例如。你可以按上午和下午预订，这样StartTime就是上午9点，下午1点。或者以一小时为单位，这意味着记录9am-10am，10am-11am等。

Create table BookingPeriods (
    id int primary key
  , StartTime Time
  , EndTime Time
  , Description varchar(max)
)

BookingAvailability将是时隙和日期之间的简单映射。因此，您可以按日期列出哪些时隙是可能的

Create table BookingAvailability(
    id int primary key
  , Day Date
  , PeriodId int references BookingPeriods(id)
    constraint uq_bookingAvailability unique (Day, PeriodId)
)

BookableAreas只是一个物理位置的列表。

Create table BookableAreas(
    id int primary key
  , Name varchar(100)
  , ActivityTypeId int
  , Description varchar(max)
)

成员保持不变

Create table Members(
    id int primary key
  , FirstName varchar(100)
  , ...
 )

然后，bookings就变成了所有东西汇聚在一起的桌子。每个可用性记录和区域都有一个唯一的记录。成员is只是记录的一些辅助信息。

Create table Bookings(
    id int primary key
  , AvailabilityId int references BookingAvailability(id)
  , AreaId int references BookableAreas(id)
  , MemberId int references Members(id)
    constraint uq_Bookings unique (AvailabilityId, AreaId)
)

此数据模型的用法如下：

确保永远不要在这里的时隙中注册“重叠”。例如，如果你想要更细粒度的细节，你需要每小时创建一条记录。

INSERT INTO BookingPeriods (id, StartTime, EndTime, Description)
VALUES (1, '9:00 AM', '12:00 AM', 'Morning'), 
       (2, '1:00 PM', '4:00 PM', 'Afternoon')

这是一个您可以预订的可能的时隙列表。(例如，不包括周末的日期)

INSERT INTO BookingAvailability(id, [Day], PeriodId)
VALUES (1, '20120801', 1),
       (2, '20120801', 2),
       (3, '20120801', 1),
       (4, '20120801', 2),
       (5, '20120801', 1),
       (6, '20120801', 2)

INSERT INTO BookableAreas (id, Name, ActivityTypeId, Description)
VALUES (1, 'Ground Floor', 1, 'The ground floor room'),
       (2, 'East Wing Room', 1, 'The east wing room on the first floor'),
       (3, 'West Wing Room', 1, 'The west wing room on the first floor')

INSERT INTO Members(id, FirstName)
VALUES (1, 'Barak'),
       (2, 'Tony'),
       (3, 'George')

在这里，我们尝试创建实际的预订。

INSERT INTO Bookings(id, AvailabilityId, AreaId, MemberId)
VALUES (1, 3, 1, 1) -- Barak books the ground floor on 20120801 in the morning

INSERT INTO Bookings(id, AvailabilityId, AreaId, MemberId)
VALUES (2, 3, 1, 2)  -- Tony books the ground floor on 20120801 in the morning --> error

INSERT INTO Bookings(id, AvailabilityId, AreaId, MemberId)
VALUES (2, 4, 1, 2)  -- Tony books the ground floor on 20120801 in the afternoon --> ok