'Advice on database modeling, OneToOne with multiple related tables

I am looking for advice on the best way to go about modeling my database

Lets say I have three entities: meetings, habits, and tasks. Each has its own unique schema, however, I would like all 3 to have several things in common.

They should all contain calendar information, such as a start_date, end_date, recurrence_pattern, etc...

There are a few ways I could go about this:

  1. Add these fields to each of the entities
  2. Create an Event entity and have a foreign_key field on each of the other entities, pointing to the related Event
  3. Create an Event entity and have 3 foreign_key fields on the Event (one for each of the other entities). At any given time only 1 of those fields would have a value and the other 2 would be null
  4. Create an Event entity with 2 fields related_type and related_id. the related_type value, for any given row, would be one of "meetings", "habits", or "tasks" and the related_id would be the actual id of that entity type.

I will have separate api endpoints that access meetings, habits, and tasks.
I will need to return the event data along with them.

I will also have an endpoint to return all events.
I will need to return the related entity data along with each event.

Option 4 seems to be the most flexible but eliminates working with foreign keys.
Im not sure if that is a problem or a hinders performance.
I say its flexible in the case that I add a new entity, lets call it "games", the event schema will already be able to handle this.
When creating a new game, I would create a new event, and set the related_type to "games".
Im thinking the events endpoint can join on the related_type and would also require little to no updating.
Additionally, this seems better than option 3 in the case that I add many new entities that have event data. For each of these entities a new column would be added to the event.

Options 1 and 2 could work fine, however I cannot just query for all events, I would have to query for each of the other entities.

Is there any best practices around this scenario? Any other approaches?

In the end performance is more important then flexibility. I would rather update code than sacrifice on performance.

I am using django and maybe someone has some tips around this, however, I am really looking for best practices around the database itself and not the api implementation.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source