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

 

 

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.

 

 

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.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart
• Simple Auction Bidding In PHP MYSQL – Code Boxx