Structure database for timeline with different objects

Question

I need to work on a project where we have a "case". Within this case we need to attach different things, in a timeline.

Those things can be:

• Status change (Open, On hold, Closed)
• Messages (just text)
• Calls (call history, "call lasted x seconds")
• Call rating

Now I don't really have a way of storing this in a MS SQL database. We are using EF Core for net5.0.

How do I make a database structure that has different tables all linked in one timeline? I'm struggling with my data layer.

Example:

10:12 AM - Pete wrote: "Foo bar"

10:14 AM - Pete called John, call lasted x seconds. Click here to view the call.

10:14 AM - John rated the call x x x 0 0. (three stars visualized)

11:12 AM - Pete put this case on hold.

11:14 AM - John wrote "ABC"

11:15 AM - John closed this case

This is the "timeline" for a case in text. But ofcourse in the actual front-end we'll show a thumbnail of the video call. And we'll show a status change in color. And we'll show actual stars instead of writing x x x 0 0 .

I don't expect an answer where my program is realized. But could you point me in the right direction so that this is expandable with new tables in the future.

Answer 1

This looks very much like events with associated data depending on event type.

So the main EVENT table could have fields ID, TYPE, TIMESTAMP, CASE_ID, ORIGINATOR, and a few auxiliary fields (such as TEXT, DURATION, RATING etc.) that could be used in different event types.

For status changes, you would have to decide whether there's a generic event type "StatusChange" and the new status is represented in the TEXT field, or there are different event types for the different possible status values.

Associated data that's not easily represented in an auxiliary field would reside in additional tables or even outside the database (you would probably not store media such as video recordings or even thumbnails in the database.) If additional tables are used, you might connect them to the respective events through an EVENT_ID foreign key field. For external media, it would be more fitting to have a MEDIA_URL field in the main EVENT table, and a THUMBNAIL_URL field for thumbnails to be shown in the UI.

Answer 2

The simplest thing that springs to mind is a view that UNIONs the relevant information from each thing table into a summary text, a case id, maybe a thing type, a timespan and whatnot. You can then index the view on case id and timespan and you got your timeline.

One of the advantages of this is that things can have their own tables, normalized as you see fit.

One possible downside to this is that whenever you have changes to the structure of a thing (add/remove/rename columns, add/remove thing tables) you will have to update the view.