The Important Dates Data Model

Are you forgetting something? A data model to help you remember important dates – before they happen.

Have you ever forgotten an important date – your mom’s birthday or your anniversary? Or that you’re giving a lecture? Yup, things like that happen in real life. Maybe not to all of us, but to some of us (including me), they certainly do. To prevent such disasters, we’ll create a data model you could use as the background for an application that will notify you right on time.

It’s time to say goodbye to all those disappointed and sad faces and to gifts that were not bought on time. :)

Let’s dive right into the model.

The Data Model

Our goal is to create a data model for an application that would allow us to define future events and all actions related to them. The app should notify users when they do a certain real-life thing and mark that thing as done when it’s been completed. Some tasks are repeating, e.g. that event triggers a future event at a time we’ve defined.

Of course, we’ll also need to develop web and mobile applications to make this system really useful. But for now, let’s focus on the data model.

The model consists of three subject areas:

• User accounts & dates
• Events & actions (definition)
• Events & actions (real)

We’ll describe each of these three subject areas in the order they’re listed.

Section 1: User Accounts and Dates

Users of our application can create their own user profiles and store important dates of their choosing. To support that, we’ll use the following tables.

The user_account table is similar in structure to the ones described in many data model articles, but let's repeat it once again. For each user, we’ll store:

• first_name and last_name – The first and the last name of the user.
• user_name – The user’s selected username.
• password – The hash value of the password the user selected.
• mobile – The mobile phone number provided by the user.
• email – The email used during the registration process.
• confirmation_code – A confirmation code sent to the user to complete their registration.
• confirmation_time – When the user completed the confirmation process.
• insert_ts - The timestamp when this record was inserted.

After the registration is completed, the user will be able to select their own important dates. This list is stored in the selected_date table. Although we’re talking about dates, what the user is actually selecting are rules that will denote dates. We’ll first describe all the attributes in this table and then we’ll discuss how users can set rules using those attributes. The attributes are:

• user_account_id – The ID of the user that inserted this record.
• date_year, date_month, and date_day - Integer values representing the date parts (year, month, and day of month).
• date_weekday – A textual representation of the ordinal number of the day of the week. We’re using text because it allows users to select more complex values – they can define both the weekday and the week in the month, e.g. the second Monday in each month.

Please notice that all four date parts could be NULL. We won’t allow records with all NULL values, so we’ll programmatically check that at least one date part is NOT NULL.

And now a few examples:

• If we want to select an exact date, e.g. 31.12.2018, we’ll set values to date_year = 2018, date_month = 12, and date_day = 31. This defines something that will happen only once, on that single date.
• If we use the combination date_year = 2019 and date_month = 1, leaving the remaining two values NULL, then we’re defining something that will repeat during the whole of January 2019.
• The combination date_year = 2019 and date_day = 2 would trigger an event on the second day of each month in 2019.
• If we insert the value , we’re defining something that will happen on the second Monday of every month.

Section 2: Events and Actions (Definition)

I’ve been mentioning a vague “something”, but that something is actually an event. Events and actions are the reasons why we’re here. We want to relate the time domain with actual events and actions that will happen in the future. In this subject area, we’ll store the definitions for all events and actions. These definitions will later be used to create actual events and actions.

The event table is definitely the central table in this subject area, but before describing it, I want to describe two dictionaries, the event_catalog and the recurrence_interval. Both of them have the same structure, with an auto-incrementing primary key (id) and the UNIQUE name attribute.

The event_catalog dictionary will store values like “birthday”, “public holiday”, “anniversary”, and “other”. This will help us classify our events.

On the other hand, the recurrence_interval will store values like “year”, “month”, “week”, and “day”. This value denotes the unit of the time that will pass before the referred event/action repeats (if it’s defined as a recurring event). When that time period passes, a new instance of the same event/action will be generated.

Now we’re ready to get to the heart of this subject area. In the event table, the user defines all the events that are important to them. For each event, we’ll store:

• selected_date_id – References the date definition.
• event_catalog_id – Denotes the type of the event.
• description – An additional textual description of that event.
• recurring – A flag denoting if the event is recurring.
• recurrence_interval_id – Defines how often the event repeats (year, month, etc). Combining the date definition from selected_date with the recurrence interval will enable us to define the starting point of the event and how many events after that starting point will be created automatically. This way we could define something like: “Starting from the 2^nd Monday in each month (the selected_date table), automatically schedule daily meetings (the event.recurrence_interval attribute)”.
• recurring_frequency – A number denoting how many units (defined by recurrence_interval_id) have to pass before this event takes place again (if it’s a recurring event). For the previous example (daily meetings), we would define this value as 1.
• recurring_times – The number of instances of this event. For the previous example, this would be “5” (daily meetings from Monday to Friday).

