I'm having an issue designing a solution with this type of product structure.
It's a joinery company that machines timber. Ideally i'd like all products to be on the same table but this would mean some sort of self relationship.
Western Red Cedar 4x2" (primary product or group) Table 1
Western Red Cedar 4x2" 7ft (secondary product- Bale) Table 2
Western Red Cedar 4x2 8ft / 9ft (secondary product- sticks) Table 3
The company can sell a bale of timber or open a bale and sell the sticks which are all part of the primary product.
I have looked at a primary product table for the primary product and two other tables one for bales and one for sticks all related.
This works fine but does cause an issue when trying to create a report for PO's or SO's as far as I can see you can only create the report from one table or other
Would it be a better idea to use one product table and self join for the common secondary product.
Hope this makes sense. Thanks