Newbie poster here.
I have an, I guess, architectural question. I am still very much a beginner in mysql so bear with me.
I have an e commerce site in which the prices of my products fluctuate very often. By default, my customers see the regular prices or sale prices for each product. The exception is when a returning customer comes to my site, I need them to see the prices they paid last time they made a purchase.
Ex: Customer A bought 3 widgets last month at $5.00 a widget. The price now is $6.00 a widget but when customer A logs in they see the price they paid last time which is the $5.00 per widget.
Finally my question.
Can anybody recommend the best way to do this in terms of database structure?
For example should I keep a separate table linked to the customer table that holds an array of each item they have purchased in the past and the price they paid?
Is there a better way to do it that anybody can think of?