I wish to create DB in Firestore. My program will have multiple user and each user will have calendar and can create an event. The user can share this event with any other user (Similar to Google calendar) but each user can add comment only visible to self on that event. So my question is how to manage that. Options:
Create table of users and each user will have collection of events and in case the user shares and event it duplicates to other users in their own collection and each user can input personal comment. But it resolves in duplication in the DB.
Same as 1, but instead of duplicating, each user will have collection to show what shred with him and only edit the comment so self.
Preferred option. Have all events in one table, and each user will have connection table to know to which event he is connected. So my question is so: if I have lets say 10M users, and each user will have 1K events, I would have table of Events with size of 10M*1K rows. Is this acceptable to have such huge table and is this acceptable to store all events of every users in one table. And what is the time retrieval for 1 row in this case?
There is possibility that the DB will be moved from Firestore to SQL, so I need more generic approach.