CODEWITHSUNDEEP
ruby-on-railsdatabase-designcalendar
JBlake
JBlake

Reputation: 1064

Proposed Simple Scheduling/Availability Database Schema

For this application, staff need to be able setup when they start & end each day of the week. They can optionally have that start/end repeat every 1, 2, or 4 weeks. For example, I start work at 9am and finish at 2pm every Monday. With simple recurrences like that, I'm thinking of having 3, maybe 2 models (for denormalization):

Event
  - start: datetime
  - end: datetime
  - type: int (appointment, time off, cancelled, etc)
  - date: date (useful?)
  - staff_id: int
  - customer_id: int
  (more, redacted)

DayTemplate
  - active_start: datetime
  - active_end: datetime
  - staff_id: int

Day
  - weekday: int
  - start: datetime
  - end: datetime
  - recurrence: int (0: none, 1: once/mo, 2: other week, 4: every week)
  - day_template_id: int

The idea is that the parameters are now set, so customers can go in and book a staff when they're available (in specific chunks, say 9am, 10:15am, 11:30 -- to allow for breaks to be setup). For any day on the schedule, you can easily query for that staff members active DayTemplate. Staff can then further tweak the schedule by booking events as "time off" to give them fine-tune control. Additionally, I was thinking of denormalizing Day into DayTemplate - I know it would look ugly, but the schema would never change, and the performance benefits would make it worth it? Is this enough flexibility?

Appreciate the feedback. I'll be building this in rails.

Upvotes: 2

Views: 2052

Answers (1)

Mike Grassotti
Mike Grassotti

Reputation: 19050

For this application, staff need to be able setup when they start & end each day of the week. They can optionally have that start/end repeat every 1, 2, or 4 weeks. For example, I start work at 9am and finish at 2pm every Monday. With simple recurrences like that, I'm thinking of having 3, maybe 2 models (for denormalization):

Dont denormalize yet. Focus on getting the business rules down, then map those to business objects in rails. Let the schema design be driven by specific app requirements.

Additionally, I was thinking of denormalizing Day into DayTemplate - I know it would look ugly, but the schema would never change, and the performance benefits would make it worth it?

For sure you should not be worrying about optimizing for performance at this point. Instead optimize for clarity and simplicity. Chances are the aspects of your app that will need performance optimization will be totally different than what you would have guessed upfront.

Is this enough flexibility?

Maybe too much. I'd recommend building the simplest possible thing you can that meets the minimum acceptance criteria. Don't try and engineer in flexibility, it's too hard to guess upfront what kind of flexibility will be required. Over time the cost of maintaining all of these "maybe-we-will-need-this-someday" features will get in the way of building stuff your real users actually want. It's counter-intuitive but keeping things as simple as possible now is the best way end up with a really feature-rich system down the line. Optimize for speed-to-deliver, get it in the hands of real users and start collecting feedback.

Also, the thing about building any kind of scheduling system is that the complexity gets out of hand really quickly. If you're doing this as an exercise it may be worth it to try and code yourself, but if this is something you really need to get done seriously consider using one of the many ruby gems designed to help with modeling/scheduling recurring events. Personally I have used ice cube but there are many choices at the ruby toolbox

Upvotes: 1

Related Questions