Some Scheduling Questions

[AndrewS]

As a preface, I'm relatively new to Java, but have many years experience with Visual Studio and Web languages. So some of the intricacies of Java may be beyond me.

1) I'm creating a Scheduling component, and am looking to create a view that shows the schedules of multiple employees at once. I've bought the manual, but I'm seeing nothing promising there. I had thought of creating a page with 4 schedule items defaulting to and only allowing Day view, but with no visible way of having all the schedules sync the selected date (when you change it with the arrows,) it's looking like I'll have to create my own schedule from scratch. Hopefully I'm wrong on that.

I understand I could just dump all the appointments into a single Schedule, but each employee's appointments needs to be separate from the others via columns or such.

2) I need a way to find the first few available appointments in the future when given a set of employees to search from. This is a pain in the rear. A single worker I could probably manage well enough, but with my appointments allowing multiple employees to attend, it ends up being a 4-table nightmare of a query that I just get lost in.

Table Layout:

Schedule -> lnkScheduleEmployees -> Employees -> Location

So a single schedule item uses the Link table (just stores the PKs for the two other tables) to hold a list of attending Employees, and each Employee also belongs to a single Location. So when I'm looking for an available appointment, I do so either by a single Employee search (though they would rarely use this method,) or by Location (more common.) The latter is the nightmare for me.

I have to grab a list of Employees at that location (one of which has over 1000 listed Employees,) and then look for a free slot and spit out the top ten or so.

A coworker suggest setting up a View, which I've honestly never worked with.

3) When in Day view, if I have multiple appointments in the same time slot, they overlap something fierce. As it stands now, I have 3 appointments starting at 9am, lasting until 10am, 11am and noon. They all stretch down the proper amount, but the noon one stretches horizontally inside the 11am one almost completely. The other two seem to behave alright. Originally I thought it was an issue with the length of the title, so I brought the titles down to under 30 characters and it's still acting the same, pixel for pixel.

Using PrimeFaces 2.2.1 and MySQL and Hibernate. Though I'm not particularly fond of Hibernate due to my inability to get it working with complex queries.

[jp_2011]

This is quite a complicated scenario, but some of my experience in working with a similar problem may assist you here.

My first step was to build out the database to hold the appointments (with attributes like start time, end time, location, etc) and relationships of those appointments to end users. For example a table called CalendarEvents which holds every single calendar event for every person. With the use of a many-to-one relationship to the users table (1 user owns many calendar appointments) you can set your foreign keys to the owner of the appointment. Then you need a many to many relationship for the invitees. Ok hopefully you are still following along alright at this point and you now have a traditional representation of calendar appointments owned by users that allow for attendees.

Following standard JPA, build your entity and session beans that represent your CalendarEvent table. To have your entity work with the Schedule componenet, you need to have your entity implement Primefaces' "ScheduleEvent" and "Serializable". Once this is done, your calendar event can then be viewed by Schedule in standard JSF ways. With this approach you can adjust your @NamedQuery so that you can pass 1 or more user id's to fetch back all the calendar events into a single Collection which is displayable in the Schedule component.

I hope this gives you some ideas to run with. Good luck with your project!

[AndrewS]

Thanks for the quick response. What you proposed is indeed what I'm doing currently. I've already built the scheduling beans and all that, and it's all in place and working. Multiple attendees on an event works great, after much grief.

I'm just stuck on the "find first appointment" part, and being able to view multiple worker's schedules at once without it being a mess. As well as appointments in the same time slot visually overlapping. Though from the look of it, it's an issue of the underlying FullCalendar component.

If you go to http://arshaw.com/fullcalendar/ and look at today's Day view, you'll see what I mean. It's not a big deal in the linked example, but when you have 5-6 appointments in a time slot it's a visual mess.

[jp_2011]

Perhaps I do not understand your requirement for "First Appointments" properly, but all I would do is have a hashmap where the key is the user name/id and the value is an array list of max size x (say 10 for example) containing their next x appointments given a day as a start point. Then you can easily traverse the next x appointments per person given any day you choose.

As far as displaying columns of Schedules you might be able to make use of a dataTable with columns being your users and the data row being the schedule but I've never tried using those two components together. I think this would be a nice feature to add to the Schedule component as it would be very useful.

[AndrewS]

Sorry, Java's still newish to me so I may be using wrong terminology or misinterpreting yours.

I currently store the following in Schedule:
primarykey, ownerid (links to Employee,) startTime (date and time,) endTime (date and time,) description, title

lnkScheduleEmployee - used to create the Many to Many relationship
primarykey, scheduleid, employeeid

The issue arises that I cannot look at that Schedule table alone to see an Employee's availability. I must use the lnkScheduleEmployee table.

Example:
- Bob creates a new appointment with Jane and Fred as attendees. He also selects the "I will attend" button, as there are cases when the appointment owner/creator is not attending. IE: Secretary creates an appointment for another person.
- SQL creates a single Schedule item owned by Bob, then uses the Link table to attach Bob, Jane and Fred as attendees, without creating two extra schedule items. If the owner IS attending, it adds them into the Link table.

Thus, when I actually check a single person's Schedule, it pulls all Schedule items in which the person exists on the Link table, as well as the ones they own so they can edit them, even if they're not attending.

Maybe I didn't structure the Many-Many relationship the ideal way. I'm self-taught on most everything, and it's how I always structured my tables in the .Net world.

Anyway... it obviously makes the availability check a little complex, as I have to tie so many tables together.

But back to your suggestion... I think I'm maybe not understanding your usage of the "hashmap" idea. So the stuff below may not apply to your suggestion

I want to avoid creating data where possible, as there are thousands of users that will be hammering on this system daily. I cannot use giant queries that create huge tables, nor can I generate a virtual "calendar grid" using tables that gets each entry flagged as used/unused, as I have seen some people suggest on various Google results.

I'm thinking I need a View or Stored Procedure that can return a list of unbooked time for a list of users, probably one week per iteration of the View/Stored Proc.

This Schedule table is going to get massive over time, as the thousands of employees can have appointments booked months in advance. So creating queries that spawn a temporary table per user (remember, over 1000 users) containing a week or month's worth of 15 minute blocks of time isn't feasible.