Key Fields – How do I use combined/concatenated keys?

[Kevin Mullins]

I am trying to design a solution that will be used to manage events from year to year. I have a number of tables that have unique information per event but not unique in total per table.

The item table, the attendee table and the catalog table are all related to one event however I don't want to build new tables per event. I could do a find by event and then sub find by the needed item but I thought it would be better to be more specific in my design to over come the issue.

In short for each Event I want to use the same bidder numbers, the same catalog numbers, and I only want to see items that have been collected for a specific event. I need to be able to run reports and or create invoices on the bidder numbers and the catalog numbers per event, but since Event 05 and Event 06 both have bidder 101 how do I properly store & report on things if they are not unique numbers within their own table.

I attached a pdf to illustrate my current thinking but I can't quite figure out how to translate my thoughts into a real world design.

Any help or pointers would be greatly appreciated

Kevin

auctionERD.pdf

[Ender]

Welcome Mullins!

I can't quite make out what you're trying to do from your description. It might help if you gave a real-world example with how the events from year-to-year should or should not relate to the various elements from other years.

Also, I don't know if this was just an error on your ERD, but your keys don't match what the chicken foot is doing on the Item<=>Catalog relationship. Either the chicken foot is going the wrong way, or the foreign key in Item should be fk_CatalogID (with no fk_ItemID in Catalog.)

[Kevin Mullins]

Thanks for the reply

On the ERD

I think the ERD is correct. One catalog can have multiple items but an item can be in only one catalog. Hence the one to many from Catalog to Items. In my mind the catalog table is made up of items and multiple items can be in one catalog record. So I used the ItemID from the Item table as the key between the two tables.

Real world situation.

This is an auction system that will be used from year to year (assumption one auction a year but if I create the right key system there could be multiple events a year).

Each Event (auction) has donations of individual items. Those items are pulled together as Catalog items. (A catalog item could be made up of multiple donated items or it could be a one for one) When building the catalog items for say the 2006 Event I only want to see the donations for this year’s event. And I would also want to run a report on things donated in past auctions to see what the hot items were year to year.

Each year I would like at least the cataloged to start from 100. And each event has attendees and I would like the bid numbers for those attendees to be reused each year starting at 100. Doing that means I don't have a truly unique key, so I need to do something that lets me first set the event and then set the Catalog key and the bidder keys for that particular event, which would mean that I have a super key for each item that is made up from the Event ID and something in the Item, Catalog and Attendee tables. Someone had told me that I should use two key fields to make the true primary key, but I just don't know how to do that and have everything flow together.

I don’t know if that helps explain any better, although the more I talk it out to try and explain it to others things get clearer for me.

Kevin

[Ender]

I think the ERD is correct. One catalog can have multiple items but an item can be in only one catalog. Hence the one to many from Catalog to Items. In my mind the catalog table is made up of items and multiple items can be in one catalog record.

In this case, swap the keys around as I suggested (if you don't believe me, try it and see!)

Each Event (auction) has donations of individual items. Those items are pulled together as Catalog items. (A catalog item could be made up of multiple donated items or it could be a one for one) When building the catalog items for say the 2006 Event I only want to see the donations for this year’s event. And I would also want to run a report on things donated in past auctions to see what the hot items were year to year.

This part looks fine.

Each year I would like at least the cataloged to start from 100. And each event has attendees and I would like the bid numbers for those attendees to be reused each year starting at 100. Doing that means I don't have a truly unique key, so I need to do something that lets me first set the event and then set the Catalog key and the bidder keys for that particular event, which would mean that I have a super key for each item that is made up from the Event ID and something in the Item, Catalog and Attendee tables. Someone had told me that I should use two key fields to make the true primary key, but I just don't know how to do that and have everything flow together.

While you could use compound keys for such a relationship, I think it complicates things a bit. Instead, I would suggest using an auto-entered serial number to generate the pk_Bidder# in Attendee and the pk_CatalogID in Catalog, and DO NOT reset them each year. Use these as the relational keys, and use a different Bidder Number and Catalog Number for the user's benefit, which CAN be reset each year.

Not sure if you need this, but if you want to remember all the Bids that the Attendees place on each Catalog, you will need a join table between Attendee and Catalog.