1

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?

1

"to enforce the fact that each of the lifecycle steps must happen in a particular sequence."

If you want to enforce referential integrity of an item's life-cycle, then I would recommend using essential integrity enforcing tools built into the product, namely constraints.

Having independent keys is good for quickly getting a record specific to a table, but it seems you're not going to get a lot of the benefits of constraints by trying to 'roll your own'.

0

With the JOINS you can use the Primary Key and Foreign Key to link the tables together, but to answer your question in regards to using a bunch of different views, I would almost say that disqualifies the purpose of a view. I would create a view called Inventory_Lifecycle and include all of the tables involved to obtain all the information you would need to report/view on Inventory_Lifecycle

Example..

What I am referencing when I say Key Duplication is linked by Primary Keys and Foreign Keys..

Inventory Table and Sold Table will both have a PK and FK you will link the item sold as a PRIMARY KEY on INVENTORY and that same item will have a FOREIGN KEY on the SOLD table

I am sure you will get many ideas and contributions but I think I am at the very least covering the basic's on designing\approaching the view design. Good luck!

0

At a glance, your method of enforcing the integrity of the lifecycle as you have done seems like a good idea. Using the same key for each table would make it possible to have an entry in a later stage without an entry in an earlier stage. Carr's Law of Data Integrity (which, of course, is just a version of Murphy's Law) is: if it is possible, it is inevitable.

You've made it impossible to skip a step. Well done.

However, keep in mind that the purpose of all these cascading lifecycle keys is to maintain the integrity of the lifecycle. The identity of the individual inventory item is maintained by its own unique identifier. So to maintain consistency of the data, that id value must follow the lifecycle also.

Tracking an item through the lifecycle, you must perform the joins. This is not an issue. But the joins expose the path of the item, so they are performed on the item id, not lifecycle ids. These would be outer joins, of course, so unachieved steps will show up as NULLs. (Unless all you want to see are items that have completed the lifecycle...)

Do not assume that just because there is a defined foreign key relationship between two tables, that those fields must be used to join the tables in a query. The purpose of foreigns keys is data integrity. They play no role in queries (except, I suppose you could say, to help assure the validity or veracity of the output). In queries, you join tables on any field or set of fields that make sense.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.