0

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.

  • 1
    I think reading up on some relational database design will help. You won't want to have separate databases for each of these - you'll want separate tables in a single database. Getting a better hold of these concepts will help you draft a better schema. – LowlyDBA Dec 9 '14 at 14:44
  • Thanks, John. I will do that. I suppose where I say "database" you can therefore read "table". – user664303 Dec 9 '14 at 14:46
5

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?

  • This is the obvious solution. As far as scalability, as long as appropriate indexes exist this should be very quick to query. – JNK Dec 9 '14 at 15:26
  • It could be. The query time is the important thing. JNK mentioned appropriate indexes. Are these maintained automatically by the database, once created? Sorry for the newbie questions. – user664303 Dec 9 '14 at 15:42
  • @user664303: That depends on your database. Most databases will automatically create an index on your primary key. If you know that most of your queries will also reference both user_id AND event_id, you might want to create an index that contains both columns. You could also probably create two separate indicies, one for each column. Your best bet might be to populate the table with test data, run your queries and analyze the performance and query plans to determine the best way to create indices. – FrustratedWithFormsDesigner Dec 9 '14 at 16:12

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.