Tracking Part Number Changes

[kagoodwin13]

I am trying to think of a method to track changes in part numbers.



I work with engineers, and when they make revisions to parts, they must change the part number as a result. For instance, the first revision of the part might be 12345-AAAAA. After review, they have decided to move a datum hole 2mm to the right, so the part number is now 12345-AAAAB.

I would like my database to connect the information saved about 12345-AAAAA to 12345-AAAAB. Additionally, there might be more than one revision, so the database must support revision changes to the nth degree.

Any ideas?

[orange]

How about a PartTable and a PartRevisionTable

PartTable
PartId PK autonumber
PartNo whatever you want to show/report
PartDesc
other info related to original part

PartRevisionTable
PartRevId PK autonumber
PartID FK
RevPartNo <--latest part number
RevisionDate
RevisionDesc
other info specific to this revision of this part

PartTable------>PartRevisionTable 1 to Many relationship

NOTE: Because the PartNo as you refer to it is constantly changing, you CAN NOT use it as the PK of the table.
Since PartNo has meaning to you and others you Display RevPartNo (the latest value) as the latest modified Part Number.

[kagoodwin13]

Thanks for the idea! I'll have to adjust my PK to an autonumber. The PK is currently part number.

[orange]

Be cautious. If you already have data some things may be dropped.