I am building an advertisements system for websites ( a plugin for a framework ). I am not quite sure how I should set up the table in terms of indexes.
Basically there are two options for billing - by impressions and by clicks, so I guess the table fields ( initially, without customization ) would be
this may not be valid code, just a representation
`id` int(10) unsigned AUTO_INCREMENT, `headline` VARCHAR(100), `text` VARCHAR(255), `url` VARCHAR(255), `impressions` int(10) unsigned NULL, `clicks` int(10) unsigned NULL, `threshold` int(10) unsigned
threshold will be the amount of impressions/clicks paid for, and respectivelly
clicks will hold the same amount initially, and will then decrease as the advertisement gets attention accordingly.
I'm having my doubts about this design because one of the
clicks columns has to be empty at all times, and this just doesn't seem right. Also my select queries will look a little messy
SELECT (fields) FROM adverts WHERE ( clicks > 0 OR impressions > 0)
And then from the selected advertisements I have to reduce impressions
UPDATE adverts SET impressions = impressions - 1 WHERE id IN (selected previously)
But if the advertisement is paid as clicks, setting
NULL - 1 doesn't make much sense, but seems to work, surprisingly for me.
Also what I'm more concerned about are indexes. Having in mind that most probably there will be an audience selection option, where advertisers will be able to chose their audience by, mainly, age and location. I'd like to ask, how are indexes used in such situations where there are going to be such complex queries and multiple possible columns to validate the record from (
impressions )? Indexing either of them is pointless because the driver can only use one index and is still going to have to go through all other records. This makes me think that I'm using the wrong schema, is there a better way?