Design help, please

[Guest]

Hello. I just designed a new section of my db. We receive
donated auction items and want to track the AuctionGiver
and AuctionGift. We also want to create auction sheets
(that give the price, minimum bid, room for people to make
bids). The auction sheet has the description of the
auction item. All went well.

Until I realized that we often combine auction items to
create gift baskets (things that go well together like a
hotel stay and airline tickets, or small items that are
grouped together for greater value). We need to keep
these items separate in that we need to be able to track
which AuctionGiver gave which AuctionGift. But I'd also
like to create a "Basket" section where we could track
AuctionGifts lumped together.

I'm a little mystified how to do the "basket" thing. I'd
appreciate any suggestions. Thank you for your
consideration, Sal

 

[Guest]

What you would want is five tables (PK = primary key; CPK = composite
primary key)

tblAuctionDonors (the people who donate gifts)
AuctionDonorID (PK)
AuctionDonorName

tblAuctionGifts (the gifts you get for an auction)
AuctionGiftID (CPK)
AuctionDonorID (CPK)

tblAuctions (the individual auctions)
AuctionID
AuctionName
AuctionDate

tblAuctionItems (the items that are available for each auction)
AuctionItemID (CPK)
AuctionID (CPK)
AuctionItemName

tblAuctionItemContents (the gifts that are in each item)
AuctionItemID (CPK)
AuctionGiftID (CPK)

 

[Guest]

Ken, thanks for the clarification- I appreciate it and was
able to build the tables, but of course now I'm struggling
with the forms.

I created an AuctionDonor form that contains pertinent
donor information from the tblAuctionDonors (name,
address...). I originally had a subform that showed all
of the things that the donor had given including the
value, minimum bid, description. But now I'm confused
because the tblAuctionGifts that contains the gifts we get
for an auction doesn't seem to contain any of that
information, rather tblAuctionItems does.

I still must track the description of each item donated
and link it to a donor (so I can thank them properly for
their gifts and what-not). So I'm not sure what the Donor
form / items subform would look like.

I like the idea of tblAuctionItems to show items available
for each auction. Question: tblAuctionItemContents- can I
create a Basket1 for the given auction name (so that 5
years from now, I don't have to start at Basket 2,000) and
then tell the basket which auction items it holds?

I have the AuctionName form done (the easy one you know!).

I believe the third form would show both individual
auction items and basket that are composed of individual
items combined together. What would that look like?

Thanks for your patience, Sal

 

[Guest]

Comments/answers inline...

--

Ken Snell
<MS ACCESS MVP>



Yes, tblAuctionGifts would contain one record for each gift that is donated
for an auction. So in your example, you would have two records.



Yes, this table contains all the "items" (whether individual gifts that were
donated or your combined "baskets" that are composed of many individual
gifts). So these are the items on which people are actually bidding. And
this table allows you to store all auctions' items in one table; each item
is associated to a specific auction.



Yes, you are understanding correctly.



All of the example tables that I posted contain a primary key. CPK means
that the primary key consists of more than one field. It's "composed" of
more than one field; thus, it's called a composite primary key.
Alternatively, you can add a separate PK field, and then you'd create a
unique index on the fields that I labeled with the CPK designation.




No problem. Good luck.