Zydeceltico
Registered User.
- Local time
- Today, 02:29
- Joined
- Dec 5, 2017
- Messages
- 843
Hi all -
We make products. We make 50 products.
Most of the products (assemblies) are created by attaching a top part to a bottom part. Two parts make one assembly - usually.
Very often, a part that acts as a bottom part is used with a variety of different tops resulting in multiple unique assemblies.
Sometimes, a single Part_ID could be used for both the top and the bottom of a unique Assembly_ID.
Some of the products we sell are just a top or bottom piece as a standalone part.
We have over 100 parts.
I have tblAssemblies. This table also has a PK named "Assembly_ID" which is an autonumber. Among other fields, tblAssemblies has field "Top" and field "Bottom" (i.e., component1 and component2 of the assembly).
tblParts also has key field: Part_ID. Again, assemblies take 2 parts from tblParts.
I am not keeping inventories or any such calculating. These tables exist primarily as lookup tables and always will. We have an ERP that handles everything else on a much larger scale.
I want to relate parts to assemblies. I know I could put a second instance of tblParts in the Relationship diagram and link Part_ID from tblParts to both "Top" and "Bottom" fields in tblAssemblies but I suspect there is a more elegant way to do this.
I wonder if a junction table is something I should be considering or BOM or Order Taking schema maybe fit the bill.
Thoughts?
Thanks,
Tim
We make products. We make 50 products.
Most of the products (assemblies) are created by attaching a top part to a bottom part. Two parts make one assembly - usually.
Very often, a part that acts as a bottom part is used with a variety of different tops resulting in multiple unique assemblies.
Sometimes, a single Part_ID could be used for both the top and the bottom of a unique Assembly_ID.
Some of the products we sell are just a top or bottom piece as a standalone part.
We have over 100 parts.
I have tblAssemblies. This table also has a PK named "Assembly_ID" which is an autonumber. Among other fields, tblAssemblies has field "Top" and field "Bottom" (i.e., component1 and component2 of the assembly).
tblParts also has key field: Part_ID. Again, assemblies take 2 parts from tblParts.
I am not keeping inventories or any such calculating. These tables exist primarily as lookup tables and always will. We have an ERP that handles everything else on a much larger scale.
I want to relate parts to assemblies. I know I could put a second instance of tblParts in the Relationship diagram and link Part_ID from tblParts to both "Top" and "Bottom" fields in tblAssemblies but I suspect there is a more elegant way to do this.
I wonder if a junction table is something I should be considering or BOM or Order Taking schema maybe fit the bill.
Thoughts?
Thanks,
Tim