I'm designing a database to track receipts and shipments of various items for a repair facility.
First, let's say that the items are all notebook computers that circulate in and out, then the schema is obvious. Three tables -- device, receipt, and receipt_device -- will suffice for the receiving part (which is mirrored by the shipping part).
But what if not only devices are received, but also parts? Like devices, parts also have identifying part numbers and serial numbers, but the similarities end there. A device entry can have owner and distributor and various details like OS and RAM that are not applicable to a part. And a part entry may (depending on implementation) have a used_for field to denote which device it ends up in, which naturally doesn't apply to a device.
I've thought of various ways of dealing with this, and so far the most promising solution I've come up with is to create two new tables, part and receipt_part. For data entry, the receipt form will need two subforms, one for receipt_device and one for receipt_part, which is somewhat clunky but not a deal-breaker.
Any suggestions would be welcome. If it makes any difference, devices and parts are rarely, if ever, received or shipped together.
