I am creating a web service for managing events. The service will have users, who are listed in one database, called Users. It will also have events, which are listed in another database, called Events.
Each event will have a list of invited users, which is variable in length. Each invited user has various properties, including if they accepted the invitation, and if they attended.
I plan to use SQL databases; either SQLite, MySQL or PostgreSQL. However, this could be changed if that was advised.
My question is how should I store the invited user info for each event? The requirements are:
I can look up the users invited to an event and the events a user is invited to equally easily.
The solution is scalable, i.e. if I end up having millions of users and millions of events then the approach can cope with that. Note that each event will only ever have a small number of guests e.g. 2-1000.
The solution is efficient, in access time, computational resources and storage requirements.
Some options I have thought of:
For each event guest list, store the user IDs and other properties as a blob, or a blob per property, in the Events database. The issue here is that the number of guests varies. Fixing the length would mean wasting space or limiting the max guest length size. Also, this approach doesn't allow a fast look up of events a user is invited to.
For each user, store the list of events they've been invited to, plus the other properties, as a blob in the Users database. Again, the issues are that the number of events varies, and this approach doesn't allow a fast look up of users invited to an event.
Combination of 1 and 2. This introduces redundancy, and the possibility of errors occurring when keeping the two databases in sync.
Separate SQLite database for each event user list. I suspect this will be very inefficient both storagewise and computationally. Also it doesn't allow the events a user is invited to to be computed easily.
Another database which stores user-event items, one for each user invited to each event. This must allow fast filtering by user or event. I'm new to databases so don't know if this can be achieved in an SQL database, or requires another type, and if so, what.
Comments on these options, and any further suggestions, are gratefully received.