Plain vanilla orders database: Unit price question (because there aretwo unit price fields)

[Strasser]

Situation: Orders table linked "1:many" to the junction table,
OrderDetails table.
Products table also linked "1:many" to OrderDetails table.

I think I get this.
I think I should have a unit price field in BOTH the Products table
AND the OrderDetails table,
because when the Unit price changes, the change will only be recorded
in the Products table.

That way any old OrderDetails.UnitPrice fields will not be changed.
The history (the value of OrderDetails.UnitPrice) of a unit price in
an old order will remain the same as when product was ordered.

Any NEW order will reflect the new Products.UnitPrice value.
Is this correct?

Is there any "best way" to copy the value from the Products.UnitPrice
field and "pop it into" the OrderDetails.UnitPrice field for a current
order?
I am using a macro with the SetValue macro action (I don't know VBA).
Is that OK?
I am using the "on exit" action in the Products.UnitPrice field to
trigger the copying from Products.UnitPrice to OrderDetails.UnitPrice.

Thanks in advance.
Strasser

 

[Allen Browne]

Yes: your logic is spot on as to why you need a UnitPrice field in both the
OrderDetails and Products tables.

If there is a difference between the 2 values (e.g. in old orders after the
price goes up), this difference is correct and meaningful. That's the
criterion for deciding when to duplicate a field across 2 tables. In any
situation where a difference in the 2 fields would represent an error in the
data, you must not ducplicate the field across both tables.

The SetValue action is fine for looking up the current price when a product
is entered in the OrderDetails subform. It would be better to use the
AfterUpdate event procedure of the ProductID field rather than On Exit: that
way it only fires when the product change, and does not mess up the price if
you are tabbling through old orders.

 

[Strasser]

Yes: your logic is spot on as to why you need a UnitPrice field in both the
OrderDetails and Products tables.

If there is a difference between the 2 values (e.g. in old orders after the
price goes up), this difference is correct and meaningful. That's the
criterion for deciding when to duplicate a field across 2 tables. In any
situation where a difference in the 2 fields would represent an error in the
data, you must not ducplicate the field across both tables.

The SetValue action is fine for looking up the current price when a product
is entered in the OrderDetails subform. It would be better to use the
AfterUpdate event procedure of the ProductID field rather than On Exit: that
way it only fires when the product change, and does not mess up the price if
you are tabbling through old orders.

EXCELLENT EXPLANATION. EXTRA INFO VERY HELPFUL! THANKS SO MUCH!