I would add some fields to the order table that record the product price, for sure. Then you can just change the product price in the products table and it will won't affect order history but will reflect in new orders. Same is probably true of coupon data, but that depends what info is stored there. If it is a discount (say 10%) and you think the coupon discount might vary in future, I'd store the coupon_id in the order table and extend the order fields to include net price, discount and gross price. This has an added advantage that you can query, sum and report on this data without having to do any joins or calculations.
Addresses is a different issue because you presumably have to allow for (i) changing an address because it is incorrect (say wrong house number) and (ii) moving address, which means adding a new address. In this case I'd allow many addresses per customer and record the address_id the order is sent to. Your challenge will be making the user interface easy; are you updating this address or changing it - that language could get confusing. Jeremy Burns Class Outfit [email protected] http://www.classoutfit.com On 10 Jun 2010, at 10:17, keymaster wrote: > Our orders need to maintain information about the addresses orders > were delivered to, the coupons used, the price of the individual > products at the time the order was placed, etc. > > I have Product, Coupon and Address models which store all the > respective info. > > Once an order is placed, I need to “freeze a copy" of the Product/ > Coupon/Address info, in order to associate it with the order, so that > forever into the future we can always know what the info was which > made up the order, at the time the order was placed. > > This is so that if later on a user changes his address, or the prices > of the products change, the order information will continue to reflect > the actual info at the time the order was placed. > > Where should I store the copied info? > > 1. In the same Product/Coupon/Address tables, and point the copied > records to the Order (ie. add a foreign key to Order, which is null > except for those records which belong to an Order). This creates a > wrinkle when listing the coupons on the system for the administrator - > we'd have to filter out the copies assigned to orders, and just list > the originals. It's also an issue if I want the model to prevent > duplicate coupon names. > > or, > > 2. Create new tables: OrderProduct/OrderCoupon/OrderAddress and copy > all the info from the original Product/Coupon/Address tables into the > new tables, after the order is placed. (but then we have duplicate > tables, models, validation rules, associations, tables, etc.) > > From a data modelling perspective, what is the best way to deal with > this? > > Check out the new CakePHP Questions site http://cakeqs.org and help others > with their CakePHP related questions. > > You received this message because you are subscribed to the Google Groups > "CakePHP" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] For more options, visit this group at > http://groups.google.com/group/cake-php?hl=en Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
