Database design scenario for budget (whole and daily)

database-designnosql

Scenario: Each user add funds to his account, for example 100 USD, and he defines that the system (our website) could spend up-to 10 USD per day for showing his ads in the website. each time his ad is shown in or website, it's gonna cost him 1 USD.

P.S: we're not inserting a record into the database every time his ad is shown, we just deduct the amount for showing his ad (1 USD) from his whole budget.

because we're showing too many ads, it's not possible to insert a record for each ad show into the database.

I have 2 columns in users table, whole_budget and daily_budget, so for the above user whole_budget is 100 and daily_budget is 10.

in the above scenario, how can we manage daily budget? so when an ad is shown, how we're gonna deduct 1 USD from his account?

how can we know if he has left daily budget? so we can decide to show his ad for the rest of the day or not.

I would appreciate your help

EDIT —————

This could be simply achieved by having a table called shown_ads, and every time we show an ad, we insert a record in this table with the respected DATE() so we can later on calculate the amount of his budget already spent today. but imagine we're showing 1M ads each day, so what I believe is that it's not good to insert 1M records every day! It's just a case-study, I want to know how would you implement this.

Best Answer

The easiest solution (probably) would be:

Have one table for the whole budget for each campaign (let users have more than one ad at a time :-):

CREATE TABLE budget
(
    campaign_id INTEGER PRIMARY KEY, 
    user_id INTEGER REFERENCES users(user_id),
    /* Assumption: your currency doesn't have decimals; or you're using cents
       as currency unit */
    original_budget    INTEGER NOT NULL,  
    available_budget   INTEGER NOT NULL,
    daily_budget_limit INTEGER NOT NULL,

    -- Security checks
    CHECK(original_budget > 0),
    CHECK(original_budget >= available_budget),
    CHECK(daily_budget_limit > 0),
    CHECK(daily_budget_limit <= original_budget)
) ;

Have a second table for the budget of just today

CREATE TABLE todays_budget
(
    campaign_id INTEGER NOT NULL REFERENCES budget(campaign_id) PRIMARY KEY,
    original_daily_budget INTEGER NOT NULL,
    available_daily_budget INTEGER NOT NULL,

    CHECK(available_daily_budget >= 0)
) ;

Every day, at 00:00, perform the following operations:

  1. Subtract the spent_budget (original_daily_budget - available_daily_budget) from the budget
  2. DELETE everything from todays_budget
  3. INSERT new rows to todays_budget, with both original_daily and available_daily budget equal to the min of (daily_budget_limit, available_budget)

This can be done with the following queries:

  1. Subtract remaining from yesterday

    UPDATE 
    budget
SET
    available_budget = available_budget - 
        (SELECT
            original_daily_budget - available_daily_budget
        FROM
            todays_budget tod
        WHERE
            tod.campaign_id = budget.campaign_id)
 WHERE
      campaign_id IN 
          (SELECT 
              campaign_id 
          FROM 
              todays_budget 
          WHERE 
               available_daily_budget > 0
          ) ;

  2. Clear out everything from todays_budget

    DELETE
FROM todays_budget ;

  3. And fill it up again

    INSERT INTO todays_budget
   (campaign_id, original_daily_budget, available_daily_budget)
SELECT
    campaign_id, 
    CASE WHEN available_budget > daily_budget_limit then
        daily_budget_limit
    ELSE
        available_budget
    END AS original_daily_budget,
    CASE WHEN available_budget > daily_budget_limit then
        daily_budget_limit
    ELSE
        available_budget
    END AS available_daily_budget
FROM
    budget
WHERE
    available_budget > 0 ;

And very time you show an ad: Subtract one from available_daily_budget

UPDATE 
    todays_budget
SET 
    available_daily_budget = available_daily_budget - 1
WHERE 
    campaign_id = ((the corresponding campaign_id)) ;

Notes

  1. I'm using SQL to describe your scenario. It is possible to do the same with noSQL databses. I wouldn't. This works well under a relational model.
  2. If using SQL, steps 1 through 3 should be wrapped in a transaction, under SET ISOLATION LEVEL SERIALIZABLE; to avoid concurrency issues. If any of the check constraints fails, the whole transaction will be aborted and you'll have to retry it as a whole.
  3. I've tried to use SQL Standard as much as possible, specific databases could allow for (slightly) simpler queries.
  4. I am ignoring very many details that should be taking into consideration in a realistic situation.
Related Question