Storing old orders in web shop

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
StumpDK
Forum Commoner
Posts: 35
Joined: Thu Feb 12, 2004 2:28 am
Location: Copenhagen, Denmark

Storing old orders in web shop

Post by StumpDK »

I'm currently working on a shop system in which customers creates orders which later is being processed by the shop owner (as usual in shops :) ).
When an order is processed, it is necessary to store in some way, so old orders can be looked up later.
My question is which way is the best way to store old orders. I could store it in the database with the original relations to the original items ordered, but this won't work as the item price, item description and so on will change over time.
As I see it, there is two opportunities: flat file system or a new database table.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

option 3: add a simple flag to the order table and store the price used for each item.
StumpDK
Forum Commoner
Posts: 35
Joined: Thu Feb 12, 2004 2:28 am
Location: Copenhagen, Denmark

Post by StumpDK »

In the order table? But there can be many items at each order. I would still have to create a new table then, or what?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

no.. you add a price field to the item table, and the flag to the order table.
StumpDK
Forum Commoner
Posts: 35
Joined: Thu Feb 12, 2004 2:28 am
Location: Copenhagen, Denmark

Post by StumpDK »

I have 3 tables:

Items

item_id
price
description

Orders

order_id
customer_id

Items_ordered

item_ordered_id
item_id
order_id


Items are joined with orders through items_ordered. The price in the item table are the current price, but that price will change once in a while, and therefore I can't set the old price in the items table because that would result in several old prices in the items table.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you store the price in your ordered items table.
Post Reply