Tips on designing this recursive database model
In my Twitter clone, a post sometimes needs to be displayed by itself, and sometimes together with its parent post.
I do this by including the
replyToPost
in the query.
But every post can also be a repost of another post, so both the post and the ´replyToPost need to include a potential
repostOfPost.
And each post can also contain a quote of another post. So there are 3 of these relationships in my model. To get the data I want, I needed to build this very confusing
includes` setup.
Is there a better approach than this?![No description](https://cdn.answeroverflow.com/1250355074543390782/image.png)
7 Replies
Some feeds render parent posts if one exists:
If the post is a repost of another post, the
Post.tsx
component detects this and decides which post to show:
This all works but the types are really messyIt’s a bit late in my day, but I think one change might make your structure and queries a lot nicer.
Posts can be replies to other posts. That you can model by setting a “parent” or “replyTo” on a post.
Then, you have to handle normal posts, reposts, and quote posts. In my opinion, it would be a lot easier to model all of these as the same thing.
A normal post is a post with text, attachments, etc.
A repost is a post with no text attachments, etc, but with a foreign key “repost” pointing to another post.
A quote post is both. A post with text, attachments, etc that also references another post via a “repost” field.
Does that make sense?
Yes but this is almost what I have, isn't it? I have a
repostOfId
FK. I could get rid of the quoteOfPostId
with your suggestions, but would that make the structure a lot easier?
In my JS code, the additional distinction helpsIf the distinction helps you then I would keep it 🙂
My suggestion was if you’re looking to simplify the queries, then reducing the number of joins you have to do would be the first thing I would look at
This will become even more complex if I ever need to add more FKs and query them. I was wondering if my whole DB structure is wrong.
It already feels impossible to work with in its current form
I'm not a database expert
I'll try your suggestion and remove the separation between quotes and reposts. It will only get rid of one FK but maybe that's enough.
A related question: Is it correct to create functions like
getPostInclude
around Prisma.validator
? I do that because I need the authenticated user's ID in the query.If you're using TypeScript, the
satisfies
operator might be better: https://www.prisma.io/blog/satisfies-operator-ur8ys8ccq7zb
But, yes, those functions look right to mePrisma
How TypeScript 4.9
satisfies
Your Prisma WorkflowsLearn how TypeScript 4.9''s new
satisfies
operator can help you write type-safe code with PrismaThank you for the hint