1

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.

bongbang
  • 111
  • 2
  • 1
    If your DB is just to track the items, does it matter who the distributor or owner is, or the OS and RAM? It may matter - but then, I'd argue that the DB does more than just track what came in and what went out. Is there any chance of needing to track something else with a different set of attributes, that would require a third (fourth, fifth, etc) subform? If so, you may need to rethink the organization of things to keep the receipt/shipping forms sane. – RDFozz Oct 25 '17 at 19:02
  • 1
    @bongbang The considerations brought up by RDFozz above are certainly worth analyzing. On the other hand, if (a) Device and Part are both associated in the same way with Receipt and Shipment but (b) Device and Part have a certain set of properties that are exclusively applicable to one of those entity types, then (c) you may find of help some of the considerations contained in this Q & A. – MDCCL Oct 25 '17 at 20:01
  • @RDFozz Yes, the DB does more than just keeping track of what comes in and goes out. I was just singling out this part to focus on the problem at hand. Another (probably bigger) purpose is to maintain a device history file, which shows everything that's ever happened to a device, of which getting shipped and received is but a small part. There's a very remote chance of another table for generic parts w/o SN, but more likely I just won't track them. By the way, the device is actually not a notebook, but comparable (OS and RAM are just examples). – bongbang Oct 25 '17 at 20:17
  • 1
    @MDCCL Wow, that's quite a write-up! Thank you for pointing me to it. I've tentatively started down that path myself in my mind, and reading your answer helps me realize that in this case device and part are so different that they don't belong to the same supertype. A whole new table w/ its own relational table seems the way to go. Thank you. – bongbang Oct 25 '17 at 21:03

0 Answers0