Database structure for users, events and event lists

Question

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:

1. I can look up the users invited to an event and the events a user is invited to equally easily.

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

3. The solution is efficient, in access time, computational resources and storage requirements.

Some options I have thought of:

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

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

3. Combination of 1 and 2. This introduces redundancy, and the possibility of errors occurring when keeping the two databases in sync.

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

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

Answer 1

Based on your comment above, it sounds like when you said everything was stored in separate "databases", you actually meant to say "tables". I'm going to work with that assumption...

I'd suggest a simple table that stores who's been invited to which event, and what their response was.

invitations
-----------
  id (primary key for the table)
  user_ID (foreign key to users.ID)
  event_ID (foreign key to events.ID)
  response (could be a simple string, or an ID to a table of possible responses, such as "attending", "not attending", "unsure"...)
  date_sent (a date showing when the invitation was sent)
  respond_by_date (date that a user must response by)
  mode_sent (was the invitation sent by email, text message, other electronic message, regular mail, semaphore flags, etc...)

The above structure will keep track of which user was invited to which event. It will also keep track of the user's response. Other fields such as date_sent aren't necessary, but could be useful for other purposes. Data for this might look like:

invitations
-----------
id   |  user_id  | event_id   | response  |  date_sent  | respond_by_date  | mode_sent
--------------------------------------------------------------------------------------
1    | 123       | 5          | attending |  2014-12-01 | 2014-12-05       | email
2    | 456       | 5          | not attnd |  2014-12-01 | 2014-12-05       | email
3    | 456       | 7          | attend+1  |  2015-01-01 | 2014-12-02       | semaphore flags

(This assumes that the user and event tables already exist)

Would this work for your purposes?