拍卖风格网站的数据库设计

Question

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

Answer 1

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.