Hi all. In my current project, I have a parts table that, among other things, tracks part supersession/consolidation. Here is a dummy version focusing on these specific fields:
I have both "new" and "current" part number fields so that I can keep track of supersession history: "new" is the immediate supersession, but if that new part is itself superseded, "current" captures the most up to date part number.
Obviously I only want users to be able to select part numbers that actually exist, so I want to create a relationship between these two fields and the parts table--the problem, of course, is that these fields are in the same table. Here is what I am doing now:
Is this the right way to achieve what I'm after? It looks sloppy (especially in the real database, since I actually have a third part number field in the same table and the table itself is much bigger (i.e., has more fields)).
I have both "new" and "current" part number fields so that I can keep track of supersession history: "new" is the immediate supersession, but if that new part is itself superseded, "current" captures the most up to date part number.
Obviously I only want users to be able to select part numbers that actually exist, so I want to create a relationship between these two fields and the parts table--the problem, of course, is that these fields are in the same table. Here is what I am doing now:
Is this the right way to achieve what I'm after? It looks sloppy (especially in the real database, since I actually have a third part number field in the same table and the table itself is much bigger (i.e., has more fields)).
)
