2

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.

  • Any good adserver will store every ad shown, and also to which user (or cookie, or IP), at which time, and whether and when the user clicked. That's the only way to really be able to tell how efficient your ads are; and develop strategies to decide what to show to whom and when in a (close-to) optimal fashion. This involves a large dataset (and a good deal of analytics, machine learning and other IA). Sure enough. Be ready for it. And probably, don't be overzealous: if the customer paid 10 USD and you showed 11 ads (because you had more visitors than anticipated); be happy. – joanolo Jul 2 '17 at 10:03
  • As i said, it's just a case study, it's not happening in the real world, I want to know how this could be implemented without having an extra table (which is obviously gonna get big after a while) and making troubles querying. thanks for the comment – behz4d Jul 2 '17 at 10:08
2

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.
  • thanks you, another thing, if we're showing 100.000 ads per day, would you go with a shown_ads table? so inserting 100.000 records per day, is that ok? – behz4d Jul 2 '17 at 10:47
  • If you earn money showing ads; and specially if you earn money for clicks, spend the needed amount for your infrastructure. I would go for shown_ads in most scenarios (100 K, 100 M, 100 G). That doesn't mean your shown_ads cannot coexist with this solution. Redundancy is not always bad. – joanolo Jul 2 '17 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.