Advice on database modeling, OneToOne with multiple related tables

Question

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.

Answer 1

I would keep it simple and choose option 1. Splitting up data in more tables than necessary for proper normalization won't be a benefit.

Perhaps you will like the idea of using PostgreSQL table inheritance. You could have an empty table event, and your three tables inherit from that table. That way, they automatically have all column from event, but they are still three independent tables.