Can I use SQL to model my data?

Question

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?

Answer 1

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).

Answer 2

You're describing a many-to-many relationship. In very simplified form, your tables would look something like this:

user:
    id int primary key

job:
    id int primary key

bids:
    user_id int
    job_id int
    primary key(userid, job_id)
    foreign key (user_id) references user (id)
    foreign key (job_id) references job (id)

basically, the bids table would contain fields to represent both the user and the job, along with whatever other fields you'd need, such as bid amount, date/time stamp, etc...

Now, I've made the user_id/job_id fields a primary key in the bids table, which would limit each user to 1 bid per job. Simply remove the primary key and put in two regular indexes on each field to remove the limit.