Online Auction Bidding System ERD (Simple Version)

Welcome to a quick sharing of an ERD of an online auction bidding system. Planning to start your own auctioning website or business? Here’s a simple version that you can get some inspiration from – Read on for the example!

 

 

TABLE OF CONTENTS

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

SIMPLE AUCTION SYSTEM

To keep things simple, I have generalized the entities into 3 groups.

  • Users – The white elephant in the room. Users of the system – staff members and customers.
  • Items & Bids – The items available and the bids.
  • Categories – Kind of “optional”. But it does make searching for items easier.

 

 

FOR REFERENCE ONLY

I can hear the expert trolls screaming “the diagram is not accurate” and “I can create a better system”. By all means, go ahead and bang on your monitor. This is only an example for reference, and some assumptions were made.

  • An item can have none or many categories – You decide if items must have a category, or can have only 1 category.
  • An item can start with 0 bids – If you enforce “the first bid is the starting bid amount”, then there must at least be one bid entry.
  • A user can make multiple bids on the same item. If you are running an “anonymous one-time highest bidder wins”, go ahead and change that relationship.

This list can go on forever. You are building your own system, you decide your own rules.

 

TABLE DETAILS

Now that we are done with the overview, let us get into more details on the entities or tables.

 

PART 1) USERS

COLUMN DATA TYPE NOTES
user_id INTEGER Primary key.
user_role VARCHAR You decide. For example, “s”taff, “c”ustomer, “a”dmin.
user_name VARCHAR The user’s name.
user_email VARCHAR User’s email.
user_tel VARCHAR User’s telephone.
user_address VARCHAR User’s address.

Some people may prefer to have separate “staff” and “customers” tables, but I like to keep things simple. A single “user_role” will prevent multiple tables, and also allow for future expansion – Maybe “l” for logistics staff in the future?

 

 

PART 2) ITEMS & BIDS

2A) ITEMS

COLUMN DATA TYPE NOTES
item_id INTEGER Primary key.
user_id INTEGER Foreign key.
item_name VARCHAR Name of the item.
item_desc VARCHAR Description of the item.
item_start DATETIME Bid start date/time.
item_end DATETIME Bid end date/time.
item_open FLOAT Opening bid amount
item_bid FLOAT Minimum bid increment amount.

This table should be pretty self-explanatory, the items available for bidding. Feel free to add more fields as required – Maybe a short summary, picture, featured flag, etc…

 

2B) BIDS

COLUMN DATA TYPE NOTES
user_id INTEGER Primary Key.
item_id INTEGER Foreign key.
bid_time VARCHAR Name of the ad campaign.
bid_amount TEXT A description of the ad campaign.

Once again, this table should be pretty self-explanatory. As people bid on the items, this will record the amount.

 

 

2C) BID CLOSING

COLUMN DATA TYPE NOTES
item_id INTEGER Primary Key.
user_id INTEGER The related user for this entry, can be a customer or staff.
close_date DATETIME Date/time when this entry is made.
close_status VARCHAR You decide. For example, “w”inner goes to ABC. “D”elivered to winner. “C”anceled.
close_notes VARCHAR Notes, if any.

So what happens after the bid closes? Some may think “the highest bidder wins”. That is correct, but what if the highest bidder forfeits? What if the winner did not respond? What if the seller disappears? This table is used to record the “post-bidding” processes.

 

PART 3) ITEM CATEGORIES

3A) CATEGORIES

COLUMN DATA TYPE NOTES
category_id INTEGER Primary Key.
category_name VARCHAR Category name.
category_description VARCHAR Category description.

As previously mentioned, this is kind of optional. But it does organize things better, making searches easier.

 

3B) ITEMS OT CATEGORIES

COLUMN DATA TYPE NOTES
product_id INTEGER Composite primary key.
category_id INTEGER Composite primary key.

Which item is tied to which category.

 

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

LINKS & REFERENCES

 

INFOGRAPHIC CHEAT SHEET

Online Auction Bidding System ERD (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *