firm table (connected to) product table (connected to) join table (connected to) receiving orders table
And i want to create another table which calls "giving orders table"
But i could not decide to use same join table or not
I'd use the same table (called LineItems in many such systems) for both, but use different fields in that table for the quantity given and the quantity received. That would allow me to add these two fields for reporting purposes:
cBal: QtyReceived - QtyGiven
TotalOnHand: Summary, Running Total of cBal with Restart Summary for each sorted Group with LineItems::ProductID as the "group field".
This would allow you to create a report layout where you can see a running "inventory balance" for each product in your database if you sort your records by ProductID.
maybe reports could not work properly when i used same join table for two separate tables. Because of too many records in it.
That shouldn't be a problem and you can perform Finds that restrict your reports to specific sub sets of the total records in this table anyway.
Many many thanks :)