Duplicate Records, Import or Lookups
Just looking for some "best method" advice. As I'm moving along with this ERP solution that I'm working on, I came to a point where I need to figure out which method would work best.
As in previous posts, I'm tracking Assemblies, Sub-Assemblies and the Tasks required to build those sub-assemblies. Basically it's the same thing over and over. Each assembly with it's related sub-assembly will have a manufacturing serial number that ties the assembly, subs and tasks together. (I'm using a centralized Inventory Transaction log to keep track of products and materials used and ordered/created)
So considering this... The assembly and it's related subs and it's related tasks.. never change. The basic information about each will be pretty static. The only thing that will change for each will be that will be related to a job number, a manufacturing serial number and the person the task is assigned to.
Things like Name, Description, Materials used, will remain unchanged.
So I'm wondering if it's a good practice to keep all these "template" records in their own tables respectively and relate them that way to start and then.. when needed a script creates new records in the Products and Tasks tables and duplicates or performs a lookup of all the static information as needed and then the script Sets all the fields like the related Job Number, Serial Number, Person Assigned, etc?
Or maybe I should create the initial record in each of the respective main tables (Products and Tasks) and just duplicate the records as needed.
Or is an import records the way to go.
Thanks for your advice.