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

Technical
7

@mrshll I completely agree that inserting a normalized table between schedule and service is a Major version upgrade. I believe I was informed in another post that I can just add that in a Profile, which made me very queasy from an engineering perspective, but I’ll address that in a different post.

Your suggested schema is very close to what I imagine (and what was drafted by Andrew Benson at the National 211 Data Platform) with two exceptions:

  1. schedule_details (naming things is hard) needs to be an array. One of the driving use cases is a service that has two seasonal schedules with different hours. service needs a one-to-many with schedule_details, and obviously schedule_details requires a one-to-many with schedule where the RRULEs are.

  2. The valid fields should be duplicated at the schedule_details level. This actually isn’t a deal breaker for me from a standards perspective, but per that use case above and all data I’ve seen so far valid time ranges are always applied to an entire group of RRULEs, so the convenience of using those at the higher level would be nice. However, I’m not advocating for removing those fields from the schedule level; the RRULE specification should remain completely intact for that table. It’s adds an additional level of granularity that’s potentially useful anyway.

What are your thoughts on these two modifications?

I’ll note on a related topic, that our community could really use some documented guidelines for how the schedule table is expected to be used for “business hours” style schedule data. RRULEs are great for event style data, but introduce some ambiguity for business hours data.

Here’s an example of ambiguity: when I have multiple days in my schedule with identical hours, do I collapse those to one record (which is allowed by the specification), or do I always create one schedule record for every day of the week? How I parse these two methods (and there are others!) will be quite different, which means that end-users cannot create a consistent method for parsing schedule data in HSDA at this time, even though it’s a highly specific and well defined data schema.

I would love to chat about this. I’ll hit you up on Slack to schedule a call. Thanks!

@bloom @devin @MikeThacker