2

We work with tracking the fabrication of large assemblies and sub-assemblies with a large flat Excel files. Usually separate spreadsheets for estimating material costs, fabrication costs, keeping track of submittals etc.

I've done some basic work in MS-Access over the years-enough to know there's probably a better way to manage this data--and have thought of developing a tool in Access. The few times I've sat down to diagram out what the different tables would look like and how they would relate I get stumped by how to treat parts and subbassemblies. To borrow the diagram from this question. The data could look like this:

Assembly (id:1)
    |
    |-Part 1: Rivet
    |-Part 2: Rivet
    |-Group 1: SubAssembly
    |   |
    |   |-Part 1: Rivet
    |   |-Part 2: Bolt
    |   |-Part 3: Bolt
    |   |-Group 1: SubSubAssembly
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-Group 2: SubAssembly
       |-Rivet
       |-Bolt

There could be parts or subassemblies (a collection of parts or more assemblies) at each level. Practicalities aside, there's no limit to the number of levels.

The best I could think of (that doesn't involve hard coding some set max number of levels) is to have a flat table with a field listing the next higher level and somehow sort and organize on that. That is similar to what appears on the drawing-a box labeled "Next higher level" that may contain another drawing number. That seems to be counter to the point of using a relational database though. I'm not looking to develop a central repository of all data, just a desktop tool that I can use to manage my data and if it proves to be useful, share with my co-workers.

What is the proper way to organize this data?

|improve this question
2

To reproduce this structure, you need an Assembly table referencing itself through a ParentAssemblyID and a Part table. The diagram in Access looks like this:

DB Diagram

Note that the assembly table appears twice in this diagram. But there's really only one unique table named Assembly in the DB.

AssemblyID and PartID must be declared as primary key (PK). Its easiest to declare them as AutoNumber column.

In the Realtionships diagram, insert the Assembly table twice, so that you can draw a relation between them. Right click the relation lines and check the boxes "Enforce Referential Integrity" and "Cascade Delete Related Records".

Your example data would have to be entered like this

Assembly
-----------------------------------------------------
AssemblyID  Assembly                 ParentAssemblyID
----------  -----------------------  ----------------
  1         Main Assembly
  2         Group 1: SubAssembly        1
  3         Group 2: SubAssembly        1
  4         Group 1: SubSubAssembly     2
-----------------------------------------------------

Part
---------------------------------
PartID  AssemblyID  Part
------  ----------  -------------
  1       1         Part 1: Rivet
  2       1         Part 2: Rivet
  3       2         Part 1: Rivet
  4       2         Part 2: Bolt
  5       2         Part 3: Bolt
  6       4         Rivet
  7       4         Rivet
  8       3         Rivet
  9       3         Bolt
---------------------------------

Answer to follow-up question: "How would I structure it if I wanted the same part to be usable in many assemblies?"

enter image description here

|improve this answer
  • I didn't know that was possible, thank you! – MattD Mar 8 '19 at 22:08
  • 1
    Cascaded deletion is a quite dangerous feature. The whole tree can be deleted easily by mistake. I prefer to restrict the deletion by FK so only the leaf nodes can be deleted. And there is a special procedure DeleteBranch() that delete leaves recursively up to the given node. – Kondybas Mar 9 '19 at 10:40
  • Yes, but if you want to delete a deeply nested assembly, it's a very tedious task without cascaded deletes. Preventing mistakes by making things tedious is not optimal. A better way would be to mark things to be deleted as deleted or archived together with a delete date. Later, you can clean up older deletes easily. – Olivier Jacot-Descombes Mar 9 '19 at 15:41
  • @OlivierJacot-Descombes I mean that I've used the special stored routine intended solely for branches deleting. User can easily delete the single leaf by DELETE ... but if he want to delete the whole branch he's forced to use CALL DELBRANCH(id) routine instead. This routine acts just like the DELETE on the unrestricted table. – Kondybas Mar 9 '19 at 19:59
  • Okay. I was assuming that the user does things via an application and does not use SQL or any DB tools. – Olivier Jacot-Descombes Mar 10 '19 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.