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?