Next, we’ll need to relate people (known by the user) with events. A list of all the people inserted by our users is stored in the person table. For each person, the user will define a full name and any additional details (if needed).

Now, these persons can be related to the user’s events. In the related_event table, we’ll store references to the event and person as well as some details of the nature of that relationship. Please note that same person could be added multiple times for the same event. This could make sense if we want to keep more than one record to specifically point to something special (e.g. “invite Sofia to the party”; Sofia is both a party guest and the singer for the band at the party).

The remaining two tables in this subject area are related to action definitions.

Actions can be anything related to the event. For example, if we want to remind ourselves of mom’s birthday, it would be great if the application tells us: “Start thinking about the gift you want to give your mom”, “Buy a gift for mom’s birthday”, “Give mom her B-day gift. And a few kisses, too” and finally “You’ve done it successfully again this year. Bravo for you (and for me)!”

Okay, let’s get serious again. Actions are sets of predefined texts that should notify users when to do something. We’ll have a dictionary with predefined action types like “start thinking”, “buy a gift”, “find a musician”, etc. A list of all such UNIQUE actions is stored in the action_catalog table. When defining an event, the user will choose one or more actions related to that event and define the following values for each of them:

• event_id – The ID of the related event.
• action_catalog_id – A selected value from the action_catalog dictionary.
• description – An optional description of that action. Each time this action is triggered, our application will look at this attribute, read the commands, and perform that action.
• action_code – A structured textual definition of that action.
• starts_before – Defines how many selected time units will elapse before the start of this action for the selected event (if this is a recurring action). If this value is not defined (i.e. is set to NULL), then the actions will start at the same moment the event starts.
• send_message – A flag denoting if a message should be sent to the user or not.
• recurring – Denotes if this action is recurring or not.
• recurring_interval_id – Denotes the interval/unit for the recurrence (if this is a recurring action).
• recurring_frequency – Denotes the number of selected units that must elapse between two recurrences of the same action (if this is a recurring action).
• recurring_times – How many instances of this action shall we create?

Action recurrence follows the same pattern as event recurrence. If the action is defined as recurring, we’ll generate a new action instance after the defined time period.

Section 3: Events and Actions (Real)

So far, we’ve created a data model that would enable us to insert events and define actions. Now we’ll move to a more interesting part of the model: actual events and actions.

The event_instance table contains a list of all events that have been generated automatically or inserted manually. While auto-generation is pretty obvious – that’s why we’ve created this model – manual event insertion is also a possibility. We can expect that an event will be inserted automatically at the time it’s due, so we’d normally have only actual and past events in this table. Still, it could happen that we’ve already taken care of some future event, e.g. we have prepared for a meeting that will take place next month. In that case, we should be able to manually insert a future event (event times being proposed according to defined rules) and everything related to that event into this table. On the other hand, our application won’t overwrite or duplicate that event. It will recognize events we’ve already inserted by using the event_time value. For each event instance, we’ll define the:

• event_id – References the event definition.
• event_time – The actual event time, in structured textual format.
• insert_ts – The actual timestamp when this event was inserted.
• event_completed – A Boolean value denoting if the event was completed or not. The event is automatically set to ‘completed’ if all related actions are completed. It can also manually be set to ‘completed’ by the user.

The event_id – event_time pair is the alternate/UNIQUE key of this table.

Similar logic is used for the action_instance table. Actions will also be generated automatically when they are due. If an action is recurring, we’ll have more than one action defined for the same event instance. For each action, we’ll define:

• action_id – References the related action.
• event_instance_id – References the related event_instance.
• action_time – The actual time of the action, in structured textual format.
• insert_ts – An actual timestamp when this event was inserted.
• action_completed – A Boolean value denoting if the action was completed or not. The action is set to ‘completed’ manually, by the user. If the action instance is set to ‘completed’, new instances won’t be generated (even if the definition says they should be).

In this table, the alternate/UNIQUE key is the combination of action_id – event_instance_id – action_time.

The last table in our model is the message table. It’s used to store the messages generated by actions. These messages are sent to users. For each message, we’ll store:

• action_instance_id – The ID of the action_instance that generated this message.
• message_title – The message’s title.
• message_text – The message text, which contains a description of why this message was generated ( i.e. textual fields from the related tables).
• insert_ts – The timestamp when this message was generated.
• message_read – A flag denoting if the message has been read by the user.

Share Your Thoughts on the Important Events Data Model

I hope you’ve enjoyed today’s article. Have you ever forgotten about an important date? Do you think this model could help you? Please tell us in the comments below.