I am trying to develop a bidding system, where an item is listed, and bidders can place a bid, which includes a bid amount and a message. An item may have an arbitrary number of bids on it. Bidders should also be able to see all the bids they have made across different items.
I am unfamiliar with SQL, so am a little unsure how to model this scenario. I was thinking the following:
- A User table, which stores information about bidders, such as name, ID number, etc.
- A Bid table, which contains all the bids in the system, which stores the bidder's user ID, the bid amount, the bid description.
- A Job table, which contains the User ID of the poster, an item description, and then references to the various bids.
The problem I am seeing is how can I store these references to the Bid table entries in the Job table entries?
Is this the right way to go about approaching this problem? Should I be considering a document-oriented database, such as Mongo, instead?
SQL will work fine like you have it set up... I would do:
create table usertable ( userID integer unsigned not null auto_increment primary key, userName varchar(64) ); create table jobtable ( jobID integer unsigned not null auto_increment primary key, jobDesc text, posterUserRef integer not null ); create table bidtable ( bidID integer unsigned not null auto_increment primary key, bidAmount integer, bidDesc text, bidTime datetime, bidderUserRef integer not null references usertable(userID), biddingOnJobRef integer not null reference jobtable(jobID) );
Now you can figure out whatever you want with various joins (maximum bid per user, all bids for job, all bids by user, highest bidder for job, etc).