0

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:

  1. 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.

  2. 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.

  3. 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?

  4. There is possibility that the DB will be moved from Firestore to SQL, so I need more generic approach.

1

You should avoid duplicating rows in a database design. You have a many-to-many relationship between events and users, so you will need the "connection" table between them to do that. There will be a one event to many connection relationship and one user to many connection relationship.

The connection table will have FK columns to both event and user, plus any attribute that is useful about that connection (e.g. visibility indicator, date accepted). Make the two FK columns be a unique constraint to make sure there are no duplicates.

You will need another table, perhaps named USER_EVENT_COMMENT, that will also FK to the user and event tables. You can either use an incrementing as a PK or combine the two FK columns with something else (perhaps a date) to make a composite PK. I think the auto increment approach is cleaner.

As to restricting the visibility of the comments, that depends on how you access the database from your application. If all access to the database is the same identity, then your application will need to enforce visibility. If you access with different identities, you can enforce visibility in the database.

As for size, a few billion rows is no big deal. The proper indexes and thoughtful query construction (use bind variables) and you should be fine.

2
  • Another important aspect to think about is the data lifecycle: how long do you retain events once they have occurred ? You mention 1000 events per user: that would be over the course of several (many) years. If you limit the database to upcoming and current events then the number of rows in the events and user-events tables is much less. Same for users who close their accounts. – Albert Godfrind Sep 5 '20 at 6:30
  • Another aspect to consider is recurring events (like monthly meetings). That is easily represented by having an EVENTS table and a child EVENT-OCCURRENCES table. – Albert Godfrind Sep 5 '20 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.