ssorfonos ssorfonos - 1 month ago 8
MySQL Question

What is the best way to calculate current capacity for place?

I have database like this and I want to calculate current capacity for places.

Ex: A Place max_capaity 64, current_capacity 5.

Someone go to A Place. current_capacity should be 6.

I can create "current_capacity" column and can update it.

But is that a right way? Or should I query all person who in the A place and count current_capacity from there? (using COUNT)

Which one is right way for big or active databases?
(Imagine 2000 places and billions people)

enter image description here

Answer

You should start with a basic join and aggregation. Something like this:

select p.*, p.max_capacity - u.cnt
from places p left join
     (select place_id, count(*) as cnt
      from user_places
      group by place_id
     ) u
     on p.place_id = u.place_id;

I wouldn't recommend adding a column unless you really have to. In order to maintain the column, you then have to implement triggers, to update the value whenever a user is assigned or removed from a place.