CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4
John
John

Reputation: 634

Retrieve data quickly and efficient

I am using Ruby On Rails and Postgresql as a DB.

My users are subscribed to a plan and based on their plan they are allow to a number of resources, I have a DB table which is keeping track of their usage. I need to check their usage on multiple actions, I would like to know if there is a best practice of working whit this data (storing and retrieving)

My table: UserStats: id(pk), projects_left, keys_left, user_id

Usually on create actions I retrieve data and then update the data on that userstats table also there are many places where I do just a select on the table.

Upvotes: 2

Views: 71

Answers (1)

David Chan
David Chan

Reputation: 7505

If resources are also stored as database tables you can consider creating a trigger on insert to those tables which will cause the insert to fail if they exceed their limits.

this way you never need to update UserStats, you just store their max allowed.

I believe it's less error prone, handles deletes without extra code and allows other apps to modify the db

eg:

CREATE OR REPLACE FUNCTION check_limits_projects(int user_id) RETURNS TRIGGER AS
DECLARE
   my_projects_count INT
   my_project_limit INT
BEGIN
   SELECT count(*) INTO my_projects_count FROM projects WHERE user_id = NEW.user_id
   SELECT projects_left INTO my_projects_limit FROM UserStats WHERE user_id = NEW.user_id
   IF (my_project_count >= my_project_limit) THEN
       RETURN FALSE
   END IF
   RETURN NEW
END

CREATE TRIGGER 'limit_check' BERFORE INSERT ON projects;

Upvotes: 1

Related Questions