Updating auction system

[fred&ron]

I have a database where bidders can place bids on types of items rather than specific items. At the end of the auction, I need to run some sort of query to allocate specific items to specific bids so that I can create a report to inform successful bidders of their allocated item.

Right now there are 4 tables: Bidder, Bid, ItemType, Item. Relationships are Bidder->Bid, ItemType->Bid, ItemType->Item.

Bids are stored in the Bid table until the end of the auction. Theoretically I need a 1:1 relationship between bid and item, but I cant figure a way to allocate individual items to bidders.

Can anyone advise on how to solve this?

 

[jzwp22]

You say this:

Theoretically I need a 1:1 relationship between bid and item,

But you also say that each bid is tied to several items so you cannot have a 1:1 relationship. You would have a 1:1 relationship between the bid and the item TYPE but not the item itself. Do you ever relate the item type to the items that make up that type?

I would use a structure like this:

tblBidder
pkBidderID primary key, autonumber
txtBidderName
other bidder fields

tblItems
pkItemID primary key, autonumber
txtItemNumber
txtItemDescription
other item fields

tblBidBatches (this might correlate to your item type designation)
pkBidBatchID primary key, autonumber
txtBidBatchName
other BidBatch fields

tblBidBatchItems (each batch has many items)
pkBidBatchItemsID primary key, autonumber
fkItemID foreign key-->tblItems


tblWinningBids (this table relates the bidder to the batch he successfully won & in turn it is tied to the items in that batch)
pkWinningBidsID primary key, autonumber
fkBidderID
fkBidBatchID