ssorfonos ssorfonos - 1 year ago 52
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 Source

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.