Just some general observations.
You seem to be arbitrarily “fixing” constraints because you’re rushing towards the finish line - by making arbitrary decisions you are trying to artificially narrow the decision space in order to eliminate potentially confusing options (i.e. “I don’t know where to start”).
So for example you simply decide that your “user activity log” has to be in a single table. Why? I’m not saying they shouldn’t be in a single table but it may be worth exploring how you came to that conclusion.
the nearest example is right here, elixir forum’s notification
So right there we’re looking at just one possible representation of your information. Ideally we would like to get this type of information in a single query and even that isn’t a hard and fast rule as there are other ways to aggregate information - but there really is no constraint here to keep it in a single table (but there may be other reasons why that might be a good idea).
my use-case example is like this:
Rather than heading straight for the database, start defining and categorizing the “bits and pieces” in terms of your problem space. Your examples seem to suggest:
- “Liking another post” notification
- (implying) “My post being liked” notification
- “Being followed” notification
- “New video” notification
- “My post being commented on” notification
- etc.
Then start looking at the various “bits and pieces” and start asking yourself some questions:
- What do some of the pieces have in common
- What are the actual differences between the pieces
- Is there a way I can factor out the differences, unifying the containing commonalities
- Is there a way I can extract out the contained commonalities, making the different containing types much clearer.
(The notion of exploring commonalities/variabilities was formalized as Commonality/Variability Analysis for multi-paradigm design and OO by James O. Coplien (1998) (who uses it now in Data, Context, and Interaction) and picked up by Alan Shalloway (2004). But commonality isn’t necessarily always expressed in inheritance heirarchies but can also be exploited through composition and containment.)
My first approach is like this:
Realize that there will be lots of times where your first attempt will not be quite right, possibly even totally wrong. Just be committed to making the necessary changes when the “pain points” of your current solution start to become obvious.
The trick is to find the “cheapest” way to exploring your particular problem space to find its “natural constraints” around which you want to build your application. So exploit low effort (non-coding) explorations whenever you can - which doesn’t mean that you should avoid spikes or prototypes, just know what they cost (and don’t get attached).