I am basically looking for an unbiased outside opinion on how to structure some data. If you have some time to give this some consideration I would appreciate it, I also though you might appreciate the mental challenge. So here goes

Overview: I need to help users assign resources to events so that an event may be marked as complete. Attendees may register for completed events.

Rules:

• Resource has a type (valid types are person, facility, office equipment, etc.)
• Resource has a schedule
• Resource has a site
• Site has resources
• Site has location attributes (i.e. city, state, zip, time zone, etc.)
• Resources may be re-allocated to different sites (i.e. Joe Bloggs might be a resource and he might move between sites, changes between sites would automatically impact a resources schedule)
• Events must have one resource of type person and one resource of type facility in order for the event to be considered complete
• Attendees may register for completed events

Questions: How would you design the schedule table to optimize finding an available resource? What would the tsql look like to find all available resources for a given site, on a given day, between a start time and end time, with a given resource type?

Devildog74 wrote:

I am basically looking for an unbiased outside opinion on how to structure some data. If you have some time to give this some consideration I would appreciate it, I also though you might appreciate the mental challenge. So here goes

Overview: I need to help users assign resources to events so that an event may be marked as complete. Attendees may register for completed events.

Rules:

• Resource has a type (valid types are person, facility, office equipment, etc.)
• Resource has a schedule
• Resource has a site
• Site has resources
• Site has location attributes (i.e. city, state, zip, time zone, etc.)
• Resources may be re-allocated to different sites (i.e. Joe Bloggs might be a resource and he might move between sites, changes between sites would automatically impact a resources schedule)
• Events must have one resource of type person and one resource of type facility in order for the event to be considered complete
• Attendees may register for completed events

Questions: How would you design the schedule table to optimize finding an available resource? What would the tsql look like to find all available resources for a given site, on a given day, between a start time and end time, with a given resource type?

Hmmm...you know it's bad when it's 2:45 a.m. and you're actually considering answering a question like this. <sigh> I wish I could blame it on my baby tonight, but nope; it's just me.

Anyway:

Question #1

Schedule Table

ScheduleID int identity
ResourceID int FK to Resource
EventID int FK to Event

---

Event Table

EventID int identity
SiteID int
EventStartDT as datetime
EventEndDT as datetime
ResourceStartDT as datetime --The date and time the resource is required
ResourceEndDT as datetime -- The date and time the resource is released

Question #2

--Find available resources for a given time frame, site, and resource type
SELECT * 
FROM Resource a
WHERE 
a.SiteID = @siteID AND
a.ResourceTypeID = @resourceTypeID AND
NOT ResourceID IN
(SELECT a.ResourceID
FROM Schedule a
JOIN Event b ON a.EventID = b.EventID
WHERE (
(@beginDT <= ResourceStartDT AND @endDT >= ResourceStartDT)
OR 
(@beginDT >= ResourceStartDT and @beginDT <= ResourceEndDT))) as BookedResources

Notes:

• This structure makes the assumption that the only things booked on schedules are events.
• The requirement to automatically update a resource's schedule if a resource changes sites must be handled in code
• The requirement to qualify an event as having the necessary resources scheduled must be handled in code This what you're looking for? It's 3:00 am, and I really have no idea whether I just gave you my imaginary aunt's famous chocolate chip cookie recipe or the arming procedure for a Trident II D-5 Ballistic Missile. Go figure. If it's written in English I'm way ahead of the game at this point.

Jeff...

Jeff, thanks a lot for the response. I am still trying to wrap my brain around this. I understand what you have described, but I havent had a chance to put any test data to it yet.

Thanks for the response, I will let you know how it works out.

Devildog74 wrote:

Thanks for the response, I will let you know how it works out.

Hey, Devildog. Just curious how this ended up working out for you.

Jeff...