招标网站数据库结构 [英] database structure for bidding site

问题描述

我有两个表:

items              bids
 -id (pk)          -id (pk)
 -user_id (fk)     -user_id (fk) references users (pk)
 -title            -item_id (fk) references items
                   -bid_price

我还希望用户可以选择接受出价.我应该如何构造数据库以具有此功能.

I also want the option for user to accept the bid. How should I structure the database to have this facility.

选项1:

bids 
-id (pk)
-user_id
-item_id
-bid_price
-is_accepted

选项2:

items
-id
....
-accepted_bid_id (fk references bid)

我应该选择哪个选项或向我建议其他选项?

Which option should I take or suggest me other options ?

推荐答案

如果每个项目仅接受一个出价，则选项2是更好的方法.选项1允许接受任何数量的出价.

If there is only one accepted bid per item, Option 2 would be the better way. Option 1 would allow for any amount of bids to be accepted.

更多理由:选项2中的 accepted_bid_id 列不仅可以确保每个项目最多接受一个出价，而且还可以使您更快地访问该出价.如果您使用布尔值(您的选项1)，则必须执行以下操作才能加入接受的出价:

A bit more on the reasoning: The accepted_bid_id column in Option 2 not only ensures that there only is a maximum of one accepted bid per item, it also lets you access that bid quicker. If you used booleans (your Option 1), you'd have to do something like this to join on the accepted bid:

SELECT * FROM items
LEFT JOIN bids ON bids.item_id=items.id AND bids.is_accepted

这要求出价的复合索引(item_id，is_accepted)要相当快.另一方面，使用选项2，您可以执行以下操作:

This requires a compound index on bids (item_id,is_accepted) to be reasonably fast. With Option 2 on the other hand, you can do this:

SELECT * FROM items
LEFT JOIN bids ON bids.id = items.accepted_bid_id

除了您已经拥有的两个主键之外，这不需要任何索引.

This does not require any index apart from the two primary keys you already have.

所以总结一下:

• 选项2保持完整性
• 选项2不需要其他索引
• 选项2允许更简单的查询

因此，没有太多内容指向选项1的方向.

So there isn't mucht that points in the direction of Option 1.

这篇关于招标网站数据库结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！