I would think about it like this
How often does the information about that product change and can that information be tied to some other type of record
Information that can be tied to some other kind of record inventory purchase order invoicing should be kept in its own record
Even pictures and descriptions can change periodically and should be given consideration to the "effective dating"
Just think in terms of one to many relationships if there is a many relationship about this kind of information then it should more than likely be its own table.
Also consider time when determining if there is a many relationship if the information changes frequently over time or needs a point in time reference then you more than likely need a table for that.
In the case of descriptions and pictures sometimes its a new product ID to accommodate things but even this can be avoided with effective dated tables for changeable information.
Its good to really look at your data structure and plan these things out well effective dating is a real good way to deal with things.
When you build a relationship to an effective dated table you match on key fields and effective date sorted descending. This allows you to place a single related field on a layout and it always be the currently effective date. You can also add portals or other schemes to show history
Dated records are joined by their dates in such a way to produce the correct the historical relationship (ie effdt < invoice date)
Price is definitely something that can change often
Since this is a Purchase Order database. You don't want to see the current effective price for every item listed in the purchase order, you want to see the price that was effective at the time the purchase was made. Thus ongoing price changes should not appear in past purchase orders.
In filemaker, you have a built in tool that specifically serves this purpose. You can set up a unit price field that uses the Looked Up value field option to copy the current effective price from your pricing database at the time you enter/select a product ID. With looked up values, ongoing price changes in the pricing table do not automatically update the values previously entered in your Purchase Order's line item table.
By combining both you can look up a price based on the date of the purchase order rather than the current effective price.
Sometimes we either catchup on work or plan ahead in which case we want the price from the right date and we aren't the one in control of the price entries
Thanks for clarifying aammondd,
That's exactly what I had in mind. :smileywink: