Are these the only tables used here. Seems like there should be more.
a typical invoicing system, for example, uses a minimum of 3 tables--often more:
Prices are then stored in a field in Products and are copied via an auto-enter field option into a field in LineItems. That way, future price changes do not affect past invoices.
And if you need a pricing table where each price for each product has an effective date, an additional pricing table can be added:
The relationship between lineItems and pricing would then be set up like this:
LineItems::Item = Pricing::Item AND
LineITems::Date > Pricing::EffectiveDate
And you then define a sort order on Pricing in this relationship that sorts them in descending order by EffectiveDate. This makes the most recently dated pricing record that has an effective date on or before the LineItems::Date the first related record and thus the price that is auto-entered into a price field in LineItems.