I'm designing a database that is used to track an inventory item through its lifecycle. I'm currently relating my tables using the previous lifecycle event's ID (for example, a shipment contains the inventory item's ID, the receiving department table stores a shipment ID, the sales table stores the receiving receipt ID, and so on). I've designed the tables in this way to enforce the fact that each of the lifecycle steps must happen in a particular sequence.
If I wanted to check information about a particular inventory item, I'd need to go through and join all the tables that represent that item's lifecycle. Each of my inventory items has a unique serial number, so I'm thinking about including that SN on specific lifecycle event tables in order to avoid having to perform all the joins.
I was wondering, is there a rule or guideline as to when you should use key duplication vs performing a bunch of joins? If I were to go with the joining method, would this be an appropriate place to use a bunch of different views?