I'm implementing a database to track receipts (and shipments, among other things) of unique devices. A device may be received many times (provided that it's shipped out between the receipts), and a receipt can include many devices. Often, the received devices are already in the database. Sometimes, however, a batch of brand-new devices are received from the factory that need to be entered into the database for the first time. I hoping to be able to create new device entries for those such devices by simply logging the receipt, instead of having to create new device entries first and then put them on a receipt entry. My main motivation for this database (the first such project ever for me) is to reduce redundancy, which not only wastes time and often leads to errors.
Work so far
I use the following schema (truncated to stay on point):
Code: Select all
device
====
id (PK)
model_id (FK)
serial_number
model
====
id (PK)
model_name
receipt_device
====
receipt_id (FK) (These two together constitute PK.)
device_id (FK)
receipt
====
id (PK)
receive_on
origin
(I'm leaving out the shipment-related tables for simplicity's sake.)
Problems
1. I can't add a brand-new device to a receipt. I can only choose from devices already in the database.
2. Even when I'm choosing an existing device for a receipt, my current solution is clunky. Because the underlying data for each subform item is the device_id, the list box contains lots of repeated model names. For example, typing "a" would pull up "AA, AA, AA, AA, AA, AA, ... " instead of the more sensible "AA, AB, AC, ..."
Questions
1. Is it possible for me to add brand-new devices in the receipt form? (This would require creating not only new entries in the "receipt_device" table, but also the "device" table as well.)
2. If that's not possible, I can imagine creating a new special form for receipt of brand-new devices, but I'm not sure how exactly. Would this work?
3. How can I make choosing from an existing device more user friendly. I think using model_id and serial_number as (composite) primary key would help here, but might bring up unforeseen problems since the use of a surrogate key as I have is usually recommended in this situation. Is there a better solution?
Thank you.
PS I have read Villeroy's informative post: Relations reflected by list boxes in forms, but I don't think it answers my questions.