Scenario
I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure if it should be the same kind of Post, or a different type of post for diaries, because I am not sure how to implement it with a schema design).
In the business environment of relevance:
- Users could post Posts, and inside them have Photos.
- Comments, Posts and Photos can have Likes and Comments.
- Posts can have several collaborators/owners (which is why I added the Posts Participants table in the first diagram shown below).
- Users can search for Posts either by searching for keywords inside the posts texts, or by the hashtags of the posts (that's why I used tsvector type for both of them, indexed with the GiN index type)
- Users can also have private diaries, and each diary has a posts of its own.
First diagram
What I did at first is just have separate tables for everything, like here (without the diary part):
Second diagram
And then after reading this post and MDCCL's answer, I'm trying to implement this with the supertype/subtype approach. So far, this is what I got:
Considerations and questions
But I have a few problems here:
-
The Photo table has two references, one to Regular Post and one to Diary Post, which is a violation again of the subtype approach.
Should I make the Post as a general supertype, and then have "child" table Regular Post and Diary Post, or should I just combine them into one Post table?
-
The Comments can be liked. So on the one hand it acts as a supertype, with Likes having reference to its id. But on the other hand, comments are also an independent type, just like Likes, because users can put comments on Posts and Photos.
Is that something that is supported?
Best Answer