Chris Payne
September 11, 2000
Everyone knows Ebay is raking in the big bucks. Peer to peer networking is where it's at, and auctions can
make some serious money. While finding items to sell and attracting people to your auction is up to you,
this article will show you how to build an auction that actually works.
Introduction
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.
Contents:
How do I start?
The Auction Database
The Logic
Managing Bids
Resolving Bids
Determining A Winner
Considerations
Conclusions
September 11, 2000
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.
Resolving Bids Resolving Bids - Page 6
Chris Payne
September 11, 2000
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:
Enter a user's bid (using the DoBids() function)
Find the highest bid for the item, and total number of items bid for
Loop through database and:
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.
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).
The Auction Datab
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:
tblAuctions tblAuctionItems
AID Unique ID to keep track of records Autonumber IID Unique ID to keep track of records Autonumber
StartDate Date the auction started Date/Time AID Which auction does this item belong to? (Foreign key to Auctions table) Integer
EndDate Date the auction is to end Date/Time Name Name of the item being sold Text
SellerID The ID of the seller. (Foreign key to user table) Integer Description Description of the item being sold Text
MinPrice Minimum price to sell (specified by the seller) Money
tblAuctionBids Increment Value to increment proxy bids by (specified by the seller) Money
BID Unique ID to keep track of records Autonumber Available Number of items available to sell Integer
IID Unique ID to keep track of items (foreign key to AuctionItem table) Integer
UID Unique ID to keep track of bidders (foreign key to AuctionUsers table) Integer tblAuctionUsers
WinPrice Current price for a user (calculated by application) Money UID Unique ID to keep track of records Autonumber
MaxBid Maximum price for a user Money Name Name of user Text
BidItems The number of items this user bid for Integer
WinItems The number of items this user would win if the auction ended immediately Integer
Time The last time this bid was updated Date/Time
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.
NOTE: You can use a pre-existing user table if you wish. We simply provide the tblAuctionUsers table definition for completeness sake.
Managing Bids
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
NOTE: This code above is developed for Visual Basic, and the keyword "optional" in the function opener is not supported in VBScript. In an ASP then, simply leave out the keyword "optional" here, and when you call the function, pass in an empty string, i.e.:
call DoBid(ItemId, BidderID, Price, "", "")
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.
ConsiderationsConsiderations - Page 8
Chris Payne
September 11, 2000
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!
Conclusions
Conclusions - Page 9
Chris Payne
September 11, 2000
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!