Penny auction concept and how the timer works

Question

I am creating a penny auction site using PHP yii framework. The main consideration of the system is to update the database records of all active auctions (max 15 auctions) with the current ticker timer.

I am seeking for advice on how I should design the system where every auction item will have its own countdown timer stored in the database. When someone bids the auction item, the counter should reset to 2 min. All users who are connected to the system should see the same countdown timer for that particular auction.

I am little confused on how I should design the system. Will there be a performance issue if there are frequent updates to the database (Mysql)? If there are 15 active auctions, and they are updated every second, the countdown timer decreases by a second in the database table for the particular auction.

My first idea for the underlying schema, for a table called auction_lots:

Auction_id,startdatetime,counter_timer,status

I am seeking for advice on how I should design this.

Answer 1

I would not recommend updating the records of all active auctions every second. I think the better way would be to store an expiration time for every auction as date and time (or a timestamp) and modify it only when an event happens (eg. when someone bids the auction item, set it to current_time+2 minutes).

To make everyone see the same value, you should write a simple script that computes the number of seconds to the end of all active auctions:

$time = time();   //current timestamp from server
//for each active auction:
$secondsToEnd[$auction->id] = $auction->expiration_timestamp - $time;
echo json_encode($secondsToEnd);

Then make every client read the $secondsToEnd from the server via an AJAX call every second.

I checked one Polish penny auctions site and I think that is how it works. The real problem I think is when two people click "bid" in a very short period of time. Then one of them should be given an error.