I'm in the planning stages of an inventory db. The part I can't figure out is how to design the database as far as changing costs go. If I have a cost associated with an item, how do I handle when the cost changes? I know I need to have an effective date with each cost, but all items won't have the costs changing at the same time.
You should have a seperate table which holds your costs and is referenced to the item table.
When you need to update a cost you set the expiry_date for the current record and insert a new record in costs for the items_id. This way you will also have historical data for your changing costs. When referencing the costs you just have to select the active record for the item.
Thanks. That makes sense, but I'm a little fuzzy about the details. (Let me preface this by saying I'm reasonably experienced with access, but I'm trying to learn filemaker and would rather develop it in filemaker)