Help Rebuild OsCommerce
Moderator: General Moderators
Take these scenarios.
1.) Product obselete, but orders outstanding. Product is removed from products table. Now you have a foreign key to nothingness on your order table. Which product did the customer order?
2.) Product is changed, for example price. Customer ordered at 'x' price, but the order table now says 'y' price.
3.) Reporting - everything is in one table. Easier to read even if the only storage medium you have is an SQL dump.
3.b) Your choice of storage platform dies of death. Some super huge vulnerability is discovered which renders it completely obselete and the only option to protect your data is to shutdown the software. Even non-savvy people will it easier to read n SQL dump, over having to manually map product to order across multiple tables.
4.) Backups - everyting is on one table. Things go pete tong, it's much easier to restore from a backup when all data (important data at that) is in one table. Same as 3/3b.
Remember.. it's a shop, the orders are the number 1 priority for safeguarding (alongside customer credit details etc.)
1.) Product obselete, but orders outstanding. Product is removed from products table. Now you have a foreign key to nothingness on your order table. Which product did the customer order?
2.) Product is changed, for example price. Customer ordered at 'x' price, but the order table now says 'y' price.
3.) Reporting - everything is in one table. Easier to read even if the only storage medium you have is an SQL dump.
3.b) Your choice of storage platform dies of death. Some super huge vulnerability is discovered which renders it completely obselete and the only option to protect your data is to shutdown the software. Even non-savvy people will it easier to read n SQL dump, over having to manually map product to order across multiple tables.
4.) Backups - everyting is on one table. Things go pete tong, it's much easier to restore from a backup when all data (important data at that) is in one table. Same as 3/3b.
Remember.. it's a shop, the orders are the number 1 priority for safeguarding (alongside customer credit details etc.)
-
jamiel
- Forum Contributor
- Posts: 276
- Joined: Wed Feb 22, 2006 5:17 am
- Location: London, United Kingdom
1) Like I said before, Product's shouldn't be removed, but should definately be able to be made "Inactive".
2) The Price should be linked to the Transaction, not the Product or Order. And Prices should be in their own table linked to Product ID's.
3a) Rule one of Database Normalization.
3b) I agree that it will be complex for an admin to make head or tail of the DB if he has no front-end to it.
4) Refer to 3a.
2) The Price should be linked to the Transaction, not the Product or Order. And Prices should be in their own table linked to Product ID's.
3a) Rule one of Database Normalization.
3b) I agree that it will be complex for an admin to make head or tail of the DB if he has no front-end to it.
4) Refer to 3a.
Storing the product description for every sale is not practical, especially for a high volume store. I propose the following, if I left anything out let me know.
For each product ordered, the Product Name, Quantity, Product Options, Product ID & Product Price will be copied into the customers order history table.
When a customer views their order history, if the product no longer exists, only this information will be displayed. If the product does exist, there will be a link to the product which allows them to view it.
There will be a disclamer stating that product descriptions and prices are subject to change.
I believe that will solve the problems your all talking about.
For each product ordered, the Product Name, Quantity, Product Options, Product ID & Product Price will be copied into the customers order history table.
When a customer views their order history, if the product no longer exists, only this information will be displayed. If the product does exist, there will be a link to the product which allows them to view it.
There will be a disclamer stating that product descriptions and prices are subject to change.
I believe that will solve the problems your all talking about.
Last edited by Benjamin on Fri Aug 25, 2006 9:53 am, edited 1 time in total.
-
jamiel
- Forum Contributor
- Posts: 276
- Joined: Wed Feb 22, 2006 5:17 am
- Location: London, United Kingdom
I feel I should elaborate on my second point to avoid confusion. The Price that was paid for the Order should be stored in the Transaction table. The price of the actual Product in the Prices table. That way when Prices change, Transaction records remain in tact. And the reasoning to moving the Transaction details out of the order is so that multiple payment gateways, currencies, credit cards are easily supported and accounted for without cluttering the order table.
-
jamiel
- Forum Contributor
- Posts: 276
- Joined: Wed Feb 22, 2006 5:17 am
- Location: London, United Kingdom
If the SKU is simple sitting in the product table, and we copy it into the order table with the description etc. when the order is made, then surely if we assume the admin is stupid enough to change his products to something completely different rather than adding a new product (which was onions case), we have to assume that he is stupid enough to use the same SKU for his newly edited product too.
Which brings me back to Products shouldn't change or be deleted, only made inactive.
Which brings me back to Products shouldn't change or be deleted, only made inactive.
SKUs are generated by the user... I would find a commerce solution useless if I had to reform my sku system just to use it. They are stored just like any other information about the product. They are better than primary key because primary key means nothing to our user.... skus are what he uses.jamiel wrote:How are these SKU's generated, how are they stored and how are they any better than the primary key?