In my application I have Users table in DB and theirs Posts on forum.
On every post I have a link to a user profile who wrote it.
Now, if I remove the user from DB - I want his posts to stay, but with an attribute, that “this user (author) has been deleted”.
How should I treat in this case ? User may left several thousands of posts, so if I want to remove the user, I need to select all his posts and update them one-by-one ? It may take long time.
If I mark the user like “disabled” (and don’t remove it actually) then front-end will need to send lots of additional requests for every rendered post - this is even worse.
So how should I treat with child objects if I remove the parent object ?
What do you think ? May be there is a better approach?
You can not keep user information & posts together in same document in a collection, as you'll retrieve users details & posts separately for different purposes & also adding high no.of posts to users document is not feasible to do. So what you did by having two different collections Users & Post is correct & In Posts collection maintaining a single key field which relates to User (unique userid or _id of user document) is also correctly done.
You can avoid storing User relationship in Posts & Store all posts ids in User collection only if posts are very less in number & also this doesn't work in all of your cases. To say designing database really depends on your DB operations.
I need to select all his posts and update them one-by-one ?
No, you don't have to select & delete one-by-one, Upon successfully deleting/disabling a user you would run a single query on Posts using .updateMany() with a filter (userId or _id) to find all docs related to particular User & add a new field called isUserDeletedOrDisabled : true, So when ever you retrieve posts for various pages of your application you would avoid retrieving those posts which has flag isUserDeletedOrDisabled & its true, In general while inserting new Posts docs - you don't need that flag. Upon User updates you'll add this field with true & make it false once user is back..
for example Mongo DB where joining 2 tables is expensive operation ?
I don't think joining two collections using $lookup is much expensive, it all depends on your dataset size/index/MongoDB configuration. Have indexes on keys fields (like in Posts collection on User related field plus isUserDeletedOrDisabled). So for most applications if should work just fine with proper configs in place.
Aggregation can help you to achieve most of the daily needs with ease, additionally you can check your query performance & improve it by looking in executionStats provided by .explain() (or) $explain. For more read : query-optimization