This is a sketch which represents an issue i met in work with more complicated DB project. The real project - is a DB for goods purchasing, collection & delivery service from one country to another. Customers purchasing a goods online in different stores in foreign country, using a service. Purchased goods are being collected in one place and packaged into single parcel. Then parcel with goods inside moving to the destination country.
So i have there same 2 tables - one with goods details ( name, qty, store name, store contacts, photos, description, size, colors, price, local shipping cost and many other details ) and another table with parcel details - weight, volume, qty of boxes, cross-border delivery price, advanced services & packaging spending and other). These tables are completely different (by logic and essence) each other and could not be merged. Each of these 2 tables have subsequent tables.
Between payments for the goods and payments for parcel a lot of time could be passed. Sometimes - weeks. So, each payment could be OR for the goods, OR for parcel. Impossible to pay for all by one single transaction.
Firstly customer pays for the goods and when parcel assembled - he refill his balance to make next payment for the parcel.
One of foreign keys in payment table always is null. And there is an issue - chance to get anomalies.
So , we have 2 spending tables and one table with money processing. Also i need to keep exchange rate details here and lot of another things. Sometimes is very hard to connect the normalization principles with real world tasks.
So, maybe back to sketch ( simplified representation )?
The main thing is a 2 spending tables, which could not be merged into one. And one table with money processing.
Is a ways to avoid this scheme or rebuild it into something better? I think the rebuilding of this simple draft can help me to understand better ways which could be transferred later to the real project.