I'm looking for ideas on how to best design/relate tables for the following scenario, primarily - how to best design the tables needed to assign cell#'s to products (ie. Cell Phones, Blackberry's, etc.)

I track incoming calls via tblCallTickets. From frmCall Tickets I'd like to create an Order from which I issue a cell phone, headset, belt clip, a/c and car chargers, etc. I need the Order form to exclude selecting equipment already issued via past Orders, and I need to be able to "turn in" that equipment once no longer used or the User leaves. What I'm miffed about is just how to go about handling my equipment, or Products. Not all products have a mobile# assigned to them. The tables in question are tblOrders, tblOrder Details, tblProducts, and tblMobile#'s. Presently, I have tblMobile#'s and tblProducts joined at tblOrder Details but want to be sure that I'm designing this properly early on before I begin designing forms around it all.

Mobile#'s (per the carriers, AllTel, Verizon, etc) get mated to a ESN number of each cell phone. When a cell phone package arrives I insert the Mobile# into its table and insert all other cell phone hardware data into tblProducts. Then, when I'm issuing products to someone I can, in a neighboring column, select to include a Mobile# with that piece of equipment - which will always be a cell phone in our case though could possibly be a Blackberry in the future.

I question this design because it basically identifies a cell number to a package, the OrderID, rather than mates it permanently to a specific device that may be handed off to other Users with or, as is usually the case, without some of the other items it was initially issued with.

Would it be best to create a Junction table and mate a Product to a cell number so that together they become a cell package with some accessories possibly issued, or leave as-is? If a Junction table is created, how would I go about linking that back to tblOrder Details to be issued along with other "Products" such as the necessary headsets/earbuds, power chargers, leather case, belt clips, etc?

This is the one are I'm completely stumped about and appreciate any thoughts on the matter.



Eis!