Events (how to attach date-related data to any item?)

[nelsonic]

Many items on several kinds of list can have date-time related data. e.g:

• a task that has a deadline. SELECT * FROM events WHERE item_id=123
• a checklist for a morning routine that reoccurs once each week day.
• a reminder to send a birthday card 3 days before a person's birthday. 😉

The following is the outline of the schema I have in mind: (definitely not complete!)

• event - when an item has dates & times associated.

  • id: Int
  • item_id: Int (FK item.id)
  • kind: Int (FK event_kind.id)
  • start: NaiveDateTime
  • end: NaiveDateTime
  • frequency: Int (FK event_frequency.id)

• event_kind

  • name: String
    • "deadline"
    • "meeting"
    • "reminder"
    • "class"

• event_frequency

  • id: Int
  • description: String
    • "once" - if an event is a one-off and does not repeat.
    • "hourly" -
      • "every-waking-hour" - e.g: "stand up from your desk every hour"
    • "morning-and-night"
    • "daily"
    • "week-days"
    • "weekends"
    • "weekly"
    • "monthly"
    • "quarterly"
    • "annually"
    • "custom"
      • "First Tuesday of the month"

• event_invitees the people who are invited/required to attend an event

Contributions/discussion very much wanted/encouraged!