Best way to do an event registration with capacity system?

Question

I have two tables, user and event. A user can join an event, but only one. Every event has its own capacity.

For example:

The event #1 has a 20 persons limit. The event #2 has a 13 persons limit.

I have an "event" column in user table which stores the Id of the event. On the other hand, I have a "limit" column in event table.

What is the best way to achieve this?

I am using phpmyadmin by the way.

score 1 · Answer 1 · answered Aug 16 '16 at 22:23

What you need is roughly:

 update users set event_id = 'foo'
 where user_id = 'user3892111'
 and exists (select 1 from events 
             where event_id = 'foo'
             and   capacity > (select count(event_id) from users
                               where event_id = 'foo')
 );

The query will either update the row or not, according to the where clause. Because it's a single SQL statement, it's atomic, meaning there's no race condition whereby two users could get the last seat. You just need to check whether the number of rows affected by the query is 0 or 1.

Best way to do an event registration with capacity system?

1 Answers1