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.

I appreciate any help or suggestions.


Re: inventory db 80 80

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.

Re: inventory db 80 80

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)

How would I call up the correct cost?

Appreciate your help.


Re: inventory db 80 80

I have never used filemaker but it is a relational db so their is most likely either a sql window or graphic sql designer like in acccess.

If there isn't you will have to find out how to join tables in filemaker, then join the items table to the costs table where the expiry date is null.

Simple sql

Select a.Item_name, b. Costs
From items a, costs b
Where a.id = b.Item_id
and b.expiry_date is null

Sorry I couldn't help with the filemaker syntax, there might be someone on here that knows the product.

Re: inventory db 80 80

Thanks! I didn't think of the is null part!


Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.