Put it simple, I now have member table,product table etc. and order table. I am wonderring should I put everything into order table when a user does a purchase? I didn't do that, but reference the member table on member info, and product on product innfo
for example, but the thing is the member/product record could be changed, it could be a new name, new price. That means you could reference something that is not the same as the time when the order happens.
Wonderring what is the best practise here, do I need a order table with all possible fields and copy over those field values when order happens?
Ideally, you would leave it the way you have it but add a status field to members/products tables. If you had to make a change to these records, you would make the old record "Inactive" and the insert a new record in "Active" status with the new information.
If this is not possible, then yes, you can add all of the "variable" fields to the Order table to maintain the old values. Obviously this takes more disc space, but it is acceptable since your "lookup" tables have records that might be updated and mess
up your order history.
You should determine which of the member/product fields you really need a "point-in-time" value from, and which ones could be updated but not really matter in your history. For example, if you misspeled my name and added an order, then corrected my name,
would you like the order to have the misspelled name, or the corrected name? For things like price, you would want to retain what the price was at the time of the order, so you would definately want to include it as a field in the Order table.
wyx2000
Contributor
3388 Points
873 Posts
data structure for web back end
Jan 25, 2013 08:35 PM|LINK
I have a question about the design.
Put it simple, I now have member table,product table etc. and order table. I am wonderring should I put everything into order table when a user does a purchase? I didn't do that, but reference the member table on member info, and product on product innfo for example, but the thing is the member/product record could be changed, it could be a new name, new price. That means you could reference something that is not the same as the time when the order happens.
Wonderring what is the best practise here, do I need a order table with all possible fields and copy over those field values when order happens?
AZMatt
Star
10648 Points
1896 Posts
Re: data structure for web back end
Jan 25, 2013 08:53 PM|LINK
Ideally, you would leave it the way you have it but add a status field to members/products tables. If you had to make a change to these records, you would make the old record "Inactive" and the insert a new record in "Active" status with the new information.
If this is not possible, then yes, you can add all of the "variable" fields to the Order table to maintain the old values. Obviously this takes more disc space, but it is acceptable since your "lookup" tables have records that might be updated and mess up your order history.
You should determine which of the member/product fields you really need a "point-in-time" value from, and which ones could be updated but not really matter in your history. For example, if you misspeled my name and added an order, then corrected my name, would you like the order to have the misspelled name, or the corrected name? For things like price, you would want to retain what the price was at the time of the order, so you would definately want to include it as a field in the Order table.
Matt