Database design for ads which can be promoted

Question

I've got an ads table, with ads which can be promoted in two ways. They can be promoted in search results, so they come before the others, or they can be promoted to the front page, where a random selection of a few promoted ads are displayed. They are to be promoted for a certain time period.

I'm not sure how to best do this.

Another table for these promoted ads, and should I have a cron job to delete them once they expire, or... how?

Answer 1

The best thing would be to have a second table, with a foreign key to the ads table, a column for the promotion rank (if desired), a column for the expiration date, and a column showing whether it's front page, search, or anything else you want to add later. (ie: 1 for search, 2 for front page, or 'search' and 'front_page' respectively)

When you select an ad to promote, you select from the AD_PROMOTION table and join it to the ADS table. Then, rather than using a cronjob to kill it when the expiration date has gone, you just demote or remove it when it gets selected.

while (ad != null) {
    ad = getRandomAd()
    if (ad.expires < now()) {
        demote(ad);
        ad = null; // Make sure you get another one
    }
}

Of course, you'd need something in place to make sure you wind up with an ad at some point, since you may not have any promoted ones.

ADS
AD_ID    INFORMATION
-----    -----------
1        'Max Power'
2        'Min Power'
3        'Homer Simpson'

AD_PROMOTION
AD_ID    LEVEL    EXPIRATION_DATE    PLACE
-----    -----    ---------------    -----
2        2        2010-02-18         'search'
3        3        2010-03-01         'front_page'
3        2        2010-04-01         'search'

Answer 2

I think there are two concepts here, the ad and the publication.

Talking specifically about database design, it depends on how much information you want to add to the publication.

If it's just the type, you can do with a (possibly numeric) field in the same table.

If you need to add more publication specific info, you will be better off with a new table for these, this opens some interesting design questions like if an ad can have only one or many different publications (appear first and also include it in a mail newsletter for example).