查找指定教室的空闲时间（数据库设计问题）

Question

我正在尝试设计一个用于创建学校教学大纲的数据库。简而言之，所有教室都可以从上午 8 点到晚上 18 点活跃 10 个小时。如何创建数据库来回答以下问题？

给我周一 A101 教室的空闲时间吗？

我在下面做了这个，但它不足以有效地回答这个问题|： http://i51.tinypic.com/110drgk.png

Answer 1

我只涉足数据库，但这个问题对我来说似乎很有趣。

据我所知，听起来您对如何编写查询比如何存储数据更感兴趣。根据您的解释，您想要的是一个输出如下所示的查询：

classroom  openStart  openEnd  day session
A101       9:00       10:00    mo  spring
A101       14:00      16:00    mo  spring

假设架构测试，表类。类保存 startTime 和 endTime 值。此处忽略将执行过滤的其他值，只关注您感兴趣的逻辑。此查询在 MySQL 上运行。

-- 8 and 18 are added as the boundaries for the day.
Select * from (Select * from ((select endTime as a from test.class) union (select 8)) as t1 
   join ((select startTime as b from test.class) union (select 18 as startTime)) as t2 
   where a <= b group by a order by a, b) as t3 where a != b;

三个子查询。

第一个创建所有结束时间（附加当天的开始时间）与所有开始时间（附加当天的结束时间）的叉积。这给你的是一系列代表每个可以想象的开放时间块的行。 （排列为块开始/块结束）

第二个子查询通过过滤掉块开始发生在块结束之后的值来解析第一个子查询的结果，然后按块开始、块结束对值进行排序。这使得当值按块开始分组时，您会得到一个很好的对列表，可以轻松找到类连续的位置。

然后，第三个查询会过滤掉时间相同的值，只提供相关的时间块。假设第三个查询可以使用比较来排除课程间隔不到一小时的情况。

我相信其他人可能会为您提供更简单的解决方案。

Answer 2

你的数据库图表中甚至没有教室。

如果您主要关心的问题之一是阅读提前时间，我会建议遵循以下内容：

一个 timeSlot 表，其中包含一天中时间块的列（因为这可以分解为相对较小的有限数字，例如每 30 分钟 1 次）。
教室表将有 5 或 7 个 ID，引用 timeSlot 表中的 5 或 7 个条目

希望这会有所帮助，

Ryan Taylor

Answer 3

支持通用间隔类型的 DBMS 使这变得非常简单：

使用属性 和 定义一个相关变量。

然后只需查询“X NOT MATCHING Y”，其中：

X 是一个关系文字，提及适当的教室标识符和时间间隔 08.00-18.00，

Y 是持有的相关变量，

（NOT MATCHING 是您需要的任何关键字调用关系代数的 SEMIMINUS 运算符）。

例如，在 SIRA_PRISE 中，您可以有一个名为 SCH 的相关变量，其中包含“scheduled_during”元组 {classRoomID:"A101" ScheduledDuring:[1999-01-02-09.30.00-1999-01-02 -12.00.00]}。

关系文字可以是“RELATION{TUPLE{classRoomID:"A101"scheduledDuring:[1999-01-02-08.00.00-1999-01-02-18.00.00]}}”。

然后查询 SEMIMINUS(RELATION{TUPLE{...}},SCH) 将产生元组

classRoomID:"A101" ScheduledDuring:[1999-01-02-08.00.00-1999-01-02-09.30.00] 和
classRoomID:"A101" ScheduledDuring:[1999-01-02-12.00.00-1999-01-02-18.00.00]

编辑

PS 不要被该属性名称 ScheduledDuring 显示房间未安排的时间段所迷惑。值的含义位于查询的“外部谓词”中，而不是属性名称中。

Answer 4

您的解决方案是一个基本的预订系统。在预订系统中，资源是用可用期限来定义的。如果业务规则是以单小时增量使用资源，我将设计具有 24 个周期的资源表来表示一天中的每个小时。如果业务规则规定资源在上午 8 点到上午 10 点期间可用，我会在表示该时间范围的期间列中放置一个“真实值”。预订在一个单独的表中进行跟踪，并使用返回资源的外键。预留表具有来自资源表的匹配周期列。如果在资源可用期间进行预订，则该列中会放置“真实值”。 来查找可预留的资源。

1. 可通过聚合当天每个时段的预留值
2. 构建可用资源列表
3. 显示没有预留的资源。

