Theoretical Question

[BHance]

Hi All,

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?

Thanks all

- Bill

[fatihpiristine]

store those details on another table with cust id n product id n store that price there.

[BHance]

store those details on another table with cust id n product id n store that price there.

Thanks for the reply fatihpiristine.

There are two problems with that (probably should have mentioned this)We are talking about 50,000 customers.
There are over 5000 products.

If I created another table with that information it would get very large very quickly.

My idea was since each customer probably has 20 - 30 custom prices I could store those values in some delimited format in another table and link it to the customer id in the customer table. Then when the user logs in, the delimited values are broken apart and stored in an array.

It just sounds like a hack of a way to do something like this so I was wondering if anybody knew a better way.

Thanks !!

[fatihpiristine]

thats only 3 column on another table.
otherwise if u try to get the values from prev. then you should create a good algorithm to check n compare dates and product's prices etc. then if there is any change on price, print it out. i think this table method is better. i dont have any other idea..

[BHance]

Thanks. Still though the table would be too large. If 50,000 customers have custom prices for 30 -100 items each, the table would be millions of rows long.

Thanks anyway for the suggestion.

[Antoro]

I think you must draw / shown us what records you plan to put in that temporary table .. so we could give better solution