Starting the Auction System example

Over the next few lectures I am going to be building out an extended example of a back end system. This system will support an online auction marketplace.

In this first lecture I am going to describe the structure of the database for the system and set up some basic business rules for the system.

This lecture assumes that you know the basics of relational databases and SQL. Since we covered this material in CMSC 250 I will not be covering the basics in this course. If you feel you need a refresher on the basics of SQL, there is a very good introductory tutorial on SQL available at https://www.w3schools.com/sql/.

users, profiles, and shipping

The first three tables we will cover store basic information about users.

The users table stores user ids, user names, and passwords for all system users.

In place of the usual integer identifiers for users I am going to use a more secure system based on the use of universally unique identifiers, or UUIDs. The following snippet of SQL demonstrates how we can get MySQL to generate those identifiers for us when we insert a new user:

insert into users(userid,name,password) values(uuid(),'Joe','hello')

Note that user names are required to be unique.

In addition to basic login information each user also has a profile that stores additional information about that user:

Users can opt to make their profiles either public or private. The expectation is that users who create auctions will have public profiles that include contact information, while users who use the system to only bid on auctions will generally keep their profiles private.

Shipping addresses are stored in a separate shipping table.

The expectation is that every user will have at least one shipping entry associated with their profile. The shipping field in the profiles table will point to that entry. Users are allowed to have multiple shipping entries, because we will want to give users who purchase items in auctions the option to have those items shipped to a different address than their profile address if they choose.

auctions, tags, and bids

The central activity in our system is auctions. Sellers will post items for sale. Auctions will run for a predefined span of time, during which buyers can post bids. After the auction concludes the system will start a sale process to sell the item to the highest bidder.

Auctions are stored in the auctions table:

We will also use UUIDs as identifiers for auctions. Each auction has a reserve price, which is the lowest bid that the seller will accept for the item.

Throughout the entire system will be storing monetary amounts as integers. Amounts in dollars and cents will be converted to integers by multiplying by 100. For example, $12.95 will be stored as 1295.

The completed field records whether or not the auction has concluded. When the auction concludes we will select a winning bid for the auction and set up an entry in the purchases table to manage the process of selling that item to the winning bidder.

To make it easier for users to find auctions of interest, we will allow sellers to attach tags to auctions. For example, a seller selling vintage used clothing would attach the tags 'used', 'vintage', and 'clothing' to their auction. Tags are stored in a tags table.

Bids that users place on auctions are stored in a bids table:

Purchases, balances, and transactions

Once an auction has concluded we will begin the process of transferring ownership of the item to the winning bidder.

We can determine the winning bidder for an auction by consulting the table of bids. The next step is to create an entry for the winning bid in the purchases table.

Each purchase effectively associates a winning bid with an auction. After a user wins an auction we will ask them to provide a shipping address. The shipping field is a foreign key into the shipping table where that shipping address is stored. We will then ask the seller to compute shipping charges, which will be stored in the charged field. Once the purchase has been completed and the item has been shipped the seller will put a tracking code in the tracking field.

Purchases will go through a multi-step process. I will describe that process in more detail below. The status field is used to track our progress through that process.

To manage payments in our system each user of the system will have an internal system balance. That basic balance information is stored in the balances table:

Any changes to these balances are recorded in a separate transactions table.

I am assuming that the system will use one or more external providers to manage the flow of funds into and out of the system, and that any provider will give us a unique receipt identifier for each such transaction. In the transactions table we can record these types of transactions:

reason	description
fund	Transfer money from external account to internal balance
cashout	Transfer money from internal balance to external account
sale	Transfer money from buyer to seller
charge	Deduct system charge from user's account

One of the transaction types we can model here is the charge type, which represents a payment from a user to the system. For example, the auction system may take a percentage cut from each sale as a charge deducted from the seller's account.

Tracking the purchase life cycle

When a bidder wins an auction we will create an entry in the purchases table for the winning bidder. After the bidder wins the auction there are several additional steps that we will need to carry out before concluding the purchase. The diagram below illustrates the steps in that purchase process.

Here is further detail on the meaning of each of these states and the transitions between them.

1. The process begins in the Start state. At this point we have not yet created a purchase table entry.
2. When an auction concludes the system will automatically carry out the select process, which finds the winning bid for the auction and creates a new entry in the purchases table for that winning bid. At this point the purchase is in the won_bid state.
3. The winning bidder will be asked to confirm that they still want the item they bid on. To confirm the winner must provide shipping details. If they confirm and provide shipping details the state of the purchase is set to confirmed. If they cancel the purchase the row for this purchase is removed from the purchases table and their bid is removed from the bids table.
4. Once a purchase is confirmed and the buyer has provided a shipping address the seller will be asked to compute shipping charges. Once they enter the shipping charge the state of the purchase is set to charged.
5. Once the shipping charges have been computed the buyer will be asked to pay the full amount of the purchase. If they pay the state of the purchase is set to paid. At this point the buyer also has one last chance to cancel the purchase. If they cancel the row is removed from the purchase table, their bid is removed from the bids table, and we go back to start and select a new winner for the auction. At this point the seller can also cancel the purchase if the buyer has not paid for the purchase within a reasonable time span.
6. Once the seller confirms that they have shipped the purchase and have recorded a tracking code, the purchase transitions to its final state, shipped.

Two views

One of the most important tables in the application is the purchases table, which stores data that we will collect as the purchase process runs through its various stages. Here again is the structure of that table:

One of the peculiarities of this table is the fact that it contains just enough information to do the job. For example, we don't record the id of either the buyer or the seller in this table, since that information already appears in other tables in the database. For example, the bid field is the id of an entry in the bids table. That table tells us what auction the bid was for and also stores the id of the user who placed the bid.

The auctions table can tell us who the seller in the auction was, and also provide additional details about the item being sold.

What we are seeing here is quite typical in the world of relational databases: information that we might need to fully describe a purchase is in the database, but it is scattered across multiple tables in the database.

The standard remedy in situations like this is to use the SQL JOIN command to merge together information from multiple tables. For example, we can synthesize information from the bids and auctions tables via a select that uses a JOIN:

select bidid, auction, item, seller, bidder, bid from bids join auctions on bids.auction=auctions.auctionid

We can also use this select statement to define a view in the database. I used this select statement to create a bid_detail view in the database that contains more information beyond what the bids table is able to tell us.

I also created a second view, the purchase_detail view, by doing a join between the purchases table and the bid_detail view. This view supplements the purchases table with columns that tell us the ids of bidder and the seller, and also the bid amount.