在第三步的计算中，我们可以排除任何期间可用性为“False”的资源，然后我们查看预订表中的聚合值，并声明如果存在预订（“True”），则可用性为“False”。

下面的代码是用 SQL Server 2008 编写的，演示了上述原理。

    /****** Object:  Table [dbo].[Classroom]    Script Date: 05/20/2011 08:25:53 ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Classroom]') AND type in (N'U'))
    DROP TABLE [dbo].[Classroom]
    GO
    /****** Object:  Table [dbo].[Classroom]    Script Date: 05/20/2011 08:25:53 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Classroom]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[Classroom](
        [ClassRoomID] [int] IDENTITY(1,1) NOT NULL,
        [CrIsAvailablePeriod01] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod01 DEFAULT 0,
        [CrIsAvailablePeriod02] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod02 DEFAULT 0,
        [CrIsAvailablePeriod03] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod03 DEFAULT 0,
        [CrIsAvailablePeriod04] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod04 DEFAULT 0,
        [CrIsAvailablePeriod05] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod05 DEFAULT 0,
        [CrIsAvailablePeriod06] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod06 DEFAULT 0,
        [CrIsAvailablePeriod07] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod07 DEFAULT 0,
        [CrIsAvailablePeriod08] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod08 DEFAULT 0,
        [CrIsAvailablePeriod09] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod09 DEFAULT 0,
        [CrIsAvailablePeriod10] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod10 DEFAULT 0,
        [CrIsAvailablePeriod11] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod11 DEFAULT 0,
        [CrIsAvailablePeriod12] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod12 DEFAULT 0,
        [CrIsAvailablePeriod13] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod13 DEFAULT 0,
        [CrIsAvailablePeriod14] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod14 DEFAULT 0,
        [CrIsAvailablePeriod15] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod15 DEFAULT 0,
        [CrIsAvailablePeriod16] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod16 DEFAULT 0,
        [CrIsAvailablePeriod17] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod17 DEFAULT 0,
        [CrIsAvailablePeriod18] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod18 DEFAULT 0,
        [CrIsAvailablePeriod19] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod19 DEFAULT 0,
        [CrIsAvailablePeriod20] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod20 DEFAULT 0,
        [CrIsAvailablePeriod21] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod21 DEFAULT 0,
        [CrIsAvailablePeriod22] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod22 DEFAULT 0,
        [CrIsAvailablePeriod23] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod23 DEFAULT 0,
        [CrIsAvailablePeriod24] [bit]NOT NULL CONSTRAINT DF_CrIsAvailablePeriod24 DEFAULT 0,
        [CrShortName] [char](10) NOT NULL,
        [CrLongName] [varchar](128) NULL,

     CONSTRAINT [PK_Classroom] PRIMARY KEY CLUSTERED 
    (
        [ClassRoomID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO

    SET ANSI_PADDING ON
    GO


    INSERT INTO [deleteme].[dbo].[Classroom]
               ([CrIsAvailablePeriod01]
               ,[CrIsAvailablePeriod02]
              ,[CrShortName])
         VALUES
               (0,0,'A1')
               ,(0,1,'B1')
               ,(1,0,'C1')
               ,(1,1,'D1')
    GO
USE [deleteme]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reservation_Classroom]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reservation]'))
ALTER TABLE [dbo].[Reservation] DROP CONSTRAINT [FK_Reservation_Classroom]
GO

USE [deleteme]
GO

/****** Object:  Table [dbo].[Reservation]    Script Date: 05/20/2011 08:29:59 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reservation]') AND type in (N'U'))
DROP TABLE [dbo].[Reservation]
GO

USE [deleteme]
GO

/****** Object:  Table [dbo].[Reservation]    Script Date: 05/20/2011 08:29:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reservation]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Reservation](
    [ReservationId] [int] Identity (1,1)NOT NULL,
    [ResClassroomID] [int] NOT NULL,
    [ResDate] [date] NOT NULL,
    [ResIsReservedPeriod01] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod01 DEFAULT 0, 
    [ResIsReservedPeriod02] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod02 DEFAULT 0, 
    [ResIsReservedPeriod03] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod03 DEFAULT 0, 
    [ResIsReservedPeriod04] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod04 DEFAULT 0, 
    [ResIsReservedPeriod05] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod05 DEFAULT 0, 
    [ResIsReservedPeriod06] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod06 DEFAULT 0, 
    [ResIsReservedPeriod07] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod07 DEFAULT 0, 
    [ResIsReservedPeriod08] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod08 DEFAULT 0, 
    [ResIsReservedPeriod09] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod09 DEFAULT 0, 
    [ResIsReservedPeriod10] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod10 DEFAULT 0, 
    [ResIsReservedPeriod11] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod11 DEFAULT 0, 
    [ResIsReservedPeriod12] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod12 DEFAULT 0, 
    [ResIsReservedPeriod13] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod13 DEFAULT 0, 
    [ResIsReservedPeriod14] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod14 DEFAULT 0, 
    [ResIsReservedPeriod15] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod15 DEFAULT 0, 
    [ResIsReservedPeriod16] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod16 DEFAULT 0, 
    [ResIsReservedPeriod17] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod17 DEFAULT 0, 
    [ResIsReservedPeriod18] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod18 DEFAULT 0, 
    [ResIsReservedPeriod19] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod19 DEFAULT 0, 
    [ResIsReservedPeriod20] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod20 DEFAULT 0, 
    [ResIsReservedPeriod21] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod21 DEFAULT 0, 
    [ResIsReservedPeriod22] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod22 DEFAULT 0, 
    [ResIsReservedPeriod23] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod23 DEFAULT 0, 
    [ResIsReservedPeriod24] [bit]NOT NULL CONSTRAINT DF_ResIsReservedPeriod24 DEFAULT 0, 

    CONSTRAINT [PK_Reservation] PRIMARY KEY CLUSTERED 
(
    [ReservationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reservation_Classroom]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reservation]'))
ALTER TABLE [dbo].[Reservation]  WITH CHECK ADD  CONSTRAINT [FK_Reservation_Classroom] FOREIGN KEY([ResClassroomID])
REFERENCES [dbo].[Classroom] ([ClassRoomID])
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reservation_Classroom]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reservation]'))
ALTER TABLE [dbo].[Reservation] CHECK CONSTRAINT [FK_Reservation_Classroom]
GO


INSERT INTO [deleteme].[dbo].[Reservation]
           ([ResClassroomID]
           ,[ResDate]
           ,[ResIsReservedPeriod01]
           ,[ResIsReservedPeriod02])
     VALUES
           (1,'06-02-2011',1,0)
           ,(1,'06-02-2011',0,1)
           ,(2,'06-03-2011',1,1)
           ,(4,'06-03-2011',0,1)


GO

下面的代码将返回三个结果集。第一组是可用性计算，第二组是教室表列表，最后是预订表。

declare @when date
set @when = '06-03-2011'


;With CTE_Res AS
(
SELECT     
Reservation.ResClassroomID AS 'ResClassroomID'
,ResDate as 'ResDate'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod01] as int)),0)as BIT) AS'IsReserved01'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod02] as int)),0)as BIT) AS'IsReserved02'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod03] as int)),0)as BIT) AS'IsReserved03'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod04] as int)),0)as BIT) AS'IsReserved04'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod05] as int)),0)as BIT) AS'IsReserved05'
, CAST(ISNULL(MAX(CAST([ResIsReservedPeriod06] as int)),0)as BIT) AS'IsReserved06'
FROM         
Reservation 
WHERE ResDate = @when --'2011-06-03'
GROUP BY Reservation.ResClassroomID, ResDate
)
--Select * from CTE_RES
SELECT     
Classroom.ClassRoomID
,Classroom.CrShortName

, CASE Classroom.CrIsAvailablePeriod01 
    WHEN 0 then 0
    Else  1 ^ ISNULL(CTE_Res.IsReserved01,0) 
    END As 'IsOpenPeriod01'
, CASE Classroom.CrIsAvailablePeriod02 
    WHEN 0 then 0
    Else  1 ^ ISNULL(CTE_Res.IsReserved02,0) 
    END As 'IsOpenPeriod02'

--, CTE_Res.ResDate
, @when as [Day]
FROM         
Classroom 
LEFT OUTER JOIN CTE_Res 
ON Classroom.ClassRoomID = CTE_Res.ResClassroomID
--WHERE ResDate = '2011-06-03'



SELECT  [ClassRoomID]
,[CrShortName]
      ,[CrIsAvailablePeriod01]
      ,[CrIsAvailablePeriod02]

  FROM [deleteme].[dbo].[Classroom]

SELECT TOP 200 [ResClassroomID]
,ResDate
      ,[ResIsReservedPeriod01]
      ,[ResIsReservedPeriod02]
      ,[ResDate]  
      FROM [deleteme].[dbo].[Reservation]  

      WHERE ResDate = @when --'2011-06-03'