0

Let me start out by saying that I am an engineer and not a programmer, so there are many things I am still trying to learn about databases.

Anyway, I am trying to create a simple database application with Python and SQLite for managing an engineering bill of materials (for personal projects). I have setup a database based on the information found here and everything is working well. My problem is that I'm not sure how to track revisions to the BOM. In other words, if I were to change the revision of a parent part by removing one of the child parts, how is that tracked? Should the BOM table include a column for revision?

Also, I'm looking to add an "Engineering Change" function where the user can pull a unique change number and create a BOM markup. Basically, the user should be able to add/remove/revise parts and attach them to the Engineering Change number. After that, it should be possible to apply the EC and update the PART and BOM tables. To implement this function, I was planning on setting up two tables...one will store the unique EC number and summary info and the other will store the BOM changes. The idea is that the "BOM markup" table will link to the BOM table and EC table but I'm just not sure of the correct way to do this.

Can anyone provide guidance on this or point me to information that might help?

Community
  • 1
  • 1
HyGear
  • 31
  • 4
  • 'Normal' databases store the *current* information. If you need all versions, you have to extend the schema appropriately. Anyway, this question is too vague. Create a first schema, and ask specific questions about that. – CL. Mar 11 '16 at 13:21

1 Answers1

0

The best way to store such temporal data is to use a database that supports SQL:2011 Temporal. Unfortunately, I haven't seen an open source one that does.

The next best way is to use the History Table pattern.

For each "current" table foo, add foo_history and copy a foo row to a foo_history row on writes to foo. Or use something like Hibernate Envers.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152