Help Rebuild OsCommerce

Looking for volunteers to join your project? Need help with a script but can't afford to pay? Want to offer your services as a volunteer to build up your portfolio? This is the place for you...

Moderator: General Moderators

User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

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.)
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

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.
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

Post by jamiel »

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

Post by jamiel »

That disclaimer sounds like an unneccessary compromize. I'm pretty sure we can agree to a schema. And yes I do realize I have stood up again ;)
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

astions wrote:Wiki Board

http://astions.com/projects/wiki/
Get a better template (see Patterns for PHP) :wink:
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Another scenario why I dont like moving the product into the order, is if I buy 2 jersey's and 1 pair of socks, its three products but one order. Its NOT 3 orders. Its 3 Items, 1 Order, 2 Products. Hence the need for an order table, an Item table and a Product Table.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Just store the line item information. Link to the full product if available. Use the product SKU, not the product table's primary key for linking.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

Another key feature is to handle cancelled Orders or Refunds. Which is where the Transaction table comes in handy.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

How are these SKU's generated, how are they stored and how are they any better than the primary key?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Using the sku ensures that it's linked to the correct product. Problems could occur with imported data or a corrupt database.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

And what is an SKU I hear you cry (I did)?

http://en.wikipedia.org/wiki/Stock_Keeping_Unit
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post by jamiel »

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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

jamiel wrote:How are these SKU's generated, how are they stored and how are they any better than the primary key?
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.
Post Reply