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?