Q:

Determining minimum users available during time frame

I am writing an attendance/PTO tracking app, and am absolutely stumped as to how I should determine the minimum amount of agents that are working between two given datetimes (start and end). This will be used to determine whether an agent can request off (if they are going to be alone at any point, they would be auto-denied). Given the below db schemas:
metadata = MetaData(engine) #< engine is an already established mysql conn

# Users table declaration
self.Users = Table(
    'Users', metadata,
    Column(u'ID', Integer(), primary_key=True, nullable=False,
           index=True),
    Column(u'Username', Unicode(16), nullable=False, index=True),
    Column(u'Firstname', Unicode(16), nullable=False),
    Column(u'Lastname', Unicode(50), nullable=False, index=True),
    Column(u'Nickname', Unicode(16), nullable=False),
    Column(u'Email', Unicode(16), nullable=False),
    Column(u'AvayaID', Integer(), nullable=False, index=True),
    Column(u'ManagerID', Integer(), nullable=False, index=True),
    Column(u'ShiftID', Integer(), ForeignKey('Shifts.ID'), nullable=False),
    Column(u'DaysID', Integer(), ForeignKey('Days.ID'), nullable=False),
)

# Shifts table declaration - Shift times
self.Shifts = Table(
    'Shifts', metadata,
    Column(u'ID', Integer(), primary_key=True, nullable=False, index=True),
    Column(u'Start', DateTime()),
    Column(u'End', DateTime()),
)

#   Days table declaration - Days of week worked
self.Days = Table(
    'Days', metadata,
    Column(u'ID', Integer(), primary_key=True, nullable=False),
    Column(u'Sat', Integer(1), nullable=False),
    Column(u'Sun', Integer(1), nullable=False),
    Column(u'Mon', Integer(1), nullable=False),
    Column(u'Tue', Integer(1), nullable=False),
    Column(u'Wed', Integer(1), nullable=False),
    Column(u'Thu', Integer(1), nullable=False),
    Column(u'Fri', Integer(1), nullable=False),
    )

Users table has two Foreign Keys, corresponding to a ShiftID in the Shifts table and a DaysID in the Days table. The Shifts table just holds possible shift start/end times and the Days table holds possible combinations of days worked. Joining across the tables yields me a structure similar to: 
{ 
  'Username' : str,    #< Agent username
  'ManagerID' : int,   #< Agent manager ID
  'Shift.Start' : time,#< Agent shift start time
  'Shift.End' : time,  #< Agent shift end time
  'Days.Sat' : bool,   #< If agent works this day normally
  'Days.Sun' : bool,   #< If agent works this day normally
  'Days.Mon' : bool,   #< If agent works this day normally
  'Days.Tue' : bool,   #< If agent works this day normally
  'Days.Wed' : bool,   #< If agent works this day normally
  'Days.Thu' : bool,   #< If agent works this day normally
  'Days.Fri' : bool,   #< If agent works this day normally
}

I need to determine the minimum amount of agents that will be available during a selected agent's shift. 
I'm running into a few problems here, one is the fact that I have to account for overnight shifts (start_time > end_time) and another is simply wrapping my mind around writing this query. I'm thinking that my database schemas are the problem here, but I can't think of anything better and Google isn't helping. 

A:

If I'm understand you correctly you want to correlate date, user, and shift. I would keep your User table and then add something like this:
self.Schedule = Table(
    'schedule', metadata,
    Column(u'id', Integer(), primary_key=True, nullable=False, index=True),
    Column(u'User_ID', Integer(), nullable=False, ForeignKey('User.ID')),
    Column(u'Date', DateTime(), nullable=False),
    Column(u'Shift', Integer(), nullable=False)
    UniqueConstraint('User_ID', 'Date', 'Shift', name='schedule_constraint')
)

This table will store a record of every shift every employee is working. It has a constraint that every combination of date, user, and shift must be unique. This ensures that the same person can not work the same shift more than once a day but they can still work more than one shift per day, or more than one day. Then you could query the number of users scheduled to work a shift like this:
session.Query(Schedule).\
        filter(Schedule.Date == '2014-02-18').\
        filter(Schedule.Shift == '1').\
        count()

To be brief I'm assuming the shift are numbered (e.g. 1,2,3). If they were variable you could easily modify this to include a start and end time and use >= and <= statements in the query. 
Note: I followed your conventions but be aware that in SQLAlchemy Ver. 0.9 the Integer column type is declared without parenthesis, see this question: Error in SQLAlchemy with Integer: "object() takes no parameters"

