In my inventory database, I have raw materials and assemblies. An expert here said to make this a self-join situation and to make everything a "part". Then make a self-join table and start building assemblies by referencing what parts and/or other assemblies they consist of.
My follow up question in this situation is this. My assemblies are dynamically different. Some need columns for "shaft length" of pipe, others need "capacity" columns to show the type of load they can carry, and so on for about 1,000 parts. So, experts here suggested I break my assemblies up into separate tables and give them specific columns they need rather than having a master table with tons of null values.
My question is, though, with a self-join situation--how does this come into play? I have welded pipe as an assembly, I have tons of simple raw materials, I have pumps that consist of weird other parameters (columns), and so on. If I put everything in the same table in order to self-join...where do I store all this other data that is specific to different types of product so that users can search these types of fields?