Database design for an auction style website

I'm creating an auction style website where users can bid for items. I've into a bit of confusion regarding the database design when it comes down to projects and bidding features开发者_如何学Python. Initially I thought a table called 'project' could contain a multiple-valued column called 'bids' containing bid_id's.. However after a bit of research it appears this method is a no-no.. But I'm sure I can remember a lecture or two from university that mentioned multi-valued columns in database designs. What would be the best approach for the problem?

Thanks

Dan

---

It depends on your requirements on how to design the database. If you have exactly one auction per product ID, a BID table may be enough. If each auction requires individual configurations you may end up with an AUCTION table as well:

The product table

PRODUCT
 PRODUCT_ID    -- primary key
 ....

Auction table

AUCTION
 AUCTION_ID    -- PK
 PRODUCT_ID    -- foreign key to PRODUCT
 START_TIME
 END_TIME
 MODE          -- e.g. dutch, english...
 ...

Bid table

BID
 BID_ID        -- PK
 AUCTION_ID    -- foreign key to AUCTION
 AMOUNT
 TIME
 ...

In general, you should avoid multi-valued columns in a relational database model. You should aim for normalization. If it later comes to query optimization you may need to introduce further indexes, views and/or procedures.

---

In my opinion, the best solution would be to have a table bids containing all the necessary information in columns, for example: bid_id, product_id, bid_amount, bid_time, etc.

---

this is only meta-sql, you know.

users: id, ... bids: id, auction_id, user_id, amount, auctions: id, object, ... end-date, ...

indexes on bids: auction_id, amount desc (among others like id's, names ...)