Online auctions are marvelous things; bringing people together from all over the world, to sell things amongst themselves, and take a cut off the top. Everyone has something that someone else wants, so this idea is golden - and we all know how well Ebay is doing.

So now you want to build an auction, but don't know where to start? There is a lot of thought and engineering that goes into such a development, so you'll need to start out on the right foot. We'll show you the underlying database structures and how to make things work for a peer to peer auction.

This article will assume you have a good understanding of Active Server Pages and VBScript, and database methods.

Before we go jumping into code and databases, let's take a moment and examine the needs and strategies, as well as the different types of auctions.

First of all, there are many different types of auctions (which we'll discuss in the next section), so you'll have to decide what your needs are, and adjust accordingly. I'll outline the needs for our auction here, and we'll reference these as we progress. First, let's get some definitions out of the way:

• Buyer/Bidder - The person who places a bid, and wants to buy an item.
• Item/Lot - One listing in the auction (can be several actual items, but will be represented by one listing).
• Proxy bidding - A buyer places a maximum bid to never be exceeded. The actual bid then is simply the last highest winning bid plus the minimum increment. The actual bid will be increased automatically when outbid until the max is reached.
• Seller - The person who listed the item to be sold.
• Winning bid - The price a buyer pays to win an item.

And now our assumptions:

• "Second Price auction:" Bidders may place proxy bids. The winners will then end up paying the lowest winning bid for the item(s).
• We will only allow single lots to be sold. This means we will not allow groups of lots to be sold together (you won't be able to sell items A and B together). However, you can sell a bunch of items as one lot (i.e. 100 pencils in lot A).
• Once an auction is over, it is over. In some auctions, if a bid is placed within a certain amount of time (i.e. 5 seconds) before the end of an auction, the auction is extended for some time. We will not do that here.
• Number of items. In the case that two people bid the same thing, the person who bid for more items will win, regardless of who bid first. If both buyers bid for the same number of items, the earliest bid will win.
• Proxy bidding rounds. One round will be defined as when the auction engine cycles through all users and adjusts bids accordingly, based on the current high bid and the buyer's maximum bid. A round will commence once a buyer places a bid on a lot, and will proceed in the order the bids were placed. If at the end of the round, a resolution is not achieved, we will start over from the beginning.

There are also two other types of auctions that we will not use here, but may be good for reference.

• Winning Bid Auction - The winning buyer gets the requested lots for the price they bid. If there are multiple winners, everyone gets the price they bid.
• Clear Price Auction - The winning buyers get the requested lots for the lowest winning bid.

Now that we've outlined our needs, let's take a look at the database structure behind the auction.

Based on the information in the previous section, we have a good understanding of what our database schema should look like. The following tables list our database structure:

Note that the above tables are simply used for example. You may decide to store more information for your purposes (i.e., you may store more than just a name in the user table). Already, however, the schema is getting kind of complex - each table has foreign keys to one or more other tables.

First, we'll discuss the easy part. You'll have to create a few forms - one for the users to register (that is, get themselves into our AuctionUsers table), and one for sellers to post their info. These forms should be easy to create if you know how to handle forms (check out this WDVL article for more information). Basically, you should collect all the information from the forms and update the appropriate tables:

'Set variables and create object
strConnectionString = "DSN=MyAuction;UID=username;PWD=password;Database=MyAuctionDB"
set rst = Server.CreateObject("ADODB.Recordset")


'Insert info into auction table
strSQL = "INSERT INTO tblAuctions (StartDate, EndDate, SellerID)
VALUES ('" & Request.Form("StartDate") & "', '" & Request.Form("EndDate")
& "', " & SellerID & ")"


rst.open strSQL, strConnectionString


'Get the ID of the auction we just entered
strSQL = "SELECT max(AID) as AID FROM tblAuctions"
rst.open strSQL, strConnectionString
intAID = rst(0)
rst.close


'Insert item info
strSQL = "INSERT INTO tblAuctionItems (AID, Name, Description, " & _
			"MinPrice, Increment, Available)" & _
			"VALUES (" & intAID & ", '" & Request.Form("ItemName") & _
			"', '" & Request.Form("ItemDescription") & "', '" & _
			Request.Form("MinPrice") & "', '" & Request.Form("Increment") & _
			"', " & Request.Form("Available") & ")"


rst.open strSQL, strConnectionString


'Clean up
set rst = nothing

The bids are a bit harder to manage. Let's look at these in more detail.

This is the complex part - you must make sure everyone's bids are correct, update those that have proxy bids, reallocate lots to winners, notify buyers who have been outbid, and perform some upkeep.

First let's look at the code to add a bid.

Function DoBid(ItemID, BidderID, Price, optional MaxPrice, optional MaxItems)


'Set variables and create objects
strConnectionString = "DSN=MyAuction;UID=username;PWD=password;Database=MyAuctionDB"
set rst = Server.CreateObject("ADODB.Recordset")


'Check to see if a bid already exists for this buyer and auction
strSQL = "SELECT BID FROM tblAuctionBids WHERE IID = " & ItemID & " AND " & _
	"UID = " & BidderID
rst.open strSQL, strConnectionString


if rst.eof then 'A bid does not exist 
	rst.close
	'Insert info into table
	strSQL = "INSERT INTO tblAuctionBids (IID, UID, WinPrice, MaxBid, " & _
		"BidItems, WinItems, Time VALUES (" & ItemID & ", " & BidderID & _
		", '" & Price & "', '" & MaxPrice & "', " & MaxItems & _
		", 0, '" & Now() & "')"
		'Default WinItems to 0 for now


else	'A bid does exist 
	rst.close
	'Update info in table
	strSQL = "UPDATE tblAuctionBids SET WinPrice = '" & Price & _
		"' WHERE IID = " & ItemID & " AND UID = " & BidderID
end if


rst.open strSQL, strConnectionString


''Fix bidding information
call ResolveBids(ItemID)


End Function

This function basically takes some info, and either inserts it or updates it in the Bids table - fairly simple stuff. The function ResolveBids however is where all the good stuff happens.

Function ResolveBids(ItemID)


'Set variables and create objects
dim monIncrement, monHighPrice, intAvailable, intTotItems, flgQuit
dim blnResolved


'Assume bids are resolved
blnResolved = True
strConnectionString = "DSN=MyAuction;UID=username;PWD=password;Database=MyAuctionDB"
set rst = Server.CreateObject("ADODB.Recordset")


'Get information from items table
strSQL = "SELECT Increment, Available FROM tblAuctionItems WHERE " & _
	"IID = " & ItemID
rst.open strSQL, strConnectionString
monIncrement = rst(0)
intAvailable = rst(1)
rst.close


'Find the highest bid and total number items bid for
strSQL = "SELECT max(WinPrice) AS WinPrice, sum(WinItems) as " & _
	"WinItems FROM tblAuctionBids WHERE " & _
	"ItemID = " & ItemID
rst.open strSQL, strConnectionString
monHighPrice = rst(0)
intTotItems = rst(1)
rst.close


'If a user with a higher max bid exists, then
' update their bid if and only if available items is exceeded
strSQL = "SELECT MaxBid, UID, BidItems, WinPrice FROM " & _
	"tblAuctionBids WHERE ItemID = " & ItemID
rst.open strSQL, strConnectionString
if not rst.eof then
	do until rst.eof
		if (rst(0) > monHighPrice + monIncrement) AND (intTotItems > intAvailable) & _
			AND (rst(3) <> monHighPrice) then
			monHighPrice = monHighPrice + monIncrement
			call DoBids(ItemID, rst(1), monHighPrice)
			blnResolved = False
		end if
		rst.MoveNext
		'If we're at the end of the recordset
		and the bids are not yet resolved, go back to the beginning
		if not blnResolved AND rst.eof then
			rst.MoveFirst
			blnResolved = True
		end if
	loop
end if
rst.close

End Function

The main part of the above code is the last do...loop section. The process is as follows:

1. Enter a user's bid (using the DoBids() function)
2. Find the highest bid for the item, and total number of items bid for
3. Loop through database and:
4. IF the current bidder's maximum bid is higher than the highest winning bid,
   AND the total number of items bid for is greater than the number of items available,
   AND the current bidder's bid is not the highest bid,
   THEN increment the current bidder's bid by the increment value.
5. If we reach the end of the recordset, and the bids are still not resolved, the start over.

This process will automatically update all bids appropriately, and weed out those whose max bids are not high enough. The reason we check to make sure the total number of items bid for is greater than the number of total items available in step 4 is because if all bids are placed and items claimed, and there are still lots available, then everyone wins and bids do not need to be incremented.

For example, imagine there are 10 lots available, and there are 3 bidders. If bidder A wants 3 lots at $3, bidder B wants 3 lots at $2, and bidder C wants 3 lots at $1, everyone will win because there is enough lots to go around, and then some. The do loop above will only have to go through once.

However, suppose bidder C now wants 5 items. Since there are not enough items to go around, someone will have to lose out. The loop in the code above weeds this person out by checking max bids and updating the winning bids. If everyone's max bids in this scenario was $20, and the increment was $2, then bidder C and whichever of bidder A and B placed the first bid would win (remember that bid priority is placed on most lots bid for, followed by bid time).

Well, now your auction can run for an indefinite time. People can keep placing bids until you decide to stop them (good for the seller, but makes bidders kind of unhappy, to say the least). Let's discuss the mechanisms for stopping an auction.

There are two easy ways to do it. The first, and easiest to perform, though requiring more manual intervention in the long run, is to simply build in an "Active" bit field into your tblAuctions table. When you decide to stop the auction, flip the bit, and the auction is over. (You'll also have to add some code to make sure that the DoBids and ResolveBids functions don't operate on closed auctions.) Then simply query the database, find out the winner(s), and let them and the seller know. Easy as pie.

The second method is to go by the end date the seller specifies (better business, believe me). To do this, you can manually stop the auction (via the process above) when the appropriate date comes, or you can schedule a task to turn an auction off at the appropriate times. There are a few ways to do this, via your database program and the Windows NT Task Scheduler, so I won't go through each one. You could simply set the script to run every midnight or so to stop the auction and determine the winners.

If you let the seller specify an exact time for the auction to end, then you're introducing a whole new set of complications. One way to handle this is to programmatically set a scheduled task as soon as the seller submits the auction, for the end date of the auction. This requires minimum intervention, but requires you to know how to do that (for documentation on the Task Scheduler, read this MSDN article). Another method is to create your own specific version of task scheduler; create a small program that will run in the background and watch the times on auctions. When an auction end date passes, flip the bit.

Okay, so the auction is over. Now what?

Assuming that you would like minimal manual intervention, and you don't really care who wins (like the head guys at Ebay care who wins every single auction), then you could create a function to send alerts to the winner(s) and seller that will kick off when the auction is over. This function could also in turn kick off some type of payment system, but that is beyond the scope of this article. Let's look at the function:

Function SelectWinners(AuctionID, itemID)


'Set variables and create objects
dim totItems, intAvailable
totItems = 0
strConnectionString = "DSN=MyAuction;UID=username;PWD=password;Database=MyAuctionDB"
set rst = Server.CreateObject("ADODB.Recordset")


'Find the number of items available
strSQL = "SELECT Available FROM tblAuctionItems WHERE " & _
	"IID = " & ItemID
rst.open strSQL, strConnectionString
intAvailable = rst(1)
rst.close


'find the winners

'If two customers bid the same amount, the customer requesting
more items will win. If still tied, the customer placing the
earliest bid will win
strSQL = "SELECT UID, WinPrice, WinItems FROM tblAuctionBids " & _
	"WHERE IID = " & itemID & " ORDER BY WinItems DESC, Time"


rst.open strSQL, strConnectionString
if not rst.eof then
	do until rst.eof OR totItems >= intAvailable
		'Keep a running tally of items distributed
		totItems = totItems + rst(2)
		If totItems <= intAvailable then 'This buyer won
			'Send an email alerting this buyer
			call SendWinningEmail(rst(0))
		End if
		rst.movenext
	loop
end if


End Function

This is a pretty simple function. Simply loop through the bids in the correct order (by number of items won first, and then by date), and alert the buyers that they've won. Once the number of items bid for gets higher than the number of items available, every one else loses, and you can stop the loop. I won't go into the SendWinningEmail() function, but all it does is send the user specified by rst(0) an email that says they've won and for how much and how many. (For an example using email, check out this WDVL article.)

So now you've got yourself a pretty functional auction. Let's talk a bit about what we haven't covered, and a few miscellaneous things.

Things to Add

While this auction will work, it lacks a few features of finer products. For instance, you could have built in a "take less" option - if, for example, a user bids for 10 items, but is willing to take less if necessary, then this could be accommodated. It would require adding a boolean field to the database, and a few modifications to the ResolveBids() code. You should also give the seller the option to "not take less," meaning that if all the items haven't been bid for, then the whole thing is off.

Another option could be adding "deeper lots." This allows sellers to specify that they have multiple lots that they must sell together - for instance, 5 TV's and 5 VCR's. These could all go in one lot, but by separating them into multiple lots that are sold together, you are offering the seller and buyers an advantage (both pricewise and competiveness-wise).

Most auctions also allow "reserve prices." This is a price the seller specifies that must be met before any items can be sold. For example, I want to sell a car, but I won't take less than $5000. This is a bit different than the minimum acceptable bid, in that the latter is set low to encourage buyers to place bids, while the former ensures I don't end up selling my $5000 car for $2 - seller protection.

A fully functional auction should also have more robust alerting mechanisms. I haven't built in here the ability to notify users when they have been outbid. (Make sure though, that if you do build this functionality, you don't send an alert every time the proxy engine places a bid - the user would get flooded with tons of emails that aren't necessary. Rather, just send an alert when the price for an item grows larger than the maximum bid the buyer specified, to give them another chance at the item.)

Finally, you'll need more functionality than just the functions I specified here. You might want to create an admin interface that allows an adminstrator to view all bids, users, and auctions, delete specified items, modify them, etc. And you'll need to build the visual front end - something so that buyers and sellers can bid for and enter items to sell, and view the current status of an auction (the current winning bid, the number of items sold, etc). You may also wish to "beef up" the tables specified here, to allow users to enter in more specific information (i.e. a SKU number, a URL, etc).

Coding Practices

You may have noticed I specified the connection string and created a recordset object in every function. While this was done for clarity in this article, a more efficient method would be to specify a global or session variable somewhere else - for instance in the global.asa file, or an include file that is included on every single page.

Also, this type of functionality would best be wrapped up in a COM object. This would enhance speed and increase security. Do all the testing in the ASP first though!

Hopefully this article showed you that creating something as complex as an auction isn't impossible, but just takes some forethought and imagination. You're probably not ready to take on Ebay yet, but if you have need to set up something similar, you are now well-equipped to do so. There is also a lack of auction applications out there in the market now, so this is a good place to get started.

And if you do end up creating the next Ebay and become zillionaires, remember where you read it first, and send some $$ this way!

Happy scripting!

discuss this topic to forum