Challenges with the `schedule` table for organizations in the USA

Technical
1

I’ve been working with the National 211 Data Platform (NDP) in conjunction with a data managers from several 211s towards creating, among other things, a 211 Profile for HSDS. We’ve hit a snag trying to use the schedule table with existing real-world data.

Many software systems where resource data originates provide a single plain text field for schedule data. It’s gross, but that’s the reality for now. Here’s a sample of real schedules provided by the NDP for reference:

<u>Women:</u>Monday - Friday, 9:00am - 11:00am<u>Men:</u>Monday - Friday, 2:00pm -
4:00pm<u>Meals:</u>
Monday - Friday, 11:30am - 1:00pm"
Monday - Friday, 8 a.m. - 5 p.m.
Mon-Thu 7am-5pm, every other Fri 7am-5pm
Mon-Fri - 6:30am to 6:00pm
Mon V - V; Tue V - V; Wed V - V; Thu V - V; Fri V - V;
Mon 8:30am - 5pm; Tue 8:30am - 5pm; Wed 8:30am - 5pm; Thu 8:30am - 5pm; Fri 8:30am - 5pm;
Mon 9am - 5pm; Tue 9am - 5pm; Wed 9am - 5pm; Thu 9am - 5pm; Fri 9am - 5pm;
Mon - Fri 8:00 am - 5:00 pm
Mon - Fri 8:00 am - 4:30 pm

211 data managers are drafting how they would like to be able to represent data, both in an interface and under the hood. I’ve been pushing for using the HSDS schedule schema under the hood, but there are two problems:

  1. The source data will be retained in the description field for a schedule record, but where a single schedule requires more than once record (for example Mon-Thu 7am-5pm, every other Fri 7am-5pm), that description will have to be duplicated across each record, which breaks the rules of normalization.
  2. Further, where an entity has more than one schedule, each with different times on different days, it can be difficult or impossible to determine which schedule rows should be grouped together. We might consider matching by valid_from and valid_to values, but not all records will have that info, nor can we guarantee they will line up cleanly even when they are present.

The solution proposed by our work group is to move the current schedule fields into a table called hours, and then simplify schedule to (more or less) description, valid_from, and valid_to. There would be a 1:many relationship from schedule to hours, thereby allowing hours to a) be grouped and b) share a global description.

It’s an intuitive abstraction that looks similar to the pattern used for location and address.

@davidraznick suggested we could use attributes to group schedule records and add the description, but also acknowledged it’s a little hacky and wouldn’t negate the description duplication problem. Of more importance to me is that it’s not obvious; developers would have to know to look for attributes, and know what to do with them, whereas the table structure suggested above is self documenting.

I’m very interested in getting feedback from others about how you have solved these challenges with schedule data, and whether you think there is merit to abstracting hours a level below schedule.

@bloom @devin @PollyM @MikeThacker

2

We’ve experienced a simile need for different reasons.

Our users have used the schedule table effectively but they want a way to put a message at the top of the schedule.

They want to document their recurring hours (ex 9-5 M,W and 10-6 Tu,Th,F) but then want to be able to add a message (ex “Closed on Christmas and New Years”). They could use the schedule table to document this but it’s a lot more work than simply documenting recurring hours.

We’ve used an attribute style approach to achieve

As for a technical solution, I think we should consider a third solution in which we use the existing schedule table to hold description and validity period data only and maybe an additional field to document that this field is not iCal valid data but for display purposes only. I’m not convinced that’s the best approach but I think it’s worth considering.

The other two technical solutions proposed thus far seem workable to me.

3

Thanks to @skyleryoung and @devin for getting this conversation started.

This is one of our ongoing challenges, so for reference, I just want to point back to the thread where we’ve had most discussion in the past (and which led to our adoption of the RRULE framework in v2), along with other open issues related to schedule in the Github repo.

4

Thanks for the links @bloom . I missed in the documentation that schedule is using RRULE. I feel stupid for missing that, but perhaps calling that out a little more boldly and linking to a guide where additional parsers are recommended would help reduce the barrier to entry that RRULE entails.

I’ll note that the recommendations made by the 211 workgroup don’t replace RRULE, they just add a layer above it for more intuitive grouping and labeling. After reading your GitHub thread, I would recommend adding another field to the higher-level schedule table: type with enumerated values of event and hours. Again, that follows the pattern of the location and address relationship.

@devin Have you run into the use case of needing to have more than once schedule active for a service record?