Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your solution is a basic reservation system. In a reservation system the resources are defined with an availability period. If the business rule was the resource was used in single hour increments I would design the resource table with 24 periods to represent each hour of the day. If the business rule stated the resource was available during 8AM to 10AM I would place a "True value" in the period columns representing that time frame. Reservations are tracked in a seperate table with a foreign key back to the resource. The reservation table has matching period columns from the resource table. When a reservation is made during a period the resource is available a "True value" is placed in the column. Resources available for reservation are found by </p> <ol> <li>Aggregating the reservation values for each period for the day</li> <li>Build a list of available resources</li> <li>Display the resources which do not have a reservation.</li> </ol> <p>In the calculation for step three we can rule out any resource who's period availability is "False" then we look at the aggregate value from the reservations table and state if there is a reservation("True") then the availability is "False".</p> <p>The code below is written in SQL Server 2008 and demonstrates the principals above. </p> <pre><code> /****** 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 </code></pre> <p>The code below will return three result sets. The first set is the availability calculation, second is the list of the classroom table and finally the reservation table.</p> <pre><code>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' </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POFinding Empty Hours For A Specified Classroom ( Database Design Issue )
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRC_Cleland
    1. USRC_Cleland
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload