I didn't mean for you to change the relationship type. I meant you have too many tables that are not necessary to track what you are wanting to track. From what I can understand based on your tables and fields in those tables, you create products for sale made from supplies you purchase from various suppliers.
- You have multiple categories of products made from supplies you buy
- You produce these products in batches
- Each product batch could be created in multiple sizes, net weights and gross weights
- Each size could be made and sold in multiple colors (colours)
- Each color you sell has various wholesale and retail prices with Value Added Tax (VAT) and without VAT and some with extra Packaging cost.
As to items you purchase to create your products:
- You purchase items from your suppliers to make your products
- Each supplier sells multiple items to you probably with a Purchase Order (if you use them)
- Each item has a cost to you, an SKU code, a lot number, a description and other information
As to your customers:
- Each customer has name, address, contact information and discounts associated with them and may place multiple orders through time
- Each order has an order date, quantity ordered and Order Code Number
- Each fulfilled customer order has final delivery information consisting of the applicable price, shipping cost, delivery information, date paid and paid amount (an Invoice if you use them)
Does this sound like your business model?
