I am learning Filemaker to help wife setup a stationery shop.
Read through many database schemas and tutorials but still have lots of confusion and really want to build a valid durable schema before I develop.
Here is my first "problem": I have the following tables Supplier PurchaseOrder PurchaseOrderLine Product Deliverable
Supplier is easy :-) Product describes the product including it's barcode/UPC/EAN as a universal identifier
Deliverable maps a Product to a specific Supplier. A product can be carried by multiple suppliers. A "Deliverable" is a product carried by a specific supplier that will have the supplier SKU (the code to order that product from that specific supplier) and a price (for now let's forget prices changing in time and order units).
If I get this right I'd be able to do the following: when I need to order a product, list all suppliers that carry it sorted for price from low to high.
Does the above make sense?
When I am building an order I might find the product, if not I need to enter the new product. Ideas on how to do this in a user friendly way?
I still have to understand two main processes:
a) How do I handle orders so that when goods arrive I am going to be able to handle any discrepancy in quantity or price between what I've ordered and what I've received
b) How do I handle price changes. Would I set prices on Products or on Deliverables ? On products it would make my life easier when handling sales but as each supplier might sell me the same product at different prices I'd lose the capability of knowing my net value right ?
Thank you for any help