Databse design for a part/subassembly list

Question

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?

Answer 1

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:

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?"