Composite key?

[Guest]

Hi. I'm trying to add a section to my database and I'd appreciate help on the
best design method.

We hold an auction every year. We have a sit-down dinner in which our
contacts are assigned to a table number (10 of our contacts are assigned to
sit at table #1, "table" is too confusing with Access so let's call it
location #1). I have db tables for Auction (ID, Name) and Contacts (ID,
Name...).
Now, I'd like to set up a table for locations (which will be reused each
time we have an auction) and a way to assign contacts to the locations based
on the auction. Although I have a ContactID, that's really for our members,
not necessarily our auction attendees, although a contact can be an attendee
(I'd like for each auction to start the attendee numbering at 1, so that we
can tell how many attendees there are, and because the attendee number is
their auction bid number so I'd like to keep the bid numbers at 1-250 rather
than auction #12 having attendee number 10,000).

So, AuctionA has Location1 (that seats Attendees 1-10, or perhaps 1-8
depending) and Location2 (that seats Attendess 11-20).
AuctionB has Location1 (that seats Attendees 1-10, that are almost certainly
not the same as AcutionA's Location1 Attendeess) and Location2 (that seats
Attendees 11-20, ditto on not the same). The contacts and/or attendees can
attend more than one auction throughout the years, but won't have the same
attendee number or location assignment.

Hope I explained that fairly clearly. Any suggestions with design (I'm
really not so good at composite keys, so a step-by-step would be helpful).
Thanks!

 

[Amy Blankenship]

Try this:

Locations
========
LocationID (AutoNumber)
LocationDesc (text)

AuctionAttendeeLocations
===================
BidNumber (Number)
AuctionID (Foreign key to Auction)
ContactID (Foreign Key to Contact)
LocationID (Foreign key to Location)

You'll need to take care of incrementing the BidNumber per auction in the
form or other data entry method where you assign your people to the
different locations available for the auction, but that data structure
should be sufficient.

HTH;

Amy

 

[Guest]

Thanks, Amy! I'll give that a try and repost if I'm having difficulties. I
appreciate it!