4 Replies Latest reply on Nov 17, 2014 5:25 AM by jdevans

    Database design help/opinions needed

    jdevans

      I am building a model for manufacturing aerospace systems for a small company...

      NOTE- the Manufacture process is like...the parts for a given project will be designed by engineers, who produce their CAD. Some CAD designs are "assemblies" of lower-level parts...part A is made from 2 part Bs, 2 part Cs and some hardware (screws, nuts, etc). So, to that end, some assembly designs consist of not only the CAD and related draft file (drawing), but the draft/drawing will have a list of parts that define what all goes into making one unit of the assembly. This is where I'm getting lost in the design.

      Our Engineers can export a .csv of the list of parts from the draft file. This could then be read into the database. Not sure if I need to consider each row of the .csv file as a record in the "PARTS" table or some other table, maybe "DESIGNED PARTS". There is a definite table needed called PARTS because I know I have part number, lot/date code, part description. This would be for off-the shelf, purchased parts like nuts, screws, washers, glue, etc. But I'm not sure if I need a "PARTS LIST" table. This might be for the fact that each designed assembly, the parts lists generated by the engineers (BOM, bill of materials) consist of both other parts they design and commercial-off-the-shelf parts. I'm thinking that there is enough of a difference between these two that they should each have their own table, else, I'll end up with lots of null values.

      Entities/tables as I've identified so far:

      Project (stores project-level records: pk_proj_id, proj_name, proj_start_date, fk_fab_ops_id, etc)

      Fab/Ops (a parent record for storing the build history for a given part or sub-assembly)...stores pk_fabop_id, part description, part number, revision, date requested, fk_project_id

      Fab/Ops Step (a collection of steps/operations that went in to building a part to completion) this will be the many side of a one to many relationship with Fab/Ops

      Parts Lists (not sure if I really need this)...thinking a designed part has a part list, and only one part list. Unless it is revised, then the part gets a new revision level, and the part list grows or shrinks accordingly. It could be that I use "In-House Designed Parts" instead, and that way, each "list" will only be generated upon a search. The data would actually be just individual parts, but each would have an foreign key to a parent "Design" or "Drawing" number.

      Part Assignments A collection of "transactions" where a part was "consumed" by a project via the Fab/Ops. This is a join table btwn a many-to-many involving EITHER the Fab/Ops table and the Parts table OR btwn the Part_Lists and the Parts table. Records date it was used, and gets you to the Fab/Ops parent.

      Parts As you'd expect here. A table whose records track pk_part_id, Part Name, Part Number, Bin Number (storage location), Date/Lot Code, Qty on Hand (calculated), Description, Expiration Date. This table would be the One side of the 1:M relationship with Part Assignment.

      Fab/Ops Step This table is a collection of "transactions". Like a Line_Item is to a Invoice. Think of the Fab/Ops as the Invoice, and the Fab/Ops Step as the Line Item. Captures what step was performed- full description, Employee Id of the technician (which is another necessary table), Date step performed, Calibrated Tools used (yet another related table). This is the Many side of a 1:M relationship with Fab/Ops.

      Employee (see Fab/Ops Step, have not modeled this yet).

      Calibrated Tools (see Fab/Ops Step, have not modeled this yet).

       

      Please see uploaded image. Trying to get design ideas, considerations...

        • 1. Re: Database design help/opinions needed
          erolst

          Your complete description was just too much text for me , but …

          jdevans wrote:

           

          Project (stores project-level records: pk_proj_id, proj_name, proj_start_date, fk_fab_ops_id, etc)

          Fab/Ops (a parent record for storing the build history for a given part or sub-assembly)...stores pk_fabop_id, part description, part number, revision, date requested, fk_project_id

           

          … I think that one of these keys is superfluous. Either belonging to a Project is an attribute of Fab/Ops, or belonging to a Fab/Ops is an attribute of a Project – it can't be both.

          1 of 1 people found this helpful
          • 2. Re: Database design help/opinions needed
            usbc

            Your question: "Trying to get design ideas, considerations..."

            You might find it helpful to model this project after a flat-rate repair manual and parts fiche. If you can find a sympathetic shop manager at a motorcycle dealership that person can walk you through the elements and show you examples which will match up very closely with your goals.

            I suggest motorcycles because the data bases they use are more bite-sized compared to automobiles and a lot of the modernization / computerization of that industry was done by escapees of the 80s aerospace collapse. Also, they are very good at cross referencing parts and sub-assemblies between different models and suppliers. Lastly, you indicate that you are doing this project for the engineers. Don't forget the bean-counters need for analysis. For example, the cam chain for a Honda 350 twin is the same item as for a 750 four. The cost  of using a slightly over spec'd chain for the 350 was insignificant compared to the logistics and inventory capital of having two items.

            HTH

            1 of 1 people found this helpful
            • 3. Re: Database design help/opinions needed
              jdevans

              yes, you're absolutely right, and I think I may have typed this incorrectly. It isn't intended to be modeled that way. The foreign key is definitely in the child table Fab/Ops, and not both.

               

              Thanks for your input into my design dilemma.

              • 4. Re: Database design help/opinions needed
                jdevans

                thanks usbc. I would never have thought of a motorcyle example. And yes, the bean counters are going to have their piece of this database. It will also encompass records related to "Employee" that include training records and timesheets, that don't directly touch the production tables. There will be a piece of the Parts tables that keep track of inventory, and another piece that records parts procurement/inspection/inventory data. This is pretty much a enterprise database design. We are using Filemaker because the organization has all its current data in largely unrelated database files built on Filemaker Pro 5.