4 Replies Latest reply on Oct 28, 2014 6:05 AM by themactech.

    Duplicate Records, Import or Lookups

    themactech.

      Title

      Duplicate Records, Import or Lookups

      Post

      Hi All

       

      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?

      Assembly_Template
      -assemblyName
      -assemblyDescription

      Sub_Assembly_Template
      -subName
      -subDescription
      -relatedAssembly

      Tasks_Template
      -TaskName
      -TaskDescription
      -relatedSubAsseembly

      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.

      Clay
      -

        • 1. Re: Duplicate Records, Import or Lookups
          philmodjunk

          To repeat a comment that, I believe, I made to an earlier thread of yours, You really have two different sub systems here to your database project. One is a "specifications" system that documents how each product is assembled from it's component parts, assemblies and sub asemblies. Another is your production management subsystem where you schedule products for production.

          You should not duplicate the specification data just to set up a production run. Instead, your records should reference the same set of BOM (Bill of Materials) records that document how the product is produced by linking to this data via a ProductID. No record duplication should be done here. Instead, you create a new blank record and select the needed value in a foreign key field to link it to your product record and from there, to the BOM records  for that product that describes how one unit of product should be manufactured.

          • 2. Re: Duplicate Records, Import or Lookups
            themactech.

            Hi Phil.. I think you're right. I duplicated my thought process.. and didn't realize  I had asked the same general question in another thread. I'll go back and re-read your original reply. 

            but thanks for the quick response. :)
             

             

            • 3. Re: Duplicate Records, Import or Lookups
              themactech.

              so re-reading.. again... (sorry).. are you suggesting one primary system and two sub systems?. The primary "Inventory, Sales and Purchasing",  and the two sub systems... "Specifications" and the other "Production Management"? They still can relate to one another but essentially function independently from each other?

              • 4. Re: Duplicate Records, Import or Lookups
                themactech.

                Hi Phil
                 

                Is it possible that you could illustrate an example of the ERD you describe above?

                 

                Clay