SQL - Finding open spaces in a schedule

I am working with a SQLite database and I have three tables describing buildings,rooms and scheduled events.

The tables look like this:

• Buildings(ID,Name)
• Rooms(ID,BuildingID,Number)
• Events(ID,BuildingID,RoomID,Days,s_time,e_time)

So every event is associated with a building and a room. The column Days contains an integer which is a product of prime numbers corresponding to days of the week ( A value of 21 means the event occurs on Tuesday = 3 and Thursday = 7).

I am hoping to find a way to generate a report of rooms in a specific building that will be open in the next few hours, along with how long they will be open for.

Here is what I have so far:

SELECT Rooms.Number 
FROM Rooms
INNER JOIN Buildings on ( Rooms.BuildingID = Buildings.ID )
WHERE 
  Buildings.Name = "BuildingName"

EXCEPT

SELECT Events.RoomID
FROM Events
INNER JOIN Buildings on ( Events.BuildingID = Buildings.ID )
WHERE
  Buildings.Name = "BuildingName" AND
  Events.days & 11 = 0 AND
  time("now", "localtime" BETWEEN events.s_time AND events.e_time;

Here I find all rooms for a specific buil开发者_如何学Pythonding and then I remove rooms which currently have an scheduled event in progress.

I am looking forward to all helpful tips/comments.

If you're storing dates as the product of primes, the modulo (%) operator might be more useful:

SELECT * FROM Events
INNER JOIN Buildings on (Events.BuildingID = Buildings.ID)
WHERE
    (Events.Days % 2 = 0 AND Events.Days % 5 = 0)

Would select events happening on either a Monday or Wednesday.

I do have to point out though, that storing the product of primes is both computationally and storage expensive. Much easier to store the sum of powers of two (Mon = 1, Tues = 2, Wed = 4, Thurs = 8, Fri = 16, Sat = 32, Sun = 64).

The largest possible value for your current implementation is 510,510. The smallest data type to store such a number is int (32 bits per row) and retrieving the encoded data requires up to 7 modulo (%) operations.

The largest possible value for a 2^n summation method is 127 which can be stored in a tinyint (8 bits per row) and retrieving the encoded data would use bitwise and (&) which is somewhat cheaper (and therefore faster).

Probably not an issue for what you're working with, but it's a good habit to choose whatever method gives you the best space and performance efficiency lest you hit serious problems should your solution be implemented at larger scales.