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