title: GAE Data Models for Events and shared Resources
body: <p>I'm having trouble coming up with good data models and queries that scale well for modeling data similar to Google Calendar's Events and Resources.</p>
  
  <p>Resources are shared among users and a Resource can only be in one Event at a time. A Resource will have multiple events, but those Events cannot overlap or be happening at the same time. An Event can also have multiple resources.</p>
  
  <pre><code>class event(db.Model):
    #user that created/owns the event
    user = db.ReferenceProperty(user, collection_name='events',required=True)
  
    #an event can have multiple resources
    resources = db.StringListProperty() # resource key(s)
  
    #when the event opens and closes (start/stop)
    opendt = db.DateTimeProperty()
    closedt = db.DateTimeProperty()
  
  class resource(db.Model):
    name = db.StringProperty(required=True)
  </code></pre>
  
  <p>With these data models, I have not been able to come up with a quick, scalable way to get the list of available resources when creating an event.</p>
  
  <ol>
  <li>Get all Resources (simple query)</li>
  <li>Get all events that overlap the new event (multiple queries)
  <ol>
  <li>find events that start before and end after the new event</li>
  <li>find events that start before and end during the new event</li>
  <li>find events that start and end during the new event</li>
  <li>find events that start during and end after the new event</li>
  </ol></li>
  <li>Filter out the resources that are included in the overlapping events</li>
  </ol>
  
  <p>I feel like this process would get slower and slower as more events and resources are added.  I also don't know how this would work to be able to paginate the end result of resources.</p>
  
  <p>When a user is creating a new event, after they enter the start/stop datetimes of their new event, I would like them to quickly be able to see the available resources for that time frame.</p>
  
  <p>Is there a better, more efficient way?</p>