In the Ledger table where the transaction line items are stored, add a Products single select field, if there is not already one. Use the Products field and the Quantity number field whenever you are recording a transaction to add to/take from a Raw Materials Inventory account, a Work-In-Progress (“WIP”) Inventory account, or a Finished Goods Inventory account. WIP is the account used to track stock allocated to make product. These three inventory accounts can be added in the Accounts table, each of which indicate in the Account Group field a group called “Inventories”. You can add a Cost of Goods Sold expense account in the Accounts table, to a “Cost of Goods Sold” Account Group. Again, be sure to use the Product field and the Quantity field whenever you are recording a transaction to decrease the Finished Goods Inventory account, and to increase the Cost of Goods Sold expense account. The Ledger table already has a Ledger Memo field to make any notes you want when recording sold product, as you mentioned.
You could add a Products table and link it to the Products field in the Ledger table. Alternatively, you could change the Products single select field in the Ledger table to a “Link to another record” field type and point to a new Products table. This will change the Products field in the Ledger table into a linked reference field, and automatically create a new Products table. A separate Products table is necessary if you want to be able to use rollups rather than groupings, and to add a Notes field, as you mentioned, to make notes for each product in the Products table.
The Products table will store all products and their component parts. The Products table can have a Quantity rollup field, and a Total Cost rollup field, each of which will sum all applicable linked records from the Ledger table. Thus, each product listed in the Products table will show its quantity on hand, and its total cost. You could create a separate view in the Products table - each view is filtered on a separate inventory account so that you can see the inventory list for Raw Materials, WIP, and for Finished Goods, for example. You could create a separate Cost of Goods Sold view in the Ledger table, filtered on the Cost of Goods Sold account, that groups by the Product field, to show total quantity sold, and cost of good sold expense, for each product sold, by making use of the summary fields.
The link you provided on the article about many-to-many relationships illustrates a self-linking relationship - like the managers/subordinates example. Similarly, in the Products table, you could add a “Link to another record” field type linking to the Products table (thus making it a self-linking relationship) to track products that have many components (a one-to-many) or many-to-many. However, as previously mentioned, while you would require a Products table to do rollups, the establishment of a self-linking setup may not be necessary, provided that the transaction affecting various products is recorded properly.
